Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-08 : 09:05:00
|
| Shaun writes "I took a few hours out this morning to write some remote backup utility for my company. In this utility I produce md5 sums from the file data, all is well, except that these sums come back as 16bytes of garble. Sometimes there will be a ', or other characters, that cause hardship in inserting this data into a table.insert into blah set xyz = '@#$Rgbf24376G(l%'This would work fine unless there were a character that interfered with the string.insert into blah set xyz = '@#$Rgbf243'6G(l%'That, for example, is obviously broken. But more characters than just ' interfere here. I am wondering where I shoudl go to find what characters will interfere so that I may deal with them.This data is being fed in from VB. The reason I ask is currently i do something similar to this:Set rs = New ADODB.Recordsetrs.Open "select file_hash from file_info where 1 = 2", cnn, ..,..rs.AddNewrs("file_hash") = strHashrs.Updaters.Closeinstead of something 'faster' such as this:cnn.Execute "insert file_info (file_hash) values ('" & strHash & "')"This is obviously MUCH faster, no object creation/destruction, no property accesses etc etc. This is what I was doing till i hit a funny binary character that seemed to make the insert barf.Thanks for any help you can lend." |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-02-09 : 07:35:12
|
quote:
But more characters than just ' interfere here.
Someone else may correct me - but so far I've found no issue except for single quote 'I have a function sqlify(value) which I pass all values through, preparing them for SQL text string. In the function I replace all ' with '' and if the value is an empty string I return "NULL".eg sqlify("helo'there") returns "hello''there" (which fixes your problem) and sqlify("") returns "NULL". sqlify("4") returns "4".That way I can do cnn.Execute "insert file_info (file_hash) values (" & sqlify(strHash) & ")" without issue.Someone else probably knows more - but I've been running with that for ages with no issue...Edited by - rrb on 02/21/2002 20:48:14 |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-02-22 : 00:51:51
|
| I do exactly that same thing. I've had no trouble with any character other than ', chr(0) included.Cheers-b |
|
|
|
|
|
|
|
|
making binary data string safe