I was trying to export the images from a signature field where the data is stored in base64, and comes from Harm's wrapper of the signaturepad js control. The export worked in about 70% of the cases, but created invalid images for the rest.
here's the code.
Code:
Procedure OnProcess
String sFilePNG
String SfileJpg
String sImage
UChar[] uImage
Integer iPixel
Integer iPixels
Integer iResult
Pointer pImage
String sMYSQL
Integer iClients
Integer iRow
String[][] sData
Set_Argument_Size 512000
Move "Select ID, Foo, Bar, Name,Zip,paytype,telephone,email,parid,((signature)) from tClients where rollYr = 2021 and (len(trim(signature)) > 15) and Name like '%MULLA%'" to sMySQL
Get SQLItems of ghoSQLHelper sMySQL to sData
Move (SizeOfArray(sData) -1) to iClients
Move 0 to iPixels
For iRow from 0 to iClients
Move (SFormat("binary: c:\temp\si\AA%1.png",trim(sData[iRow][8]))) to sFilePNG
Direct_Output channel 5 sFilePNG
Move (Right(sData[iRow][9],(Length(sData[iRow][9]) -22))) to sImage
Move (Base64DecodeFromStr(oCharTranslate,sImage,(&iPixels))) to pImage
Move (ResizeArray (uImage, iPixels +1, 0)) to uImage
Move (Memcopy (AddressOf (uImage), pImage, iPixels)) to iResult
Write channel 5 uImage
Close_Output channel 5
Loop
End_Procedure
On the red line, I added a breakpoint and added (Length(sData[iRow][9])) to the watch list.
Hey why is the length of the field 15999 ?
I checked in the database through MSSQL Management Studio. Max actually used length is 25,000, in a nVarChar(max) field
What could be limiting this to 16K ? It's not Argument Size, it's not the INT file. I stepped through the entire rabbit hole that is behind SQLExecDirect and eventually found this gem:
Code:
// Max size of buffer for variable length data. Default 16000 Property Integer piMaxVariableBufferLength 16000
What this code does is SILENTLY truncate data which is longer than the "piMaxVariableBufferLength" and DOES NOT THROW AN ERROR WHILE IT DOES IT.
Now, I can see that back in the day 16K was a lot of data to store in a field, but it this day and age, not so much.
May I suggest:
- Retrieve up to ONE BYTE MORE than the max buffer, and if the data returned is all of that buffer, an error be thrown.
Or at least the default be made more obvious (16666) bytes or better yet remove the limit.