[TD 6.0 SP2] Database insert problem with long string/image!

[lrcuand]

Hello,
there is an ugly database problem (we use SQLServer 2005 [via odbc])!
On my insert stmt i get always the ODBC error [Microsoft][ODBC SQL Server Driver]String data, length mismatch
After several hours I can reproduce the error now!

The SQL Server Table looks:

Code: Select all

CREATE TABLE [dbo].[example1](
   [prodnr] [int] NOT NULL,
   [vorsorge] [decimal](2, 0) NULL,
   [zusatz] [varchar](250) NULL,
   [prodnr2] [int] NULL,
   [zahlart] [int] NULL,
   [beispiel] [text] NULL,
   [besuchsnr] [int] NOT NULL,
   [variantennr] [decimal](3, 0) NULL,
   [vorsorgeprodukt] [decimal](1, 0) NULL,
   [pdfAngebot] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

When the insert is executed in SQL-Windows with

Code: Select all

Set lsgSQL = "INSERT INTO example1 ( BEISPIEL, BESUCHSNR, PRODNR, 
      VORSORGE, VARIANTENNR, VORSORGEPRODUKT, PDFANGEBOT )
      VALUES   ( :lsOutput, 1, 1, 
      0, 1, 0, :lsBufferCompressed)"

an ODBC error [Microsoft][ODBC SQL Server Driver]String data, length mismatch would be shown.

If you change the order of the column/bind vars the insert is successful

Code: Select all

Set lsgSQL = "INSERT INTO example1 ( BESUCHSNR, PRODNR, 
      VORSORGE, VARIANTENNR, VORSORGEPRODUKT, PDFANGEBOT, BEISPIEL)
      VALUES   ( 1, 1, 
      0, 1, 0, :lsBufferCompressed,
      :lsOutput)"

Repro case:

DBProblem.jpg

DBProblem.zip

[Jeff Luther]

I don't know why the order might be an issue, but I (don't) see one thing: You don't have the SqlSetLongBindDatatype() for your long values. TEXT is optional; TD assumes text. But the IMAGE column value must have SqlSetLongBindDatatype( ..., 23 ). See TD help for this function for details.

NOTE:
for INSERT/UPDATE: SqlSetLongBindDatatype() is called between prepare and execute
for SELECT: SqlSetLongBindDatatype() is called after prepareExecute and before the fetch

[lrcuand]

Hi Jeff,

thanks for the tip, but there isn't any improvement - same error!

[lrcuand]

@Jeff: Can you reproduce this case?

[Jeff Luther]

I didn't try to reproduce it because the code wasn't correct. It needs to be fixed for the SqlSetLongBindDatatype() calls as I mentioned.

but there isn't any improvement- same error!

If you corrected your test case and it still does not work, then kindly zip and attach that. I will test it then.

[lrcuand]

o.k, here the repro-case with the modified code:

DBProblem.zip

[Jeff Luther]

Kurt - you will not like to read this...

But I ran your latest DB test case and cannot reproduce the problem you see. Both INSERTs work to insert a row OK. I'm connecting to an MS SS 2005 DB via odbc using Sql Native Client driver. I'm testing with v6 SP2

[lrcuand]

Hi Jeff,
can you tell me which version of SQL Native Client driver you use? (we use 2005.90.4035.0)

Thanks!

[Jeff Luther]

2005.90.4035.00 - dated 24 Nov 2008-- extra zero at the end of the version for me, but that's exactly what I see in my ODBC DSA dlg. And I'm running WinXP SP3 on a Dell laptop.