Jump to content
HighlanderSword

Adodb - Error Command not ended correctly

Recommended Posts

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 ?

#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

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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???


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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


IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

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!


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

@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?

:)

 


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...