How to open a Sybase SQL Anywhere database and query its table?

[sfresher]

The system already Sybase SQL installed. And under it's interactive SQL, I have used "SELECT * FROM allevents" to sucessfully queried the table.

However, when I used autoit, it returns compiling error: "no such table: allevents"

$hDB = _SQLite_Open ("C:\Database.db" = ":memory:")
_SQLite_Exec ($hDB, "SELECT * FROM allevents")

Edited October 2, 2008 by sfresher

[PsaltyDS]

The system already Sybase SQL installed. And under it's interactive SQL, I have used "SELECT * FROM allevents" to sucessfully queried the table.

However, when I used autoit, it returns compiling error: "no such table: allevents"

$hDB = _SQLite_Open ("C:\Database.db" = ":memory:")
_SQLite_Exec ($hDB, "SELECT * FROM allevents")

SQLite has nothing to do with Sybase. Since $hDB is not a valid handle to a database, nothing you do will work.

Even ignoring Sybase and just using SQLite, you either open a database file, or open a DB in memory, not both. So '_SQLite_Open ("C:\Database.db" = ":memory:")' was wrong also.

Perhaps you mean to be making an ADO connection to Sybase? Have you Googled up the appropriate connection string for you version of Sybase, or found it in your documentation?

P.S. I see now where you got the weird opening string. In the help file entry for _SQLite_Open( [ $sDatabase_Filename = ":memory:" ] ).

The parameter is optional (square brackets) and by default will be ":memory:". So _SQLite_Open() will open a DB in memory. If you want to open a file, you just pass the path to the file: $hDB = _SQLite_Open("C:\Database.db"). But that would either have to be a new SQLite DB file you are creating, or an existing SQLite DB file. This will not work on a Sybase file.

>_<

Edited October 2, 2008 by PsaltyDS

[sfresher]

Thanks for your explaination. But my programming experience is soley depent on the UDF of autoit.

I don't quite get your point. Let's say I have have a Sybase database file, saved as "Database1.db". And I want to open it and read the table into a 2d array. Does it simply mean open a file? or I have to make an ADO connection to execute the SELECT command? By the way, I have tried to use the _sql.au3 file published in the forum, but still cannot make it work.

SQLite has nothing to do with Sybase. Since $hDB is not a valid handle to a database, nothing you do will work.

Even ignoring Sybase and just using SQLite, you either open a database file, or open a DB in memory, not both. So '_SQLite_Open ("C:\Database.db" = ":memory:")' was wrong also.

Perhaps you mean to be making an ADO connection to Sybase? Have you Googled up the appropriate connection string for you version of Sybase, or found it in your documentation?

P.S. I see now where you got the weird opening string. In the help file entry for _SQLite_Open( [ $sDatabase_Filename = ":memory:" ] ).

The parameter is optional (square brackets) and by default will be ":memory:". So _SQLite_Open() will open a DB in memory. If you want to open a file, you just pass the path to the file: $hDB = _SQLite_Open("C:\Database.db"). But that would either have to be a new SQLite DB file you are creating, or an existing SQLite DB file. This will not work on a Sybase file.

>_<

[PsaltyDS]

Thanks for your explaination. But my programming experience is soley depent on the UDF of autoit.

I don't quite get your point. Let's say I have have a Sybase database file, saved as "Database1.db". And I want to open it and read the table into a 2d array. Does it simply mean open a file? or I have to make an ADO connection to execute the SELECT command? By the way, I have tried to use the _sql.au3 file published in the forum, but still cannot make it work.

Unless by some cosmic-scale coincidence a Sybase DB file happens to be the same format as an SQLite DB file, the SQLite.au3 UDF will NOT help you here.

A Sybase database file is meant to opened and accessed via a Sybase Server Service. You don't access the file directly. You access the Sybase service (i.e. via ADO connection, or by Sybase Client software) and the service passes you the data. Is there as Sybase Server Service running here, or do you just have a bare .db file?

[sfresher]

Unless by some cosmic-scale coincidence a Sybase DB file happens to be the same format as an SQLite DB file, the SQLite.au3 UDF will NOT help you here.

A Sybase database file is meant to opened and accessed via a Sybase Server Service. You don't access the file directly. You access the Sybase service (i.e. via ADO connection, or by Sybase Client software) and the service passes you the data. Is there as Sybase Server Service running here, or do you just have a bare .db file?

Hi, the Sybase is installed as the database component of the program I am going to run automation test. In control panel, I don't see any ODBC configuration. And in windows services, I don't see any ODBC or Sybase services.

The program come with an Interactive SQL (as shown in the pic), named dbisqlc.exe, the only prarameter passed is "-c dsn=SiteManagerDB", where SiteManager is name of the database sits under the same folder. It appears that no username and password required. I can execute the SELECT command and read the table from this GUI. The thing I really want to do is read the table into a 2d array. What's the best way for me?

[PsaltyDS]

Hi, the Sybase is installed as the database component of the program I am going to run automation test. In control panel, I don't see any ODBC configuration. And in windows services, I don't see any ODBC or Sybase services.

The program come with an Interactive SQL (as shown in the pic), named dbisqlc.exe, the only prarameter passed is "-c dsn=SiteManagerDB", where SiteManager is name of the database sits under the same folder. It appears that no username and password required. I can execute the SELECT command and read the table from this GUI. The thing I really want to do is read the table into a 2d array. What's the best way for me?

You might be in trouble. It sounds like you have Sybase libraries compiled into an application with no external interface exposed. If no ODBC or ADO compatible driver is loaded that can access the DB, I don't know what you can do. Do you have the option of trying to install such a driver, if you can find one? Is there a Sybase expert available, or tech support for the app? This is getting over my head...

[Zedna]

I successfully use ADO DB on Sybase ASE with this DSN string: "Driver={SYBASE SYSTEM 11};Srvr=%server%;Database=master;Uid=admin;Pwd=password;"

You may also try to use Interactive SQL via its commandline.

[sfresher]

You might be in trouble. It sounds like you have Sybase libraries compiled into an application with no external interface exposed. If no ODBC or ADO compatible driver is loaded that can access the DB, I don't know what you can do. Do you have the option of trying to install such a driver, if you can find one? Is there a Sybase expert available, or tech support for the app? This is getting over my head...

Here is my code:

Msgbox(0,"","Start the Script and load the error handler")
_SQLRegisterErrorHandler();register the error handler to prevent hard crash on COM error

$con = _SQLStartup()
If @error then Msgbox(0,"Error","Error starting ADODB.Connection")

;Create a database
_sqlConnect(-1,"localhost","C:\SiteManagerDB.db","","")
if @Error then _DisplayError($SQLErr)  
_SQLClose()

Func _SQLConnect($ConHandle,$server, $db, $username, $password)
    
    DIM $SQLErr
    If $ConHandle = -1 then $ConHandle = $sqlLastConnection
    $ConHandle.Open ("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";") ;<==Connect with required credentials
    If NOT @error then return 1
    Return SetError(1,0,0)
    
EndFunc

If I specify DRIVER={SQL Server}, I receive this error: "[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL server does not exist or access denited."

If i specify DRIVER={SYBASE SYSTEM 11}, I receive this error: "[Microsoft][ODBC Driver Manager]Data source name not found and no default driver specified."

Can you expand this?

Edited October 3, 2008 by sfresher

[PsaltyDS]

I successfully use ADO DB on Sybase ASE with this DSN string: "Driver={SYBASE SYSTEM 11};Srvr=%server%;Database=master;Uid=admin;Pwd=password;"

You may also try to use Interactive SQL via its commandline.

I found similar connection strings with Google, but they are used to connect to a running Sybase server service. I think sfresher has an embedded database in a third-party app that doesn't expose an interface to the internal database:

Hi, the Sybase is installed as the database component of the program I am going to run automation test. In control panel, I don't see any ODBC configuration. And in windows services, I don't see any ODBC or Sybase services.

As for the commandline, the tool he has seems to be from the third-party app, not Sybase, and is GUI based.

Edited October 3, 2008 by PsaltyDS

[Zedna]

I found similar connection strings with Google, but they are used to connect to a running Sybase server service. I think sfresher has an embedded database in a third-party app that doesn't expose an interface to the internal database:

You are right.

As for the commandline, the tool he has seems to be from the third-party app, not Sybase, and is GUI based.

You are not right. Interactive SQL is Sybase application and as far as I know it supports also commandline.

[sfresher]

I have googled this command line instruction for interactive SQL: http://www.ianywhere.com/developer/product...n9/00000590.htm

But I really don't know how to code it. Can someone give me an example to help me start with it?

[sfresher]

bump

[mgillow]

Please bear in mind that I am an AutoIt noob - my programming experience is primarily InstallShield installscript code. But I work with an application that uses Sybase Adaptive Server Anywhere as its DB engine and experimented a little. I ran this on a machine with the Sybase engine already launched - I didn't mess with having the DSN auto-start the engine. In my setup, there is a System DSN named "TestMe" listed in the ODBC Data Sources view. You should have either a System or User DSN named "SiteManagerDB" in yours based on your dbisqlc note. If I am using dbisqlc to connect, I also have to provide the user "admin" and password "adminpwd" to make the connection - thus the UID and PWD parts in $ConnString in my code. In your case, either the DB requires no authentication to connect, or the uid and pwd are stored in the DSN. My DB has a table named "messagequeue" with a cloumn "machine_name". This table has multiple records. This example results in a single-dimensional array.

CODE

$objErr = ObjEvent("AutoIt.Error","MyErrFunc")

Func MyErrFunc()

$hexnum=hex($objErr.number,8)

Msgbox(0,"","We intercepted a COM Error!!" & @CRLF & @CRLF & _

"err.description is: " & $objErr.description & @CRLF & _

"err.windescription is: " & $objErr.windescription & @CRLF & _

"err.lastdllerror is: " & $objErr.lastdllerror & @CRLF & _

"err.scriptline is: " & $objErr.scriptline & @CRLF & _

"err.number is: " & $hexnum & @CRLF & _

"err.source is: " & $objErr.source & @CRLF & _

"err.helpfile is: " & $objErr.helpfile & @CRLF & _

"err.helpcontext is: " & $objErr.helpcontext _

)

exit

EndFunc

$ConnString = "Provider=ASAProv;Data source=TestDSN;UID=admin;PWD=adminpwd"

$ado = ObjCreate("ADODB.Connection")

if @error = 1 Then

MsgBox(0, "Error", "ObjCreate failed - $ado")

Exit

EndIf

$adors = ObjCreate("ADODB.RecordSet")

if @error = 1 Then

MsgBox(0, "Error", "ObjCreate failed - $adors")

Exit

EndIf

$ado.Open($ConnString)

$adors.Open("SELECT machine_name from messagequeue", $ado)

; could check if $adors.BOF AND $adors.EOF (empty result set)

While not $adors.EOF

$machinename = $adors.Fields("Machine_name").value

MsgBox(0, "Result", "Machinename = " & $machinename)

$adors.MoveNext

WEnd

$adors.Close

$ado.Close

$adors=0

$ado=0

[ChuckGo]

Hope this helps

run,dbisql -nogui -C "DatabaseFile=FileName.db;DatabaseKey=YourDBkeyIfEncrypted;EngineName=Pickan-EnginName;UID=Account to LOGinToDB;PWD=PassWord" Some TSQL code Select etc.;

[boy233]

I'm having difficulty making the connection using the example.

$ConnString = "Provider=ASAProv;Data source=DBA;UID=dacarmo;PWD=dacarmo"

Appears: Database server not found

Do I need to put the ip of the machine to access?

How do I?