Re: Maybe more of a VB question, but here goes

> -----Original Message-----
> From: Cedar Cox [mailto:cedarc(at)visionforisrael(dot)com]
> Sent: 14 March 2002 21:10
> To: Henshall, Stuart - WCP
> Cc: 'cgibbs(at)westmarkproducts(dot)com'; pgsql-odbc(at)postgresql(dot)org
> Subject: Re: [ODBC] Maybe more of a VB question, but here goes
>
>
>
> For those that like ready made function type of things,
> here's something for you. This functions converts a string
> to be suitable for handing over to the back end, with
> appropriate characters escaped and single quotes surrounding
> if necessary. It also handles NULL. I escape using \ so:
>
> input output
> ----------------
> hello 'hello'
> bla'h 'bla\'h'
> c:\linux 'c:\\linux'
> <null> NULL
>
> Here's the listing:
>
> Function escapeString(text As Variant) As String
> 'Converts a string to be suitable for handing over to the
> back end, with all appropriate characters escaped
> ' and single quotes surrounding if necessary
> Dim pos As Integer, ret As String
> If IsNull(text) Then
> escapeString = "NULL"
> Else
> ret = text
> pos = 0
> Do
> pos = InStr(pos + 1, ret, "\") 'we must do \ first
> If pos > 0 Then
> ret = Left(ret, pos - 1) & "\" & Mid(ret, pos)
> pos = pos + 1
> End If
> Loop Until pos = 0
> pos = 0
> Do
> pos = InStr(pos + 1, ret, "'")
> If pos > 0 Then
> ret = Left(ret, pos - 1) & "\" & Mid(ret, pos)
> pos = pos + 1
> End If
> Loop Until pos = 0
> escapeString = "'" & ret & "'"
> End If
> End Function

Is this quicker than something like:

Function escapeString(text As Variant) As String
If IsNull(text) Then
escapeString = "NULL"
else
escapeString = "'" & Replace(Replace(text, "\", "\\"), "'", "''") & "'"
end if
End Function

(BTW, in pgAdmin II we occasionally ran into problems using \' so we use ''
- that tended to be in CREATE/ALTER statements though rather than simple
INSERTs).

> Remember to call escapeString instead of wrapper for the last one..
> Someone may want to add this to a howto or something (hint,
> hint).. -Cedar

Hint taken. Shall we get some concencus on the best method first? Mine is
obviously simpler, but if it's not a efficient or has another problem I've
not considered...

Regards, Dave.