TSQLQuery component consistency problems

TSQLQuery component consistency problems

Postby LTSeanTaylor » Wed 13 Jun 2007 10:09

I am encountering consistency problems using TSQLQuery component defined as follows, against Firebird and MSSQL databases.

object SQLQuery1: TSQLQuery
MaxBlobSize = -1
Params = <
item
DataType = ftInteger
Name = 'ID'
ParamType = ptInput
end
item
DataType = ftMemo
Name = 'MEMO'
ParamType = ptInput
end
item
DataType = ftGraphic
Name = 'GRAPHIC'
ParamType = ptInput
end>
SQL.Strings = (
'INSERT INTO TABLE1 (ID, MEMO, GRAPHIC)'
'VALUES (:ID,'
' :MEMO,'
' :GRAPHIC)')
SQLConnection = SQLI
Left = 168
Top = 72
end

Against a Firebird database using an TSQLConnection component defined as follows

object SQLI: TSQLConnection
ConnectionName = 'InterBase by Core Lab'
DriverName = 'InterBase by Core Lab'
GetDriverFunc = 'getSQLDriverInterBase'
LibraryName = 'dbexpida30.dll'
LoginPrompt = False
Params.Strings = (
'DriverName=InterBase by Core Lab'

'DataBase=SQLSERVER:E:\Program Files\LT Enterprise\Data\DBExpress' +
'\TEST.GDB'
'RoleName='
'User_Name=sysdba'
'Password=masterkey'
'SQLDialect=3'
'BlobSize=-1'
'ErrorResourceFile='
'LocaleCode=0000'
'InterBase by Core Lab TransIsolation=ReadCommited'
'WaitOnLocks=True'
'Charset='
'CharLength=1'
'EnableBCD=True'
'OptimizedNumerics=True'
'LongStrings=True'
'UseQuoteChar=False'
'FetchAll=False'
'UseUnicode=False')
VendorLib = 'gds32.DLL'
Left = 160
Top = 48
end


If I fill the MEMO parameter thus

SQLQuery1.ParamByName('MEMO').AsMemo := Memo1.Lines.Text

upon calling EXECSQL, an exception occurs as follows

Database Server Error:
Dynamic SQL Error
SQL error code = -804
Incorrect values within SQLDA structure.


Filling the MEMO paramter thus

SQLQuery1.ParamByName('MEMO').AsBlob := Memo1.Lines.Text

no exception occurs and the SQL Statment is executed correctly.




Against a MSSQL database using an TSQLConnection component defined as follows


object SQLS: TSQLConnection
ConnectionName = 'SQLServerConnection'
DriverName = 'SQLServer'
GetDriverFunc = 'getSQLDriverSQLServer'
LibraryName = 'dbexpsda30.dll'
LoginPrompt = False
Params.Strings = (
'BlobSize=-1'
'HostName=SQLSERVER'
'DataBase=TEST'
'DriverName=SQLServer'
'User_Name=sysdba'
'Password=masterkey'
'LongStrings=True'
'EnableBCD=True'
'FetchAll=True')
VendorLib = 'sqloledb.dll'
Left = 256
Top = 48
end

If I fill the MEMO parameter thus

SQLQuery1.ParamByName('MEMO').AsMemo := Memo1.Lines.Text

no exception occurs and the SQL Statment is executed correctly.

Filling the MEMO paramter thus

SQLQuery1.ParamByName('MEMO').AsBlob := Memo1.Lines.Text

upon calling EXECSQL, an exception occurs as follows


Database Server Error: Operand type clash: varbinary is incompatible with text.

Assume that the other parameters have been filled accordingly.

I require a consistent approach when writing to memo/blob fields win Firebird and MSSQL database.
Please can anyone advise?


FYI
I am using Turbo Delphi.
Firebird 1.5 and 2.0
MSSQL 2005
Licensed and up to date versions of DBExpress drivers for Interbase and MSSQL.
I have also tried the new beta version of DBExpress Interbase drivers.

Firebird table creation
CREATE TABLE "TABLE1"
(
"ID" INTEGER NOT NULL,
"MEMO" BLOB SUB_TYPE TEXT SEGMENT SIZE 1024,
"GRAPHIC" BLOB SUB_TYPE 0 SEGMENT SIZE 1024
);

MSSQL table creation
CREATE TABLE [dbo].[Table1](
[ID] [int] NOT NULL,
[Memo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Graphic] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Thanks in advance

Sean.
LTSeanTaylor
 
Posts: 1
Joined: Wed 13 Jun 2007 09:53
Location: Southampton, UK

Postby Challenger » Mon 18 Jun 2007 06:33

We have fixed this bug. In the next build of DbxIda you will be able to update BLOB fields with TEXT subtype using ftMemo parameter type.
Challenger
Devart Team
 
Posts: 925
Joined: Thu 17 Nov 2005 10:53


Return to dbExpress driver for InterBase & Firebird