Sql Server Error with blobs > 400Kb in IMAGE

[anuta]

Hello everybody!

After Migration from Gupta 3.1 to Team Developer 6.0, we have trouble with inserting binary data in Sql Server IMAGE columns. The allowed size is now limited to 400 kb. In the previous version, it was not a problem to insert bigger blobs (>1 MB). [For this reason, i think it cannot be a Unicode issue].

NB: The problem concerns only Team Developer ODBC Driver. Microsoft Native Sql Server ODBC Driver has no trouble to put data bigger then 400 Kb in the DB.

I have tried to play with DBP_LONGBUFFER setting in sql.ini (from nothing to 30000000) and with corresponding dynamic setting in the code. And also with -SalSetBufferSize(nSize, nSize+2, nSize*2, nSize*2+2),
-SalFileOpen (...|OF_UTF16),
-Buffer as Binary or Long String
and all meaningfull combinations of this things.

But all this seems to be not a right approach.

Any suggestions?

Code: Select all

[b]Logfile:[/b]
[code]8/2/12 09:38:57 2> [set database parameter] param = 5112 value = 4531452
8/2/12 09:38:57 2> [SQLFreeStmt] 31914112, 2
8/2/12 09:38:57 2> [SQLFreeStmt] 31914112, 3
8/2/12 09:38:57 2> [compile] UPDATE XXX.DATEI SET XXX.DATEI.DATEN = :1 
8/2/12 09:38:57 2>        WHERE XXX.DATEI.DATEI_ID = :2          
8/2/12 09:38:57 2> [SQLFreeStmt] 31914112, 0
8/2/12 09:38:57 2> [SQLPrepare] 31914112, 142744800, -3
8/2/12 09:38:57 2> [write long]
8/2/12 09:38:57 2> write block [0-5979]
8/2/12 09:38:57 2> 7B5
8/2/12 09:46:15 2> [write long]
8/2/12 09:46:15 2> write block [1509972-1510483]
8/2/12 09:46:15 2> 313
...
8/2/12 09:46:15 2> 313
8/2/12 09:46:15 2> 383
8/2/12 09:46:15 2> 363
[end long]
8/2/12 09:46:15 2> [SQLExecute] 31914112
8/2/12 09:46:15 2> [SQLParamData] 31914112
8/2/12 09:46:15 2> [SQLPutData] 31914112 , 229310496 , 65534
8/2/12 09:46:15 2> [error] = Microsoft SQL Server:182[Microsoft][ODBC SQL S
8/2/12 09:46:15 2> erver Driver][SQL Server]Für READTEXT oder WRITETEXT mÃ
8/2/12 09:46:15 2> ¼ssen Tabellen- und Spaltennamen bereitgestellt werden.[
8/2/12 09:46:15 2> Microsoft][
8/2/12 09:46:15 2> [ERROR] 20035 Microsoft SQL Server:182[Microsoft][ODBC S
8/2/12 09:46:15 2> QL Server Driver][SQL Server]Für READTEXT oder WRITETEX
8/2/12 09:46:15 2> T müssen Tabellen- und Spaltennamen bereitgestellt werd
8/2/12 09:46:15 2> en.[Microsoft]

[/code]

Thank you in advance

[anuta]

Includes: Testapp, Testdb, Testfile (=400 kb), sql.ini

schema: test
user: test
password: test

Select the file and try to save it in db. Attempt to save a file > 400 Kb leads to error 20035 (182). Files <= 400 Kb are saved properly.

[Jeff Luther]

I am looking at this so let me start with a couple notes:
** I don't recommend hard-coding any of the TD DLLs in your code. This is 6.1 section but your test has a vt 60 DLL listed, so wouldn't compile. Far better to always File Include the APL associated with TD DLL, like: File Include: vt.apl
There should be an APL for any of the extended functionality provided by various DLLs, r-click in File Include so see list of mos common

** !bigger issue is that I cannot user your test.bak file so will need to verify the CREATE TABLE statement for your test case. I can make a guess at it:

create table FILES(ID integer, NAME varchar(250), SIZE integer, DATA varbinary)

but want to verify the data type for the DATA column.
MS SS info on types: http://msdn.microsoft.com/en-us/library/ms187752.aspx

** for INSERT/UPDATE, this: Call SqlSetLongBindDatatype( 1, 23 )
Must be between the SqlPrepare and the SqlExecute. For a SELECT, setlong is after prepareAndExecte and before the SqlFetchNext call(s).

** Error I get on UPDATE: 20024 - "[Microsoft][SQL Native Client]String data, right truncation"

CONCLUSION: I need to verify the data type for your DATA column.

[anuta]

Hello Jeff,

sorry, the concerned Team Developer Version is TD 6.0. But i have also tried with sqlodb32.dll from TD 6.1 Installation (to verify if it fix the problem).

The datatype of column DATA is IMAGE.

We use Gupta ODBC driver (Microsoft Native ODBC driver does not produce this error)

Also if calling SqlSetLongBindDatatype( 1, 23 ) between SqlPrepare and SqlExecute the symptomatic remains the same: file > 400 kb cannot be inserted,

Error Number: 20035
Error Text:
Microsoft SQL Server:182[Microsoft][ODBC SQL Server Driver][SQL Server]Für READTEXT oder WRITETEXT müssen Tabellen- und Spaltennamen bereitgestellt werden.[Microsoft][

[Jeff Luther]

We use Gupta ODBC driver

?? What is this? Do you mean this entry in TD's SQL.INI file:
[win32client.dll]
; order of win32client dll's is important when connecting to multiple databases.
; sqlws32 should always be the last and sqlodb32 be 2nd last.
comdll=sqlifx32
comdll=sqlora32
comdll=sqlsyb32
comdll=sqlodb32
comdll=sqlws32

I'm confused a bit: The ODBC manager (ODBCAD32.EXE) is the DB-side ODBC set up. On the TD side, we ship the client-side to the ODBC interface via sqlodb32.dll.

Well, now there's a bit of a problem because when I change the DATA column type to image I am not able to insert both a zip of your DOC file (to test a small binary file) as well as the 400K DOC file you had attached. I've run my mod. test case (I'll attach it) in both v6.0 and v6.1. Note I made a couple changes to your code:
* I moved the logon params. to Form SAM_CreateComplete - just so easier for me to change and test with.
* commented the VarSetup calls. Not needed since sql context is always local
* commented the set buffer call before the SalFileRead. SalSetBufferLenght() is needed only for external function calls (those in DLLs), not in TD SAL calls. TD knows about and calculates string buffer length internally OK. It's only when you pass a string to the 'outside world' in a DLL function that TD needs a buffer length if it's passed as a Receive String parameter.

What could be different about your MS SS setup? Here's what I test with:
* MS SS 2005
* ODBC Data Source Admin. tool show I'm using Sql Native Client driver
* TD INI sqlodb32 defined as shown above


It is this "We use Gupta ODBC driver' that is the puzzler. If you mean (old) Gupta (not new name change) then what is it and why are you using it?

[anuta]

Hello Jeff,

thank you for investigations.

The problem seems to be solved. The cause war using of full column reference in sql statement à la
update db.table set db.table.blobcolumn = :buffer where db.table.id = :id

the solution was to use
update db.table set blobcolumn = :buffer where db.table.id = :id.

With this change in code, files > 400 Kb can be inserted in IMAGE column in Sql Server (our Version is Sql Server 2008).

Sorry for confusing with Sql Server driver. We use sqlsrv32.dll. In my opinion, it was a Gupta (Unify) driver like sqlora32.dll, sqlws32.dll , because of name resemblance, but it comes from Microsoft.

SalSetBufferLenght() is needed only for external function calls (those in DLLs), not in TD SAL calls.

Just for clarification:
- i dont need to call SalSetBufferLenght before SalFileRead (but if i do so this call is not counterproductive)
- but i have to call SalGetBufferLenght() - 2 to calculate file size before SalFileWrite, if saving blobs on disk
- and i have to call SalSetBufferLenght before VisFileRead, VisFileReadBinary and VisFileReadString

[Jeff Luther]

Glad you got it solved! And to reply back to your 3 bullets at the end:

- i dont need to call SalSetBufferLenght before SalFileRead (but if i do so this call is not counterproductive)

JEFF: No, but could be misleading

- but i have to call SalGetBufferLenght() - 2 to calculate file size before SalFileWrite, if saving blobs on disk

JEFF: Yes, this is true (I modified my test to verify this):

JL_2ExtraBytesAfterFetch.png

- and i have to call SalSetBufferLenght before VisFileRead, VisFileReadBinary and VisFileReadString

JEFF: Well, no because the VT functions are part of TD product, dev. has the source and so a length attribute does not need to be set first. But, I always get curious when someone writes something that doesn't 'gel' with me, so I tried it:

JL_NoNeedForSetBufLenWithVTCalls.png

As you can see, I changed the SalFile calls to VisFile calls and note that the set buf len call is commented out and not needed.