THE -> ' <- (Apostrophe) Problem

[MBS]

Hi all,

What is the most common solution for the issues caused by using the ' character aka chr(39) aka Apostrophe in access databases 2.0 AND 7.0?

I'm so habituated to test any string for this "little pest" replacing it by chr(96) or sometimes just removing it from the string, that I ever forget to search for a most efficient and proper solution.

But now I'm in a situation that I CANNOT change the string (files names).

Maybe it is something so easy that I'm afraid to ask LOL

Thanks guys.

[dilettante]

The problem doesn't exist if you use parameter queries instead of cobbling dynamically generated SQL together on the fly.

This also helps resolve serialization issues related to data types with punctuation (non-integer numeric types, date and time types).

By using prepared parameter queries they only have to be compiled into the DBMSs internal queryplan format once, making repeated queries run faster.

[PlausiblyDamp]

The best solution is to not concatenate strings to build queries, use parameters instead.

If you search these forums you should find a lot of posts on the subject, http://www.vbforums.com/showthread.p...-my-SQL-string is a good place to start

[techgnome]

You mean like when you're inserting the data into the database or something? Oh that's easy. Don't use string concatenatenation. Use parameters.

-tg

[dilettante]

Here is a simple example.

It accepts typed in data and stores it into the single-column CSV file "demo.txt" via Jet 4.0 and ADO:




Here is an example of a created "demo.txt" file:

TextField
"Hello World!"
"O'Leary did it, not me!"
"""Bang"" went the tire as it blew."
"Nothing to it."
"""Fred's Bank"" holds Joe's life savings."

Not much code here really. A lot of it deals with the UI and making sure we have at least an empty "demo.txt" before we begin:

Option Explicit

Private Connection As ADODB.Connection
Private Command As ADODB.Command

Private Sub Command1_Click()
    With Text1
        Connection.InsertNewText .Text
        .Text = vbNullString
        .SetFocus
    End With
End Sub

Private Sub Form_Load()
    Dim F As Integer

    ChDir App.Path
    ChDrive App.Path

    'If no file yet, create an empty one:
    On Error Resume Next
    GetAttr "demo.txt"
    If Err Then
        On Error GoTo 0
        F = FreeFile(0)
        Open "demo.txt" For Output As #F
        Print #F, "TextField"
        Close #F
    Else
        On Error GoTo 0
    End If

    Set Connection = New Connection
    Connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
                  & "Data Source='.';" _
                  & "Extended Properties='Text;Hdr=Yes;Format=Delimited'"
    Set Command = New ADODB.Command
    With Command
        .CommandType = adCmdText
        .CommandText = "PARAMETERS [NewText] TEXT(255);" _
                     & "INSERT INTO [demo.txt]([TextField]) " _
                     & "VALUES([NewText])"
        .Prepared = True
        .Name = "InsertNewText"
        Set .ActiveConnection = Connection
    End With
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Connection.Close
End Sub

Private Sub Text1_Change()
    Command1.Enabled = Len(Text1.Text) > 0
End Sub

[MBS]

The problem doesn't exist if you use parameter queries instead of cobbling dynamically generated SQL together on the fly

The best solution is to not concatenate strings to build queries, use parameters instead.

Don't use string concatenatenation. Use parameters.

Thanks so much for the answers, links and samples guys I will look at it over the weekend.

Wish you all a great weekend.

Regards,
MBS

[Eduardo-]

The more versed in databases here can correct me if I'm wrong, but for query's SQL string building don't you solve that problem just replacing ' with '' (two single quotes)?

[dilettante]

That was a common trick used in old web scripts, but it can fail badly.

Depending on the DBMS there may be other problem characters: quote, NUL, backslash, etc.

And you still have the problem with date/time and number formats.

[Eduardo-]

That was a common trick used in old web scripts, but it can fail badly.

Depending on the DBMS there may be other problem characters: quote, NUL, backslash, etc.

Yes, you need to check for NULL values in the case they can be found in the data. But that's another issue not related to the characters that must be escaped.
I don't know whether it was used by other technologies, but in Access databases it's not just a trick (like something undocumented) but the offficial way to escape the single quote character, because it has an special meaning in the query syntax.

backslash, etc.

I don't know what the problem with the backslash is.

And you still have the problem with date/time and number formats.

Yes, they must be properly handled.

[dilettante]

Er, NUL is a character (vbNullChar, ChrW$(0), etc.), a separate issue from NULL the special value.

I'm surprised we don't see more problems from "comma as decimal point" folks inserting values like 1.1, because most SQL dialects want "period as decimal point."

[Eduardo-]

Er, NUL is a character (vbNullChar, ChrW$(0), etc.), a separate issue from NULL the special value.

And how must be handled that?

[dilettante]

Depends on the DBMS. But just use parameter queries and all of these issues go away.

[ChrisE]

And how must be handled that?

Hi Eduardo,

you can handle it like this..

'liefert einen Stringwert in HochKommata eingeschlossen
Public Function strStr(sString As String) As String

   Dim s As String
   
      If Len(sString) = 0 Then
         strStr = "NULL"
         Exit Function
      End If
   
      'entfernt doppelte Hochkommata ''
      s = sString
      Do While InStr(1, s, Chr(39) & Chr(39)) > 0
         s = Replace(s, Chr(39) & Chr(39), Chr(39))
      Loop
      'Hochkommata doppeln
      s = Replace(s, Chr(39), Chr(39) & Chr(39))

      'entfernt doppelte DoppelHochkommata "
      Do While InStr(1, s, Chr(34) & Chr(34)) > 0
         s = Replace(s, Chr(34) & Chr(34), Chr(34))
      Loop
      'Hochkommata doppeln
      s = Replace(s, Chr(34), Chr(34) & Chr(34))


      'Zeichenkette in Hochkommata einschliessen
      strStr = Chr(39) & s & Chr(39)

End Function

all the other's can be handled to.

regards
Chris

[Eduardo-]

Hi Chris,

Hi Eduardo,

you can handle it like this..

'liefert einen Stringwert in HochKommata eingeschlossen
Public Function strStr(sString As String) As String

   Dim s As String
   
      If Len(sString) = 0 Then
         strStr = "NULL"
         Exit Function
      End If
   
      'entfernt doppelte Hochkommata ''
      s = sString
      Do While InStr(1, s, Chr(39) & Chr(39)) > 0
         s = Replace(s, Chr(39) & Chr(39), Chr(39))
      Loop
      'Hochkommata doppeln
      s = Replace(s, Chr(39), Chr(39) & Chr(39))

      'entfernt doppelte DoppelHochkommata "
      Do While InStr(1, s, Chr(34) & Chr(34)) > 0
         s = Replace(s, Chr(34) & Chr(34), Chr(34))
      Loop
      'Hochkommata doppeln
      s = Replace(s, Chr(34), Chr(34) & Chr(34))


      'Zeichenkette in Hochkommata einschliessen
      strStr = Chr(39) & s & Chr(39)

End Function

all the other's can be handled to.

regards
Chris

Why are you changing the double quotes, I don't see a problem with that.

What I do is to store in the database the string untouched and then in the queries I replace simple quotes with two simple quotes:

Set Rec = DB.OpenRecorset ("SELECT * FROM Table WHERE Name LIKE '*" & FixQuotes(SearchTerm) & "*'")

Public Function FixQuotes(nText As String) As String
    FixQuotes = Replace(nText, ', '')
End Function

[PlausiblyDamp]

Or just do it properly and use parameters, why use "tricks" to change the data you are putting in the database, and then needing to restore it when reading it out, when using parameters solves this problem as well as many others and also protects against things like SQL injection.

[Eduardo-]

Or just do it properly and use parameters, why use "tricks" to change the data you are putting in the database, and then needing to restore it when reading it out, when using parameters solves this problem as well as many others and also protects against things like SQL injection.

Do you mean to have the queries stored in the database?

[ChrisE]

Or just do it properly and use parameters, why use "tricks" to change the data you are putting in the database, and then needing to restore it when reading it out, when using parameters solves this problem as well as many others and also protects against things like SQL injection.

Hi,

have you never asked yourself what the Access developers have programmed in order to send Data to the table?

for example

cmd.Parameters.Append cmd.CreateParameter("empid", adVarChar, adParamInput, 6, txt_empid.Text)

what is programmed behind adVarChar or any other parameter ? probably the same 'tricks'


regards
Chris

[dilettante]

Do you mean to have the queries stored in the database?

See post #5 above.

The simple example there uses an ADO Command object containing a named parameter query. The name becomes an extended method of the Connection object and parameters can be passed as with any method call:

Private Sub Command1_Click()
    With Text1
        Connection.InsertNewText .Text
        .Text = vbNullString
        .SetFocus
    End With
End Sub

This can also be done with stored procedures, even the simple ones that Jet and ACE support that seem to be QueryDefs under the covers.

[PlausiblyDamp]

Hi,

have you never asked yourself what the Access developers have programmed in order to send Data to the table?

for example

cmd.Parameters.Append cmd.CreateParameter("empid", adVarChar, adParamInput, 6, txt_empid.Text)

what is programmed behind adVarChar or any other parameter ? probably the same 'tricks'


regards
Chris

However they are doing this it certainly isn't just string concatenation or some other "trick" this kind of thing is fundamental to how databases work and is not relying on string concatenation or simple tricks to manipulate data in the same way that has been suggested here,

[dilettante]

I believe that under the covers there are indeed "tricks" employed. These are the same kinds of "tricks" we might use ourselves within data passed to/from APIs. It might involve escaping text, or it might involve a length header followed by binary bytes for each data item.

But none of that matters because each DBMS does it a different way.

An ODBC Driver or OLE DB Provider has a published client API and a private server API. The Driver/Provider knows how to talk to the DB engine and this "talk" can be any form they want.

{Program}--{ADO}--{Provider}--{DB Engine}

{Program}--{ADO}--{MSDASQL shim Provider}--{Driver}--{DB Engine}

We don't know and don't need to know the private API.

[Eduardo-]

So you want to throw out all the SQL language alltogether because of the string delimiter character for literals.
You can also throw VB because it has the same issue with double quotes.

[LaVolpe]

What I do is to store in the database the string untouched and then in the queries I replace simple quotes with two simple quotes:

Set Rec = DB.OpenRecorset ("SELECT * FROM Table WHERE Name LIKE '*" & FixQuotes(SearchTerm) & "*'")

Public Function FixQuotes(nText As String) As String
    FixQuotes = Replace(nText, ', '')
End Function

This method is also very common when using the ADO recordset's Filter and Find methods.

[dilettante]

So you want to throw out all the SQL language alltogether because of the string delimiter character for literals.
You can also throw VB because it has the same issue with double quotes.

No. Did you read anything posted above?

    With Command
        .CommandType = adCmdText
        .CommandText = "PARAMETERS [NewText] TEXT(255);" _
                     & "INSERT INTO [demo.txt]([TextField]) " _
                     & "VALUES([NewText])"
        .Prepared = True
        .Name = "InsertNewText"
        Set .ActiveConnection = Connection
    End With

[Eduardo-]

This also leads to the discussion between ADO and DAO, and for local Access databases I prefer DAO because it is faster and also simpler, and the code much more straightforward.

[Eduardo-]

Here is a simple example.

It accepts typed in data and stores it into the single-column CSV file "demo.txt" via Jet 4.0 and ADO:




Here is an example of a created "demo.txt" file:

TextField
"Hello World!"
"O'Leary did it, not me!"
"""Bang"" went the tire as it blew."
"Nothing to it."
"""Fred's Bank"" holds Joe's life savings."

Not much code here really. A lot of it deals with the UI and making sure we have at least an empty "demo.txt" before we begin:

Option Explicit

Private Connection As ADODB.Connection
Private Command As ADODB.Command

Private Sub Command1_Click()
    With Text1
        Connection.InsertNewText .Text
        .Text = vbNullString
        .SetFocus
    End With
End Sub

Private Sub Form_Load()
    Dim F As Integer

    ChDir App.Path
    ChDrive App.Path

    'If no file yet, create an empty one:
    On Error Resume Next
    GetAttr "demo.txt"
    If Err Then
        On Error GoTo 0
        F = FreeFile(0)
        Open "demo.txt" For Output As #F
        Print #F, "TextField"
        Close #F
    Else
        On Error GoTo 0
    End If

    Set Connection = New Connection
    Connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
                  & "Data Source='.';" _
                  & "Extended Properties='Text;Hdr=Yes;Format=Delimited'"
    Set Command = New ADODB.Command
    With Command
        .CommandType = adCmdText
        .CommandText = "PARAMETERS [NewText] TEXT(255);" _
                     & "INSERT INTO [demo.txt]([TextField]) " _
                     & "VALUES([NewText])"
        .Prepared = True
        .Name = "InsertNewText"
        Set .ActiveConnection = Connection
    End With
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Connection.Close
End Sub

Private Sub Text1_Change()
    Command1.Enabled = Len(Text1.Text) > 0
End Sub

When I open the project in the IDE, the first time it executes the line

Connection.InsertNewText .Text

I get the error -2147467259 (80004005) Selected collating sequence not supported by the operating system.

After that, I don't get the error any more.

But if I close the project, and open it again, the fist time it goes to that line it throws the same error again.
Also only the firt time.

[dilettante]

Why quote the entire post? This isn't the UseNet, it is a forum web site. Users can scroll back or you can just put in a link to the post. Or just quote what you need.


This is a well known flaw in the VB6 IDE.

A result returned on the stack after the call isn't handled properly. Lots of ADO methods can produce this problem, but you normally only see it when using something like the Jet Text IISAM or a Desktop ODBC Driver as used here. The Microsoft workaround: run again. At least it doesn't happen in compiled programs.


I swear, lately every thread ends with a Tums Festival.

[dilettante]

Here is a link to one article on this issue:

PRB: Collating Sequence Error Opening ADODB Recordset the First Time Against an Excel XLS