Having trouble getting all of my SQL_LONGVARBINARY data.

[David Clarke]

Thanks guys.... And Yes, I am not going to keep the images in the database.


From microsoft:

varbinary [ ( n | max) ] Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length.

[Eric Pearson]

> MS SQL Server. There is no longvarbinary type, just Image and VarBinary(n | MAX)

I didn't know that, thanks.

[Stuart McLachlan]

> What size was the column defined as in the database?

The length is not specified when a SQL_LONGVARBINARY column is created; that's the VAR (variable length) part.

He appears to be using MS SQL Server. There is no longvarbinary type, just Image and VarBinary(n | MAX)

"n" has a maximum size of 8000. If he had used VARBINARY(8000), the jpg would have fitted but the BMP would not.
If he has used VARBINARY(MAX), it would not have been an issue.

But it's moot since I see he defined it as IMAGE which is the same as VARBINARY(MAX)

(Note: MS warns that IMAGE is depreciated and should be replaced with VARBINARY(MAX) since it will not be supported in some future version).

[Eric Pearson]

> What size was the column defined as in the database?

The length is not specified when a SQL_LONGVARBINARY column is created; that's the VAR (variable length) part. The 65536 value is the SQL Tools default buffer size when the column size is not pre-defined.

> saving large images into the database.

In my experience, that's not a common practice. It's more common to store only a large-image filespec, a "pointer" (so to speak) to the actual image. For example that's how WordPress stores all of the images for a website.

[Stuart McLachlan]

Changing to PNG solved the problem... Thanks Rod!!

As Rod said, the BMP was 125KB.

I notice this is the traces:
[58417.376] | | | Type SQL_LONGVARBINARY
[58417.376] | | | Length 65536

What size was the column defined as in the database?

[David Clarke]

Changing to PNG solved the problem... Thanks Rod!!

------------------------------------------------------------ START TRACE 05-02-2022 @ 21:10:09 rtid:2012/06/29Pro-TR

[76209.603] >SQL_SetOption|OPTION 34|VALUE 8000>
[76209.604] <SQL_SUCCESS<

[76209.604] >SQL_SetOptionStr|OPTION -10|VALUE "[h0D][h0A]">
[76209.604] <SQL_SUCCESS<

[76209.604] >SQL_SetOptionStr|OPTION -41|VALUE "DD_MM_YYYY">
[76209.604] <SQL_SUCCESS<

[76209.604] >SQL_SetOptionStr|OPTION -45|VALUE " | ">
[76209.604] <SQL_SUCCESS<

[76209.604] >SQL_StatementMode|DB 1|STMT 1|MODE 10|VALUE 1>
[76209.604] <SQL_SUCCESS<

[76209.604] >SQL_StatementMode|DB 1|STMT 1|MODE 9|VALUE 0>
[76209.604] <SQL_SUCCESS<

[76209.604] >SQL_StatementMode|DB 1|STMT 2|MODE 10|VALUE 1>
[76209.604] <SQL_SUCCESS<

[76209.604] >SQL_StatementMode|DB 1|STMT 2|MODE 9|VALUE 0>
[76209.604] <SQL_SUCCESS<

[76209.604] >SQL_OpenDatabase|DB 1|CON$ "DRIVER=SQL Server;SERVER=DCI-FRACTAL;UID=sa;PWD=moo;DATABASE=DCI"|PROMPT 1>
[76209.604] | >SQL_OpenDatabase1|DB 1>
[76209.604] | | |DB 1 is CLOSED
[76209.604] | <SQL_SUCCESS<
[76209.604] | Cursor Mode Set
[76209.604] | >SQL_OpenDatabase2|DB 1|CON$ "DRIVER=SQL Server;SERVER=DCI-FRACTAL;UID=sa;PWD=moo;DATABASE=DCI"|PROMPT 1>
[76209.670] | | CON$ DRIVER=SQL Server;SERVER=DCI-FRACTAL;UID=sa;PWD=moo;WSID=DAVID-DESKTOP;DATABASE=DCI
[76209.671] | | Checking FetchScroll
[76209.671] | | FetchScroll OK
[76209.671] | | |DB 1 is now OPEN
[76209.671] | <SQL_SUCCESS_WITH_INFO<
[76209.671] <SQL_SUCCESS<

[76209.671] >SQL_Statement|DB 1|STMT 1|IMME|SQL: "SELECT [Google_Authenticator_QR_Code] FROM [DCI].[dbo].[Google_Authenticator] where [Google_Authenticator_String] = '5K6HSQ3HJJEW6KQ3'">
[76209.671] | |DB 1 is OPEN
[76209.671] | IMMEDIATE EXECUTION...
[76209.671] | >SQL_OpenStatement|DB 1|STMT 1>
[76209.671] | | |STMT 1 is currently CLOSED
[76209.671] | | |DB 1|STMT 1 is now OPEN
[76209.671] | <SQL_SUCCESS<
[76209.672] | >SQL_AutoBindColumn|DB 1|STMT 1|COL ALL>
[76209.672] | | Autobinding ALL columns
[76209.672] | | >SQL_ResultColumnCount|DB 1|STMT 1>
[76209.672] | | <1<
[76209.672] | | >SQL_AutoBindColumn|DB 1|STMT 1|COL 1>
[76209.672] | | | >SQL_ResultColumnInfoStr|DB 1|STMT 1|COL 1|RESCOL_CONCISE_TYPE/NUM>
[76209.672] | | | <SQL_LONGVARBINARY<
[76209.672] | | | Type SQL_LONGVARBINARY
[76209.672] | | | Length 65536
[76209.672] | | | >SQL_ManualBindColumn|DB 1|STMT 1|COL 1|SQL_LONGVARBINARY|PTR|LEN 65536|IND>
[76209.672] | | | | PTR 00A249B4
[76209.672] | | | | IND 009E5834
[76209.672] | | | | |DB 1 is OPEN
[76209.672] | | | | |STMT 1 is currently OPEN
[76209.672] | | | | DType SQL_LONGVARBINARY
[76209.672] | | | | CType SQL_DEFAULT
[76209.672] | | | | [email protected] 00A249B4
[76209.672] | | | | [email protected] 009E5834
[76209.672] | | | | BufLen 65536
[76209.672] | | | | |COL 1 Bound
[76209.672] | | | <SQL_SUCCESS<
[76209.672] | | <SQL_SUCCESS<
[76209.672] | | ALL columns have been bound
[76209.672] | <SQL_SUCCESS<
[76209.672] <SQL_SUCCESS<

[76209.672] >SQL_FetchResult|DB 1|STMT 1|ROW 0>
[76209.672] | FetchType 1
[76209.672] | Fetch NEXT Row
[76209.672] <SQL_SUCCESS<

[76209.672] >SQL_ResultColumnString|DB 1|STMT 1|COL 1>
[76209.672] <‰PNG[h0D][h0A][h1A][h0A][h00][h00][h00][h0D]IHDR[h00][h00][h00]¥[h00][h00][h00]¥[h08][h06][h00][h00][h00]=+=Ú[h00][h00][h00][h01]sRGB[h00]®Î[h1C]é[h00][h00][h00][h04]gAMA[h00][h00]±[h0B]üa[h05][h00][h00][h00][h09]pHYs[h00][h00][h0E]Ã[h00][h00][h0E]Ã[h01]Ço¨d[h00][h00][h0C]õIDATx^íÒÑŠ[h1D]Ë[h11][h05]QÿÿOÛõp[h0C]6½„âN¦ªGB[h01]Á@[h13];ë[h08]ô¯Ã¿¿‹bÒI1館Ð¶ZÑöEùñ[h15]Ť“bÒIQ;¡mµ¢í‹òã+ŠI'Ť“¢vBÛjEÛ[h17]åÇW[h14]“NŠI'E턶Պ¶/ʏ¯(&[h14]“NŠÚ[h09]m«[h15]m_ôùñ[h06]“wµ½¡˜tRÔNÔmí¶Ñ»ÇçÇ[h1B]LÞÕö†bÒIQ;Q·µÛFï[h1E]Ÿ[h1F]o0yWÛ[h1B]ŠI'EíDÝÖn[h1B]½{|~¼Áä]mo(&[h14]µ[h13]u[»môîñùñ[h06]“wµ½¡˜tRÔNÔmí¶Ñ»ÇçG¡®*j'´•bÒI¡n[¡NŠIW[h15]êŽÏB]UÔNh+Ť“BݶB[h14]“®*Ô[h1D]Ÿ[h1F]…ºª¨ÐVŠI'…ºm…:)&]U¨;>?[h0A]uUQ;¡­[h14]“N[h0A]uÛ[h0A]uRLºªPw|~[h14]ꪢvB[)&[h14]ê¶[h15]ꤘtU¡îøü(ÔUÅv'&[¡{UñV'êV]U¨;>?[h0A]uU±Ý‰ÉVè^U¼Õ‰ºUW[h15]êŽÏB]Ulwb²[h15]ºW[h15]ou¢nÕU…ºãó£PW[h15]۝˜l…îUÅ[¨[uU¡îøü(ÔUÅv'&[¡{UñV'êV]U¨;>?[h0A]uU1é¤PW­üŽ[Qï©«[h0A]uÇçG¡®*&[h14]ꪕßq+ê=uU¡îøü(ÔUŤ“B]µò;nE½§®*Ô[h1D]Ÿ[h1F]…ºª˜tR¨«V~Ç­¨÷ÔU…ºãó£PW[h15]“N[h0A]uÕÊï¸[h15]õžºªPw|~¼A}WœPïM:)jW©÷j·Þ=>?Þ ¾«NN¨÷&[h14]µ«Ô{µÛFï[h1E]Ÿ[h1F]oPßU''Ô{“NŠÚUê½Úm£wÏ7¨ïª“[h13]ê½I'Eí*õ^í¶Ñ»ÇçÇ[h1B]ÔwÕÉ[h09]õÞ¤“¢v•z¯vÛèÝ#?¾¢øÛ½×½(?¾¢øÛ½×½(?¾¢øÛ½×½(?¾¢øÛ½×½(?¾¢øÛ½×½æç7}[ø££o¡ßrCQ»ïÄßÿ”¿[h00]ý–[h1B]ŠÚ}'þþ§ü[h05]è·ÜPÔî;ñ÷?å/@¿å†¢v߉¿ÿ)[h01]ú-7[h14]µûNœß¸û£ë=uUq£“¢vB[9A÷¶­h{|~œPï©«Š[h1B][h14]µ[h13]ÚÊ[h09]º·mEÛãóã„zO]UÜ褨ÐVNнm+Ú[h1E]Ÿ[h1F]'Ô{êªâF'E턶r‚îm[Ñöøü8¡ÞSW[h15]7:)j'´•[h13]toÛŠ¶y­±P'+ÚNœ {[h13]·¹ñ†Ð»²’Ëúˆ:YÑvâ[h04]Ý›¸Í7„Þ••\ÖGÔÉŠ¶[h13]'èÞÄmn¼!ô®¬ä²>¢NV´8A÷&nsã[h0D]¡we%—õ[h11]u²¢íÄ[h09]º7q›[h1B]o[h08]½++w~5Џ®ŠÚ[h09]m'ŠÚ[h09]m'[h0A]u[h13]'ÌÖ[h03]ô[h0F]©ŠÚ[h09]m'ŠÚ[h09]m'[h0A]u[h13]'ÌÖ[h03]ô[h0F]©ŠÚ[h09]m'ŠÚ[h09]m'[h0A]u[h13]'ÌÖ[h03]ô[h0F]©ŠÚ[h09]m'ŠÚ[h09]m'[h0A]u[h13]'ÌÖ[h03]ô[h0F]©ŠÚ[h09]m'ŠÚ[h09]m'[h0A]u[h13]'œ½~U1餘t²2Ù[h0A]Ý“•·¶B÷~ ?~Y1餘t²2Ù[h0A]Ý“•·¶B÷~ ?~Y1餘t²2Ù[h0A]Ý“•·¶B÷~ ?~Y1餘t²2Ù[h0A]Ý“•·¶B÷~ ?~Y1餘t²2Ù[h0A]Ý“•·¶B÷~ ?>œ {Õ[h1B]Lޝl+õÚ‰[h1B]ÛÐ=C9A÷ª7˜¼;ÙVê[h1B]µ[h13]7¶ÿ {†r‚îUo0yw²­Ô7j'nlÿA÷[h0C]å[h04]Ý«Þ`òîd[©oÔNÜØþƒî[h19]Ê[h09]ºW½ÁäÝɶRߨ¸±ý[h07]Ý3œ(¶»ÊäÞööOQ¨“•ÓúÀW[h15]Û]ero{û§(ÔÉÊi}à«Ší®2¹·½ýS[h14]êdå´>ðUÅvW™ÜÛÞþ)[h0A]u²rZ[h1F]øªb»«Lîmoÿ[h14]…:Ya©ƒÕŠ¶ÕŠ¶UQ»[h09]“7´•¢vBÛªàW«[h15]m«[h15]m«¢v[h13]&oh+E턶UÁ¯[h1A]W+ÚV+ÚVEí&LÞÐVŠÚ[h09]m«‚_5®V´­V´­ŠÚM˜¼¡­[h14]µ[h13]ÚV[h05]¿j\­h[­h[[h15]µ›0yC[)j'´­Šó½…B[¹M}C[h14]궭Üت«V´ýÏ[h15]må6õ[h0D]uR¨Û¶rc«®ZÑö[h07]>?V´•ÛÔ7ÔI¡nÛʍ­ºjEÛ[h1F]øüXÑVnSßP'…ºm+7¶êª[h15]màócE[¹M}C[h14]궭Üت«V´¥Ÿþ—‡¡P·­˜tÛŠIW[h15]۝èå[h00]ý@)Ôm+&ݶbÒUÅv'[email protected]?P[h0A]uÛŠI·­˜tU±Ý‰^[h0E]Ð[h0F]”BݶbÒm+&]Ulw¢—[h03]ô[h03]¥P·­˜tÛŠIW[h15]۝èedôc°•¢v[h13]ô†¼Þ•b»[h13]Úf?7ÖÐ#[h15]m¥¨Ý[h04]½!o w¥ØÙύ5ôHE[)j7AoÈ[h1B]è])¶;¡mösc[h0D]=RÑVŠÚMÐ[h1B]ò[h06]zWŠíNh›ýÜXCT´•¢v[h13]ô†¼Þ•b»[h13]Úf?7~ŠÆB]ULº[h1B]ŠÚ‰ºU'+ÚN[h14]¹ûüý)ù ºª˜t7[h14]µ[h13]u«NV´(r÷ùûSòAtU1én(j'êV¬h;Qäîó÷§äƒèªbÒÝPÔNÔ­:YÑv¢ÈÝçïOÉ[h07]ÑUŤ»¡¨¨[u²¢íD‘»Ïßÿ#—;Q·ê¤¨Ðö†B[h14]۝¨[~ÍãåNÔ­:)j'´½¡P'Åv'ê–_óx¹[h13]u«NŠÚ[h09]mo(ÔI±Ý‰ºå×<^îDݪ“¢vBÛ[h1B][h0A]uRlwâÃ[h15]üšÇ˝øßßð_…:)j'´½¡P'Åv'êö|†²¢í¶7Ø~·Þ«Ý6z·:A÷Žüø°¢í¶7Ø~·Þ«Ý6z·:A÷Žüø°¢í¶7Ø~·Þ«Ý6z·:A÷Žüø°¢í¶7Ø~·Þ«Ý6z·:A÷Žüø°¢í¶7Ø~·Þ«Ý6z·:A÷Žü˜[h14]µ[h13]ÚN[h14]êdE[)j·Þ•ÛÔ7Î÷gX[h15]µ[h13]ÚN[h14]êdE[)j·Þ•ÛÔ7Î÷gX[h15]µ[h13]ÚN[h14]êdE[)j·Þ•ÛÔ7Î÷gX[h15]µ[h13]ÚN[h14]êdE[)j·Þ•ÛÔ7Î÷gX[h15]µ[h13]ÚN[h14]êdE[)j·Þ•ÛÔ7F/çGÐm[©Û[h1B]œ {[h13]…:)r÷ùû%ò#趭ÔíNNн‰B[h14]¹ûüý[h12]ù[h11]tÛVêöF''èÞD¡NŠÜ}þ~‰ü[h08]ºm+u{£“[h13]to¢P'Eî>¿D~[h04]ݶ•º½ÑÉ[h09]º7Q¨“"wŸ¿kèaY¹±­]E÷þd+ÚÒO¿[h06][h1F]•[h1B]ÛÚUtïO¶¢-ýôkð[h11]X¹±­]E÷þd+ÚÒO¿[h06][h1F]•[h1B]ÛÚUtïO¶¢-ýôkð[h11]X¹±­]E÷þd+Úþ@~L[h0A]uÕŠ¶ÛŠíNh[[h15]êÞ²rZ[h1F]([h0A]uÕŠ¶ÛŠíNh[[h15]êÞ²rZ[h1F]([h0A]uÕŠ¶ÛŠíNh[[h15]êÞ²rZ[h1F]([h0A]uÕŠ¶ÛŠíNh[[h15]êÞ²rZ[h1F]([h0A]uÕŠ¶ÛŠíNh[[h15]êÞ²ÒË—[h18]ýãâVݶ[h13]ê=urB½W;1û…[h17][h18]ýãâVݶ[h13]ê=urB½W;1û…[h17][h18]ýãâVݶ[h13]ê=urB½W;1û…[h17][h18]ýãâVݶ[h13]ê=urB½W;1û…[h17][h18]ýãâVݶ[h13]ê=urB½W;qÚçø-…:)jW™Ü›l'Ôw·»[h09]çæó‘·[h14]ꤨ]ero²PßÝî&œ›ÏGÞR¨“¢v•É½ÉvB}w»›pn>[h1F]yK¡NŠÚU&÷&Û[h09]õÝín¹ù|ä-…:)jW™Ü›l'Ôw·»[h09]çæ¯Dl¿«{R¨«N˜Ü«ÛÚU&÷êö|oá6Ûïêž[h14]êª[h13]&÷ê¶v•É½º=ß[¸Íö»º'…ºê„ɽº­]er¯nÏ÷[h16]n³ý®îI¡®:ar¯nkW™Ü«Ûó½…Ûl¿«{R¨«N˜Ü«ÛÚU&÷êö|Ïá—[h15]ß©«ŠÚ‰[h1B][u7œpöí ºªøN]UÔNÜت»á„³o[h07]ÕUÅwꪢvâÆVÝ[h0D]'œ};¨®*¾SW[h15]µ[h13]7¶ên8áìÛAuUñºª¨¸±UwÃ[h09]gß[h0E]ª«ŠÚmSß­]E÷ª•ºU7Q¨“â|Ïá—[h15]µÛ¦¾[»ŠîU+u«n¢P'ÅùžÃ/+j·M}·v[h15]Ý«VêVÝD¡NŠó=‡_VÔn›úní*ºW­Ô­º‰B[h14]ç{[h0E]¿¬¨Ý6õÝÚUt¯Z©[u[h13]…:)Î÷[h1C]~YQ;Q·µ[h13]ÚÊmô†¬Ôí[8m[h1B]««ŠÚ‰º­ÐVn£7d¥nßêÄiÛX]UÔNÔm턶r[h1B]½!+uûV'NÛÆꪢv¢nk'´•Ûè[h0D]Y©Û·:qÚ6VW[h15]µ[h13]u[;¡­ÜFoÈJݾՉÓ~}<áÆ»zCV´X©ÛÚ‰º­Ý„só×?"n¼«7dEÛ‰•º­¨ÛÚM87ý#âÆ»zCV´X©ÛÚ‰º­Ý„só×?"n¼«7dEÛ‰•º­¨ÛÚM87ý#âÆ»zCV´X©ÛÚ‰º­Ý„sóùÈ[Šíî[h06]õ·LºmŤ“BÝ‘[h1F]_Qlw7¨¿eÒm+&[h14]êŽüøŠb»»Aý-“n[1é¤PwäÇW[h14]ÛÝ[h0D]êo™tÛŠI'…º#?¾¢ØînPˤÛVL:)žÝ¿þý[h1F]ÍœóS[h19]G·ÿ[h00][h00][h00][h00]IEND®B`‚<
[76209.673] |DB 1 is OPEN

[76209.673] >SQL_CloseDatabase|DB 1>
[76209.673] | |DB 1 is OPEN
[76209.673] | Close open STATEMENTS associated with|DB 1
[76209.673] | >SQL_CloseStatement|DB 1|STMT ALL>
[76209.673] | | |DB 1 is OPEN
[76209.673] | | Closing ALL|DB 1 Statements
[76209.673] | | |STMT 0 was not open
[76209.673] | | >SQL_CloseStatement|DB 1|STMT 1>
[76209.673] | | | |DB 1 is OPEN
[76209.673] | | | |STMT 1 is currently OPEN
[76209.673] | | | >SQL_UnbindColumn|DB 1|STMT 1|COL ALL>
[76209.673] | | | | Unbinding ALL columns
[76209.673] | | | | >SQL_UnbindColumn|DB 1|STMT 1|COL 1>
[76209.673] | | | | | |COL 1 unbound
[76209.673] | | | | <SQL_SUCCESS<
[76209.673] | | | | ALL columns now unbound
[76209.673] | | | <SQL_SUCCESS<
[76209.673] | | | |DB 1|STMT 1 is now CLOSED
[76209.673] | | <SQL_SUCCESS<
[76209.673] | | |STMT 2 was not open
[76209.673] | | |STMT 3 was not open
[76209.673] | | |STMT 4 was not open
[76209.673] | | ALL|DB 1 Statements are now closed
[76209.673] | <SQL_SUCCESS<
[76209.673] | |DB 1 is now CLOSED

[David Clarke]

I'm starting to think it is the ODBC driver!

[David Clarke]

All good thoughts Rod!

I got stuck here for a moment trying to figure out the SQL issue. And you have a fine point about only saving the KEY.
I was using this opportunity to try saving large images into the database. They appear to save just fine but pulling them back out is not working....

[David Clarke]

I also tryed using different ODBC drivers.... Same error.

[Rod Macia]

I don't have an answer to your BLOB problem, but are saving the QRCODE as a BMP?
Seems like a wasted space.

Column type is IMAGE - Which should hold binary data - in this case a bitmap

"SELECT [Google_Authenticator_QR_Code] FROM [DCI].[dbo].[Google_Authenticator] where unique_id = 1"

Why not only save the actual KEY or KEYURL
KEY, 16 bytes
"NAR5XTDD3EQU22YU"

KEYURL appox 50 bytes
"otpauth://totp/MyCompany?secret=NAR5XTDD3EQU22YU"

in PNG format about 5 Kb

in BMP about 125 Kb

[David Clarke]

Hi Eric!
I did have some errors suppressed.
But I still am getting the truncated data error.

------------------------------------------------------------ START TRACE 05-02-2022 @ 19:22:44 rtid:2012/06/29Pro-TR

[69764.528] >SQL_SetOption|OPTION 34|VALUE 8000>
[69764.528] <SQL_SUCCESS<

[69764.528] >SQL_SetOptionStr|OPTION -10|VALUE "[h0D][h0A]">
[69764.528] <SQL_SUCCESS<

[69764.528] >SQL_SetOptionStr|OPTION -41|VALUE "DD_MM_YYYY">
[69764.528] <SQL_SUCCESS<

[69764.528] >SQL_SetOptionStr|OPTION -45|VALUE " | ">
[69764.528] <SQL_SUCCESS<

[69764.528] >SQL_StatementMode|DB 1|STMT 1|MODE 10|VALUE 1>
[69764.528] <SQL_SUCCESS<

[69764.528] >SQL_StatementMode|DB 1|STMT 1|MODE 9|VALUE 0>
[69764.528] <SQL_SUCCESS<

[69764.529] >SQL_StatementMode|DB 1|STMT 2|MODE 10|VALUE 1>
[69764.529] <SQL_SUCCESS<

[69764.529] >SQL_StatementMode|DB 1|STMT 2|MODE 9|VALUE 0>
[69764.529] <SQL_SUCCESS<

[69764.529] >SQL_OpenDatabase|DB 1|CON$ "DRIVER=SQL Server;SERVER=DCI-FRACTAL;UID=sa;PWD=moo;DATABASE=DCI"|PROMPT 1>
[69764.529] | >SQL_OpenDatabase1|DB 1>
[69764.529] | | |DB 1 is CLOSED
[69764.529] | <SQL_SUCCESS<
[69764.529] | Cursor Mode Set
[69764.529] | >SQL_OpenDatabase2|DB 1|CON$ "DRIVER=SQL Server;SERVER=DCI-FRACTAL;UID=sa;PWD=moo;DATABASE=DCI"|PROMPT 1>
[69764.609] | | CON$ DRIVER=SQL Server;SERVER=DCI-FRACTAL;UID=sa;PWD=moo;WSID=DAVID-DESKTOP;DATABASE=DCI
[69764.609] | | Checking FetchScroll
[69764.609] | | FetchScroll OK
[69764.609] | | |DB 1 is now OPEN
[69764.609] | <SQL_SUCCESS_WITH_INFO<
[69764.609] <SQL_SUCCESS<

[69764.609] >SQL_Statement|DB 1|STMT 1|IMME|SQL: "SELECT [Google_Authenticator_QR_Code] FROM [DCI].[dbo].[Google_Authenticator] where unique_id = 1">
[69764.609] | |DB 1 is OPEN
[69764.609] | IMMEDIATE EXECUTION...
[69764.609] | >SQL_OpenStatement|DB 1|STMT 1>
[69764.609] | | |STMT 1 is currently CLOSED
[69764.609] | | |DB 1|STMT 1 is now OPEN
[69764.609] | <SQL_SUCCESS<
[69764.610] | >SQL_AutoBindColumn|DB 1|STMT 1|COL ALL>
[69764.610] | | Autobinding ALL columns
[69764.610] | | >SQL_ResultColumnCount|DB 1|STMT 1>
[69764.610] | | <1<
[69764.610] | | >SQL_AutoBindColumn|DB 1|STMT 1|COL 1>
[69764.610] | | | >SQL_ResultColumnInfoStr|DB 1|STMT 1|COL 1|RESCOL_CONCISE_TYPE/NUM>
[69764.610] | | | <SQL_LONGVARBINARY<
[69764.610] | | | Type SQL_LONGVARBINARY
[69764.610] | | | Length 65536
[69764.610] | | | >SQL_ManualBindColumn|DB 1|STMT 1|COL 1|SQL_LONGVARBINARY|PTR|LEN 65536|IND>
[69764.610] | | | | PTR 00A31DC4
[69764.610] | | | | IND 009F4F74
[69764.610] | | | | |DB 1 is OPEN
[69764.610] | | | | |STMT 1 is currently OPEN
[69764.610] | | | | DType SQL_LONGVARBINARY
[69764.610] | | | | CType SQL_DEFAULT
[69764.610] | | | | [email protected] 00A31DC4
[69764.610] | | | | [email protected] 009F4F74
[69764.610] | | | | BufLen 65536
[69764.610] | | | | |COL 1 Bound
[69764.610] | | | <SQL_SUCCESS<
[69764.610] | | <SQL_SUCCESS<
[69764.610] | | ALL columns have been bound
[69764.610] | <SQL_SUCCESS<
[69764.610] <SQL_SUCCESS<

[69764.610] >SQL_FetchResult|DB 1|STMT 1|ROW 0>
[69764.610] | FetchType 1
[69764.610] | Fetch NEXT Row
[69764.611] <SQL_SUCCESS_WITH_INFO<
[69764.611] ERROR: 1|1|-1|SQL_FetchResult|999999999|01004|0|[Microsoft][ODBC SQL Server Driver]String data, right truncation|69764.611|

[69764.611] >SQL_ErrorQuickOne>
[69764.611] | >SQL_ErrorClearOne>
[69764.611] | <SQL_SUCCESS_WITH_INFO<
[69764.611] <[69764.611] SQL_FetchResult 1 1 -1 999999999 01004 0 -- [Microsoft][ODBC SQL Server Driver]String data, right truncation<

[69764.611] >SQL_ResultColumnMemo|DB 1|STMT 1|COL 1|TYPE 1|FILE "">
[69764.611] | DataType 99,-1
[69764.611] | Buffers 65536 * 16384
[69764.611] | ERROR: 1|1|1|SQL_ResultColumnMemo|999999999|07009|0|[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index|69764.611|
[69764.611] | FAIL
[69764.611] | JOIN? -1
[69764.612] | 64k blocks
[69764.612] | 2.62500000000188E-3 seconds
[69764.612] <[0 bytes]<

[Eric Pearson]

Per Appendix F of the trusty Help File, SQL STATE 01004 is Data Truncated.

At some point before the Trace your code is (apparently) telling SQL Tools to ignore that error,

ERROR WITH SQLState 01004 IGNORED

...but it's not a noncritical success-with-info-type error, it's fatal.

1) Don't do that and 2) also per Appendix F, you may need to use SQL_ResultColumnMemo instead of ...BLOB.

[David Clarke]

I am using SQL Server, Column type is IMAGE - Which should hold binary data - in this case a bitmap.

This is the trace when I am using SQL_ResultColumnBLOB - No data is returned.

Thoughts?

[58299.019] >SQL_Statement|DB 1|STMT 1|IMME|SQL: "SELECT [Google_Authenticator_QR_Code] FROM [DCI].[dbo].[Google_Authenticator] where unique_id = 1">
[58299.019] | |DB 1 is OPEN
[58299.019] | IMMEDIATE EXECUTION...
[58299.019] | >SQL_OpenStatement|DB 1|STMT 1>
[58299.019] | | |STMT 1 is currently CLOSED
[58299.019] | | |DB 1|STMT 1 is now OPEN
[58299.019] | <SQL_SUCCESS<
[58299.020] | >SQL_AutoBindColumn|DB 1|STMT 1|COL ALL>
[58299.020] | | Autobinding ALL columns
[58299.020] | | >SQL_ResultColumnCount|DB 1|STMT 1>
[58299.020] | | <1<
[58299.020] | | >SQL_AutoBindColumn|DB 1|STMT 1|COL 1>
[58299.020] | | | >SQL_ResultColumnInfoStr|DB 1|STMT 1|COL 1|RESCOL_CONCISE_TYPE/NUM>
[58299.020] | | | <SQL_LONGVARBINARY<
[58299.020] | | | Type SQL_LONGVARBINARY
[58299.020] | | | Length 65536
[58299.020] | | | >SQL_ManualBindColumn|DB 1|STMT 1|COL 1|SQL_LONGVARBINARY|PTR|LEN 65536|IND>
[58299.020] | | | | PTR 009519BC
[58299.020] | | | | IND 009148B4
[58299.020] | | | | |DB 1 is OPEN
[58299.020] | | | | |STMT 1 is currently OPEN
[58299.020] | | | | DType SQL_LONGVARBINARY
[58299.020] | | | | CType SQL_DEFAULT
[58299.020] | | | | [email protected] 009519BC
[58299.020] | | | | [email protected] 009148B4
[58299.020] | | | | BufLen 65536
[58299.020] | | | | |COL 1 Bound
[58299.020] | | | <SQL_SUCCESS<
[58299.020] | | <SQL_SUCCESS<
[58299.020] | | ALL columns have been bound
[58299.020] | <SQL_SUCCESS<
[58299.020] <SQL_SUCCESS<

[58299.020] >SQL_FetchResult|DB 1|STMT 1|ROW 0>
[58299.020] | FetchType 1
[58299.020] | Fetch NEXT Row
[58299.022] <SQL_SUCCESS_WITH_INFO<
[58299.022] ERROR WITH SQLState 01004 IGNORED, |DB 1,|STMT 1

[58299.022] >SQL_ResultColumnBLOB|DB 1|STMT 1|COL 1|TYPE -2|FILE "">
[58299.022] | DataType 99,0
[58299.022] | Buffers 65536 * 16384
[58299.022] | ERROR WITH SQLState 07009 IGNORED, |DB 1,|STMT 1
[58299.022] | FAIL
[58299.022] | JOIN? -1
[58299.022] | 64k blocks
[58299.022] | -1.43750000000153E-3 seconds
[58299.022] <[0 bytes]<

This is the trace when I am using SQL_ResultColumnString

[58417.375] >SQL_Statement|DB 1|STMT 1|IMME|SQL: "SELECT [Google_Authenticator_QR_Code] FROM [DCI].[dbo].[Google_Authenticator] where unique_id = 1">
[58417.375] | |DB 1 is OPEN
[58417.375] | IMMEDIATE EXECUTION...
[58417.375] | >SQL_OpenStatement|DB 1|STMT 1>
[58417.375] | | |STMT 1 is currently CLOSED
[58417.375] | | |DB 1|STMT 1 is now OPEN
[58417.375] | <SQL_SUCCESS<
[58417.376] | >SQL_AutoBindColumn|DB 1|STMT 1|COL ALL>
[58417.376] | | Autobinding ALL columns
[58417.376] | | >SQL_ResultColumnCount|DB 1|STMT 1>
[58417.376] | | <1<
[58417.376] | | >SQL_AutoBindColumn|DB 1|STMT 1|COL 1>
[58417.376] | | | >SQL_ResultColumnInfoStr|DB 1|STMT 1|COL 1|RESCOL_CONCISE_TYPE/NUM>
[58417.376] | | | <SQL_LONGVARBINARY<
[58417.376] | | | Type SQL_LONGVARBINARY
[58417.376] | | | Length 65536
[58417.376] | | | >SQL_ManualBindColumn|DB 1|STMT 1|COL 1|SQL_LONGVARBINARY|PTR|LEN 65536|IND>
[58417.376] | | | | PTR 009E66D4
[58417.376] | | | | IND 009A5044
[58417.376] | | | | |DB 1 is OPEN
[58417.376] | | | | |STMT 1 is currently OPEN
[58417.376] | | | | DType SQL_LONGVARBINARY
[58417.376] | | | | CType SQL_DEFAULT
[58417.377] | | | | [email protected] 009E66D4
[58417.377] | | | | [email protected] 009A5044
[58417.377] | | | | BufLen 65536
[58417.377] | | | | |COL 1 Bound
[58417.377] | | | <SQL_SUCCESS<
[58417.377] | | <SQL_SUCCESS<
[58417.377] | | ALL columns have been bound
[58417.377] | <SQL_SUCCESS<
[58417.377] <SQL_SUCCESS<


[58417.377] >SQL_FetchResult|DB 1|STMT 1|ROW 0>
[58417.377] | FetchType 1
[58417.377] | Fetch NEXT Row
[58417.378] <SQL_SUCCESS_WITH_INFO<
[58417.378] ERROR WITH SQLState 01004 IGNORED, |DB 1,|STMT 1

[58417.378] >SQL_ResultColumnString|DB 1|STMT 1|COL 1>
[58417.378] <BMš©[h01][h00][h00][h00][h00][h00]6[h00][h00][h00]([h00][h00][h00]¥[h00][h00][h00]¥[h00][h00][h00][h01][h00] [h00][h00][h00][h00][h00][h00][h00][h00][h00]Ä[h0E][h00][h00]Ä[h0E][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00][h00].....