Adodb - Error Command not ended correctly

[HighlanderSword]

Hello,

 

When using the code below i Get an error dialog ORA-00933 SQL Command not Properly ended 

I Have validate I'm connected to the Oracle Database.

I went with a very simple select sysdate from dual;

Any Ideas what I'm doing wrong ?

expandcollapse popup

#include <GUIConstants.au3>

Dim $oMyError

; Initializes COM handler
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

$ado = ObjCreate( "ADODB.Connection" )    ; Create a COM ADODB Object  with the Beta version

With $ado
    ; 'Set data source - for OLEDB this is a tns alias, for ODBC it can be 'either a tns alias or a DSN.
    ; If "provider" is used this means that the ODBC connections is used via DSN.
    ; if Driver is used = "Driver={Microsoft ODBC for Oracle};Server=TNSnames_ora;Uid=demo;Pwd=demo;" then this is a DSN Less connector
    ; More Info for Oracle MS KB Q193332
    .ConnectionString =("Provider='OraOLEDB.Oracle';Data Source='ds';User Id='user';Password='put in password prompt';")
    ;.ConnectionString =("Driver={Microsoft ODBC for Oracle};Server=DVA;Uid=system;Pwd=mypass;")
    .Open
EndWith

$adors = ObjCreate( "ADODB.RecordSet" )    ; Create a Record Set to handles SQL Records

MsgBox(0,"oracle ","opened") ;validated connection opened


With $adors
        .ActiveConnection = $ado
;~         .CursorLocation = "adUseClient"
        ;.LockType = "adLockReadOnly" ; Set ODBC connection read only
        .Source = 'select sysdate from dual;'
        .Open
EndWith

While not $adors.EOF
    For $i = 0 To $adors.Fields.Count - 1
        ConsoleWrite( $adors.Fields( $i ).Value & @TAB )    ; Columns in the AutoIt console use Column Name or Index
    Next
        ConsoleWrite(@CR)
    $adors.MoveNext                                                ; Go to the next record
WEnd

; This COM error Handler
Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1)  ; to check for after this function returns
Endfunc

 

[Nine]

try removing the ; at the end of your sql statement...

[HighlanderSword]

Hello,

 

Thanks that worked.

 

Is there any way to use an Oracle Wallet connection so that you only have to provide the alias ?

 

I have it working for sqlplus not sure how to do it for an ADODB connection

[HighlanderSword]

I found the solution 

 

Provider='OraOLEDB.Oracle';Data Source='TNSNAME';OSAuthent=1

[jchd]

2 hours ago, Nine said:

try removing the ; at the end of your sql statement...

I find it extremely strange that Oracle barks at an SQL statement final semicolumn: it's part of all SQL standard (all versions).

Is that specific to Oracle ADO driver???

[jdelaney]

You would think SQL standards are consistent, but each type has their own nuances. The hypocrisy of a standardized query language.

[jchd]

I know about SQL variants can of worms, but a final ';' is very very very standard basic SQL. If one looks at own Oracle help (I just did) they insist on appending a ; at each statement.

If one resists using non-portable constructs and given that the main purpose of using ADO is DB-engine transparency, I seriously question this peculiar requirement to omit a final ';' only for Oracle RDBMS.

If you have a lot of applications running MySQL (owned by Oracle) and decide to use Oracle RDBMS instead, you have to modify every piece of code interacting with the DB. Geez, that's plain idiocy!

[Skysnake]

@jchd, I am not nearly as skilled as you are with this, but I have noticed some peculiar comments over time concerning DB connections.  First, I love @mLipok ADO UDF.  I think it is the way to go.  However, this connection uses an Object to connect, and as you pointed out elsewhere, we can only work with what the underlying connection (ODBC / DLL) allows.  There was a question some time ago about a Firebird connection.  Turns out that the default config for the Firebird ODBC is ReadOnly. Which bring me to this

; 'Set data source - for OLEDB this is a tns alias, for ODBC it can be 'either a tns alias or a DSN.
    ; If "provider" is used this means that the ODBC connections is used via DSN.
    ; if Driver is used = "Driver={Microsoft ODBC for Oracle};Server=TNSnames_ora;Uid=demo;Pwd=demo;" then this is a DSN Less connector
    ; More Info for Oracle MS KB Q193332
    .ConnectionString =("Provider='OraOLEDB.Oracle';Data Source='ds';User Id='user';Password='put in password prompt';")

We don't know how that connection works.  I agree completely that the SQL ; is expected, but perhaps this specific driver does not use it ?

I would be very keen to know if using the 

ADO UDF we get the same problem?  Or, changing the  ODBC driver changes the bahaviour?