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

Maybe more of a VB question, but here goes

From
"Corey W. Gibbs"
Date:
Good Morning Everyone,

I'm having a bit of a VB problem, and I don't know quite how to solve this
as my VB knowledge is minimal at best.  The problem is with quotes and
apostrophes in variables.  What I'm doing is pulling some really really
really bad data from an Access database and trying to insert it into my
handy dandy Postgres db.

Here's the insert statement:
    pg_cn.Execute "INSERT into
itemlist(jobno,area,floor,building,room,elevation,phase,cycle,model,w,h,
d,itemno," & _
    "finish,mtostatus) values('" & rs!jobno & "','" & rs!area & "','" &
rs!floor & "','" & rs!building & "','" & _
    rs!room & "','" & rs!elevation & "','" & rs!phase & "','" & rs!cycle &
"','" & rs!model & "','" & rs!w & "','" & rs!h & "','" & _
    rs!d & "','" & rs!itemno & "','" & rs!finish & "','" & rs!mtostatus &
"')"

rs is an adodb recordset

Now, if rs!d="7'" (I said it was bad data ;)) that tickmark throws off the
whole insert statement. as they're interpreted as the genuine ticks.  Has
anyone come across this or can anyone point me in the right direction to
solve this problem short of removing the users "-' key?

thanks in advance
corey


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

From
Zlatko Talić
Date:
In this article there is some functions that maybe can help you
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q178070

Regards
Zlatko Talic

----- Original Message -----
From: Corey W. Gibbs <cgibbs@westmarkproducts.com>
To: <pgsql-odbc@postgresql.org>
Sent: Wednesday, March 13, 2002 7:14 PM
Subject: [ODBC] Maybe more of a VB question, but here goes
> Good Morning Everyone,
>
> I'm having a bit of a VB problem, and I don't know quite how to solve this
> as my VB knowledge is minimal at best.  The problem is with quotes and
> apostrophes in variables.  What I'm doing is pulling some really really
> really bad data from an Access database and trying to insert it into my
> handy dandy Postgres db.
>
> Here's the insert statement:
>     pg_cn.Execute "INSERT into
> itemlist(jobno,area,floor,building,room,elevation,phase,cycle,model,w,h,
> d,itemno," & _
>     "finish,mtostatus) values('" & rs!jobno & "','" & rs!area & "','" &
> rs!floor & "','" & rs!building & "','" & _
>     rs!room & "','" & rs!elevation & "','" & rs!phase & "','" & rs!cycle &
> "','" & rs!model & "','" & rs!w & "','" & rs!h & "','" & _
>     rs!d & "','" & rs!itemno & "','" & rs!finish & "','" & rs!mtostatus &
> "')"
>
> rs is an adodb recordset
>
> Now, if rs!d="7'" (I said it was bad data ;)) that tickmark throws off the
> whole insert statement. as they're interpreted as the genuine ticks.  Has
> anyone come across this or can anyone point me in the right direction to
> solve this problem short of removing the users "-' key?
>
> thanks in advance
> corey
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


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

From
Dave Page
Date:
Try:

pg_cn.Execute "INSERT INTO itemlist (.....) VALUES (..., " & Replace(rd!d,
"'", "''") & ", ...)"

Regards, Dave.

> -----Original Message-----
> From: Corey W. Gibbs [mailto:cgibbs@westmarkproducts.com]
> Sent: 13 March 2002 18:14
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] Maybe more of a VB question, but here goes
>
>
> Good Morning Everyone,
>
> I'm having a bit of a VB problem, and I don't know quite how
> to solve this
> as my VB knowledge is minimal at best.  The problem is with
> quotes and
> apostrophes in variables.  What I'm doing is pulling some
> really really
> really bad data from an Access database and trying to insert
> it into my
> handy dandy Postgres db.
>
> Here's the insert statement:
>     pg_cn.Execute "INSERT into
> itemlist(jobno,area,floor,building,room,elevation,phase,cycle,
> model,w,h,
> d,itemno," & _
>     "finish,mtostatus) values('" & rs!jobno & "','" & rs!area
> & "','" &
> rs!floor & "','" & rs!building & "','" & _
>     rs!room & "','" & rs!elevation & "','" & rs!phase & "','"
> & rs!cycle &
> "','" & rs!model & "','" & rs!w & "','" & rs!h & "','" & _
>     rs!d & "','" & rs!itemno & "','" & rs!finish & "','" &
> rs!mtostatus &
> "')"
>
> rs is an adodb recordset
>
> Now, if rs!d="7'" (I said it was bad data ;)) that tickmark
> throws off the
> whole insert statement. as they're interpreted as the genuine
> ticks.  Has
> anyone come across this or can anyone point me in the right
> direction to
> solve this problem short of removing the users "-' key?
>
> thanks in advance
> corey
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

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

From
"Henshall, Stuart - WCP"
Date:
Try double quoteing (ie '' for ').
Also you can escape it using \'.
Of course bveign able to use \ as an escape character means that when they
really have one you have to give \\ :)
Cheers,
- Stuart

> -----Original Message-----
> From: Corey W. Gibbs [mailto:cgibbs@westmarkproducts.com]
> Sent: 13 March 2002 18:14
> To: pgsql-odbc@postgresql.org
> Subject: Maybe more of a VB question, but here goes
>
>
> Good Morning Everyone,
>
> I'm having a bit of a VB problem, and I don't know quite how
> to solve this
> as my VB knowledge is minimal at best.  The problem is with
> quotes and
> apostrophes in variables.  What I'm doing is pulling some
> really really
> really bad data from an Access database and trying to insert
> it into my
> handy dandy Postgres db.
>
> Here's the insert statement:
>     pg_cn.Execute "INSERT into
> itemlist(jobno,area,floor,building,room,elevation,phase,cycle,
> model,w,h,
> d,itemno," & _
>     "finish,mtostatus) values('" & rs!jobno & "','" & rs!area
> & "','" &
> rs!floor & "','" & rs!building & "','" & _
>     rs!room & "','" & rs!elevation & "','" & rs!phase & "','"
> & rs!cycle &
> "','" & rs!model & "','" & rs!w & "','" & rs!h & "','" & _
>     rs!d & "','" & rs!itemno & "','" & rs!finish & "','" &
> rs!mtostatus &
> "')"
>
> rs is an adodb recordset
>
> Now, if rs!d="7'" (I said it was bad data ;)) that tickmark
> throws off the
> whole insert statement. as they're interpreted as the genuine
> ticks.  Has
> anyone come across this or can anyone point me in the right
> direction to
> solve this problem short of removing the users "-' key?
>
> thanks in advance
> corey
>
>

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

From
Cedar Cox
Date:
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

For Corey's case, it would probably be useful to make a wrapper function to
add a comma on the end, eg..

Function wrapper(text As Variant) As String
        escapeString = escapeString(text) & ", "
End Function

And then just do this:

> Here's the insert statement:
     pg_cn.Execute "INSERT into itemlist(jobno,area,floor,building,room,elevation,phase,cycle,model,w,h,d,itemno," & _
    "finish,mtostatus) values(" & _
    wrapper(rs!jobno) & wrapper(rs!area) & _
    wrapper(rs!floor) & wrapper(rs!building) & _
    wrapper(rs!room) & wrapper(rs!elevation) & _
    wrapper(rs!phase) & wrapper(rs!cycle) & _
    wrapper(rs!model) & wrapper(rs!w) & _
    wrapper(rs!h) & wrapper(rs!d) & _
    wrapper(rs!itemno) & wrapper(rs!finish) & _
    escapeString(rs!mtostatus) & ")"

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

On Thu, 14 Mar 2002, Henshall, Stuart - WCP wrote:

> Try double quoteing (ie '' for ').
> Also you can escape it using \'.
> Of course bveign able to use \ as an escape character means that when they
> really have one you have to give \\ :)
> Cheers,
> - Stuart



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

From
Dave Page
Date:

> -----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.