How to connect the Greenplum database using AutoIt?

[shreeks]

Hi,

Following is the issue that we are facing now. I’ve no idea of how to connect the Greenplum database using AutoIt. Could anyone share any threads/discussion(s)/information regarding this?

Background info : Greenplum runs on Linux environment.

Thanks.

[water]

I think what you need is similar to every database you want to connect to:

* A driver to install on the windows machine plus information to configure the driver

* A connectionstring that lets Autoit connect to the database

There seems to be a Windows Connectivity Package (at least for 3.3.7.0) which contains the driver.

The connection string should be similar to a PostgreSQL connection string (check this)

Edited May 2, 2011 by water

[shreeks]

My problem goes like this. My AutoIt code has to automatically

1. Create a table in DB.

2. Insert a value in DB.

3. Subsequently close the DB.

The above three steps has to be automated using AutoIt. Can anyone share information w.r.t this? Glad, if you could probably share a sample code w.r.t this issue. Thanks.

I can provide you sample credentials if you want.

[water]

I've never heard of Greenplum before but as Wikipedia states "The Greenplum Database is built from modified PostgreSQL in a massively parallel processing (MPP) database".

1) You need a driver to be installed on the Windows machine to connect to the Greenplum database

2) We will talk about the next steps if step 1) is OK

I don't know how you can get the driver. But the Greenplum community might be helpful.

[water]

Hi,

I keep getting an error - "Only Object-type variables allowed in a "With" statement". How to rectify it?

How to declare a variable of object type?Can someone give me the declaration syntax? Thanks.

So you did install the driver on the Windows PC?

Did you define a DSN?

How does your code look so far?

[shreeks]

So you did install the driver on the Windows PC?

Did you define a DSN?

How does your code look so far?

#include "MySQL.au3"
$UserName = "wfd_read"
$Password = "wfd_read"
$Database = "ESA_DevArea"
$MySQLServerName = "intcosead"
$SQLInstance = _MySQLConnect($UserName,$Password,$Database,$MySQLServerName)
Local $error,$SQLInstance
If $error=1 Then
MsgBox(4096, "@Error variable", "Error variable")
If $SQLInstance=0 Then
MsgBox(4096, "@$SQLInstance", "$SQLInstance variable")
EndIf
EndIf
MsgBox(4096, "WFD DB Connect", "DB Connection is working correctly!")
$NameList = ""
$SQLCode = "select * from dbo.Associate where AssociateID=CSK897"
$TableContents = _Query($SQLInstance,$SQLCode)
With $TableContents
While Not .EOF
$NameList &= .Fields ("AssociateName").value & " " & .Fields ("GradeDescription").value & @CRLF
.MoveNext
WEnd
EndWith
MsgBox(0,"Guest List",$NameList)
_MySQLEnd($SQLInstance)

The above code tries the following :

1. Connect to a DB - ESA_DevArea.

2. Retreive a person’s record whose AssociateID=CSK897.

3. Print the step(2).

4. Close the connection.

Tentatively, we are not trying with Greenplum(GP) DB rather - we tried connecting to SQL Server DB by using the above AutoIt code. But it doesn’t work and pops up with the following error screen.

>"C:\Program Files\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:\Users\171067\Desktop\DB_Connect\DB_Connect_try1.au3" /autoit3dir "C:\Program Files\AutoIt3" /UserParams    
+>14:38:53 Starting AutoIt3Wrapper v.2.0.1.24    Environment(Language:0409  Keyboard:00000409  OS:WIN_7/  CPU:X64 OS:X86)
>Running AU3Check (1.54.19.0)  from:C:\Program Files\AutoIt3
+>14:38:53 AU3Check ended.rc:0
>Running:(3.3.6.1):C:\Program Files\AutoIt3\autoit3.exe "C:\Users\171067\Desktop\DB_Connect\DB_Connect_try1.au3"    
C:\Users\171067\Desktop\DB_Connect\DB_Connect_try1.au3 (18) : ==> Only Object-type variables allowed in a "With" statement.:
With $TableContents
With ^ ERROR
->14:38:55 AutoIT3.exe ended.rc:1
>Exit code: 1    Time: 3.220

I tried some search on net for the reason and it was stated by one person that, “the errors above maybe are the result of a missing database and / or connection. you need a mysql server to connect to and set up the database dcot on it first”. Was the above code didn’t work since we used SQL Server in place of MySQL? Could anyone help us out in this issue? Thanks.

[shreeks]

So you did install the driver on the Windows PC?

Did you define a DSN?

How does your code look so far?

Hi Water,

The reply for this message is in the immediate below message.Thanks.

[water]

OK, changed situation. I would really appreciate if we could check your setup step by step!

1) Please make sure you have a driver for SQL Server installed. Please start "odbcad32.exe" and check the driver tab for a "SQL Server" entry

2) If there is an entry for the "SQL Server" then please check the Tabs USER-DSN, SYSTEM-DSN or DATASET-DSN for an entry that points to your SQL Server database

Please post the results. Then we will goto the next step.

Edited May 3, 2011 by water

[shreeks]

OK, changed situation. I would really appreciate if we could check your setup step by step!

1) Please make sure you have a driver for SQL Server installed. Please start "odbcad32.exe" and check the driver tab for a "SQL Server" entry

2) If there is an entry for the "SQL Server" then please check the Tabs USER-DSN, SYSTEM-DSN or DATASET-DSN for an entry that points to your SQL Server database

Please post the results. Then we will goto the next step.

Hi,

Step 1 holds fine for me as I could find "SQL Server" entry in the driver tab.

Step 2 : Only USER-DSN and SYSTEM-DSN is present when I ran "odbcad32.exe" in Run prompt. There is no entry(that points to your SQL Server database) in SYSTEM-DSN as well as in USER-DSN tabs respectively.

[water]

Fine.

So the next steps are:

3) Download This will allow to access the SQL db and issue commands

4) The following script will connect to the db and run a query (but without displaying any result). You have to change the lines markes with "==>".

#include <_sql.au3>
#include <array.au3>
Opt ("trayIconDebug",1)
_SQL_RegisterErrorHandler() ; register the error handler to prevent hard crash on COM error
$oADODB = _SQL_Startup()
If $oADODB = $SQL_ERROR then Exit Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())       
If _sql_Connect(-1,"server","database","user","password") = $SQL_ERROR then  ; ==> change server, database, user and password to your needs
    Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
    _SQL_Close()
    Exit
EndIf
If _SQL_Execute(-1,"your sql query") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg()) ; ==> change your query here
_SQL_Close()

Edited May 3, 2011 by water

[shreeks]

Thanks Water. It pops up a message box saying 'Connection Error'. I guess there should something wrong with my DB Credentials. I'll probably check out.

What might be the symptoms for this 'Connection Error' ? Any idea is welcome. Between, thanks a lot for this reply. Thank you once again.

[water]

If you run the example from SciTe do you get an error on the console?

How did you define the parameters "server" and "database"? Could you please post the strings (you can replace sensible information with *)?

[shreeks]

If you run the example from SciTe do you get an error on the console?

How did you define the parameters "server" and "database"? Could you please post the strings (you can replace sensible information with *)?

Below is the code that I ran in ScitE editor.

#include <_sql.au3>
#include <array.au3>
Opt ("trayIconDebug",1)
_SQL_RegisterErrorHandler() ; register the error handler to prevent hard crash on COM error
$oADODB = _SQL_Startup()
If $oADODB = $SQL_ERROR then Exit Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
If _sql_Connect(-1,"Ctsintcosead","ESAReports_DevArea","wfd_read","wfd_read") = $SQL_ERROR then  ; ==> change server, database, user and password to your needs
    Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
    _SQL_Close()
    Exit
EndIf
If _SQL_Execute(-1,"select * from dbo.Associate where AssociateID=246897") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg()) ; ==> change your query here
_SQL_Close()

I just compiled the code and ran it. I got a message box popped up saying - Connection Error.

[water]

Please just press F5 in SciTe to run the code uncompiled - the error handler does a consolewrite which is lost when the script is run compiled.

I will be off for two days and respond again on friday.

[shreeks]

Please just press F5 in SciTe to run the code uncompiled - the error handler does a consolewrite which is lost when the script is run compiled.

I will be off for two days and respond again on friday.

Hi Water,

The issue is resolved. I'm able to connect to the DB and execute the 'select; query by making slight modifications with the post that you posted. Thanks a lot and it was a timely help.

Could you please tell me to how to print the retrieved data and display it in the console/Msgbox?

[shreeks]

Hi Water,

Thanks a lot for your help.

I could now connect to DB, run a sample query and retreive data using AutoIt. Thanks.