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