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

> -----Original Message-----
> From: Cedar Cox [mailto:cedarc@visionforisrael.com]
> Sent: 14 March 2002 21:10
> To: Henshall, Stuart - WCP
> Cc: 'cgibbs@westmarkproducts.com'; pgsql-odbc@postgresql.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.