HighlanderSword Posted April 12, 2019 Share Posted April 12, 2019 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 Link to comment Share on other sites More sharing options...
Nine Posted April 12, 2019 Share Posted April 12, 2019 try removing the ; at the end of your sql statement... “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy Link to comment Share on other sites More sharing options...
HighlanderSword Posted April 12, 2019 Author Share Posted April 12, 2019 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 Link to comment Share on other sites More sharing options...
HighlanderSword Posted April 12, 2019 Author Share Posted April 12, 2019 I found the solution Provider='OraOLEDB.Oracle';Data Source='TNSNAME';OSAuthent=1 Link to comment Share on other sites More sharing options...
jchd Posted April 12, 2019 Share Posted April 12, 2019 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
jdelaney Posted April 13, 2019 Share Posted April 13, 2019 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. Link to comment Share on other sites More sharing options...
jchd Posted April 13, 2019 Share Posted April 13, 2019 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
Skysnake Posted April 15, 2019 Share Posted April 15, 2019 @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? Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now