SQL Related Articles: Storing binary data in strings in MSSQL 2004-07-21 -- Bruce Johnson Newsgroups: softvelocity.clarion.databasedrivers
21 July 2004
Ok - here's the consolidation of this thread for those of you that FAQ this
sort of stuff....
Let's say you're using a string in a table - and it contains binary data. A
common case for this is where you have say a long (in your program) OVER a
string data type.
Now if you simply converted your app from say TPS to MSSql this field would
get truncated at the first zero (chr(0)) character. Actually the zero is
turned into a space, and the field after that is added with spaces.
The first solution is to set the EXTERNAL NAME for that field, in the
dictionary to
whatevername | BINARY
(Both FM3 and Multi-Proj are currently being updated to support this)
If you're only using the ABC file manager, or the native file driver
commands then you're done.
However if you are using a PropSQL statement then there's an added
complication. The PropSql statement appears to take a CSTRING as it's
parameter, and hence is truncated on the first zero character. You solve
this you need to convert the string to the Sql Hex syntax.
for example;
err:other = '<0,1,0,2>'
errors{prop:sql} = 'Insert into Errors (err_other) values (''' & err:other
& ''')'
will fail. But
err:other = '<0,1,0,2>'
errors{prop:sql} = 'Insert into Errors (err_other) values (' &
SQLHex(err:other) & ')'
will work where SQLHex is declared as follows;
in map
SqlHex (string p_string),string
in code
SqlHex Procedure (string p_string)
ans string(16002)
cx long
bx long
ax long
code
ans[1:2] = '0x'
ax = 3
loop cx = 1 to len(p_string)
bx = bshift(val(p_string[cx]),-4)
if bx < 10 then ans[ax] = chr(bx+48) else ans[ax] = chr(bx+55).
ax += 1
bx = band(val(p_string[cx]),15)
if bx < 10 then ans[ax] = chr(bx+48) else ans[ax] = chr(bx+55).
ax += 1
end
return ans
If you have FM3 then SQLHex is available to you (exported from the FM3 dll)
so you won't have to re-declare it, or re-enter it in your program.
Printed November 23, 2024, 8:17 pm This article has been viewed/printed 35327 times. Google search has resulted in 404 hits on this article since January 25, 2004. |