[AccessD] Pls help with ADO to Oracle syntax... Ampersand Character

Resolved, but with puzzling aspects

First, it was actually the semicolon at the end which was the problem.

Next, when I tried out the code to run the ADO command to update the database even having the ampersands in the text, actually there was no problem.

IE - never was a need to try to SET DEFINE OFF or worry about escaping the ampersand because I guess when done through ADO, there is interruption of the routine to ask for a variable, so it treats ampersands like normal text.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Benson, William (GE Global Research, consultant)
Sent: Tuesday, November 20, 2012 1:08 PM
To: Access Developers discussion and problem solving (accessd at databaseadvisors.com)
Subject: [AccessD] Pls help with ADO to Oracle syntax... Ampersand Character

First, I am doing this through Excel VBA, using ADO and working with an Oracle provider. I am having heartburn getting some data pushed from Excel to Oracle. Apparently the command "SET DEFINE OFF" works only in SQL DEVELOPER and SQL PLUS, it is not some SQL one can simply throw at the Oracle provider via SQL. So in order to inject characters like 

g_Conn.Execute "SET DEFINE OFF  INSERT INTO SID_USERS (USER_SSO) VALUES ('Sanford & Sons');", True
       'runtime error... ORA-00922 missing or invalid option

So I wrote a routine to escape characters like ampersand for any text which I plan to insert into a field using ADO and SQL. May not be robust, but it does turned Sanford & Sons into 

'Sanford ' || '&' || 'Sons'

Now, that works in SQL PLUS but it will not work when trying ADO's Execute - I am still getting an "invalid character" error message.

INSERT INTO SID_USERS ( 
USER_SSO, 
CONAME) 
VALUES ( 'x', 
'Sanford ' || '&' || 'Sons');



Function OracleText(str) As String
Const BADCHARS = "&"
Dim strWorkingText As String
Dim strBAD As String
Dim strFinal As String
Dim bMadesubstitution As Boolean
strWorkingText = str
strWorkingText = Replace$(strWorkingText, "'", "''")

Dim i As Long
Do Until strWorkingText = ""
    For i = 1 To Len(strWorkingText)
        bMadesubstitution = False
        If InStr(BADCHARS, Mid(strWorkingText, i, 1)) > 0 Then
            strBAD = Mid(strWorkingText, i, 1)
            strFinal = strFinal & Mid(strWorkingText, 1, i - 1) & "'|| '" & strBAD & "' || '"
            strWorkingText = Mid(strWorkingText, i + 1)
            bMadesubstitution = True
            Exit For
        End If
    Next
    If Not bMadesubstitution Then
        strFinal = strFinal & strWorkingText
        strWorkingText = ""
    End If
Loop
OracleText = strFinal
End Function


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com