SQL Query from Informix Database

[unique12u]

I have written my first script and it works, however it is not what I consider to be the best solution. I want to query an informix database and output the data into a CSV file and change the format of the date fields. In order to do this I wrote a query from MS Query and my script calls the query. Here is my script:

Let>filename=C:\Documents and Settings\D800\My Documents\SOC File\Fuel Price Project\Sales Volume Query.dqy

ExecuteFile>filename

WaitWindowOpen>Microsoft Excel -*
Wait>20
Let>xlfile=c:\Documents and Settings\D800\My Documents\SOC File\Fuel Price Project\CSV Files\volume.xls

Press ALT
Send>fa
Release ALT
WaitWindowOpen>Save As

IfFileExists>xlfile,fileexists
Goto>savefile

Label>fileexists
DeleteFile>xlfile

Label>savefile
SetFocus>Save As
Send>xlfile
Press Enter

Press ALT
Send>fx
Release ALT
Send Character/Text>n

VBSTART
Sub Deleterow()
Dim xlApp
Dim xlBook
Dim xlSheet

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.open("c:\Documents and Settings\D800\My Documents\SOC File\Fuel Price Project\CSV Files\volume.xls")
Set xlSheet = xlBook.Worksheets("Sales Volume Query")
xlApp.visible = True

'xlSheet.Rows("1:1").Select
'xlSheet.Selection.delete xlShiftUp
xlSheet.Rows("1:1").delete 1
xlSheet.Columns("C:D").NumberFormat = "yyyy-mm-dd hh:mm:ss"
xlSheet.Range("D1:D500").Formula = "=(C1+.99999)"


End Sub
VBEND

//THIS LINE OPENS XL AND DELETES THE ROW
VBRun>Deleterow


Let>csvfile=c:\Documents and Settings\D800\My Documents\SOC File\Fuel Price Project\CSV Files\volume.csv
SetFocus>Microsoft Excel - *

//Send ALT-fa to save
Press ALT
Send>fa
Release ALT
WaitWindowOpen>Save As

IfFileExists>csvfile,csvfileexists
Goto>csvsavefile

Label>csvfileexists
DeleteFile>csvfile

Label>csvsavefile
SetFocus>Save As
Wait>2
Send Character/Text>c:\Documents and Settings\D800\My Documents\SOC File\Fuel Price Project\CSV Files\volume
Wait>2
Press Tab
Send Character/Text>C
Press Enter
Press Alt
Send Character/Text>S
Release ALT
Press Enter
Send Character/Text>Y


Press ALT
Send>fx
Release ALT
Send Character/Text>N


I would prefer to CONNECT to the ODBC datasource directly and run the SQL, I have NO CLUE how to go about this. I have looked at the ODBC examples, but am not getting anywhere very fast.

Here is the SQL statement:
SELECT er_sales.ersl_prft_ctr, sys_prft_ctr.prft_con_key, er_sales.ersl_date, (er_sales.ersl_date+.99999), inv_header.ivh_product, er_sales.ersl_sls_units, "1", "notes"
FROM ssfactor.er_sales er_sales, ssfactor.inv_header inv_header, ssfactor.sys_prft_ctr sys_prft_ctr
WHERE er_sales.ersl_prft_ctr = sys_prft_ctr.prft_ctr AND er_sales.ersl_prodlnk = inv_header.ivh_link AND ((sys_prft_ctr.prft_ctr Between 100 And 199) AND (er_sales.ersl_date>=(TODAY-5)) AND (er_sales.ersl_prod_type="F") AND (er_sales.ersl_rpt_type="D"))

Here is what I BELIEVE the connect string should be:

Provider=MSDASQL.1;Password=passwd;Persist Security Info=True;User ID=username;Data Source=DSN

Thanks for looking.
Nique

[Marcus Tettmar]

That connection string doesn't look right. I don't have Informix here to try it with I'm afraid but go here:

http://www.connectionstrings.com/

And click in Informix. It suggests something like:

Driver={INFORMIX 3.30 32 BIT};Host=hostname;Server=myserver;Service=service-name;Protocol=olsoctcp;Database=mydb;UID=username;PWD=myPwd

So modifying the example at:
http://www.mjtnet.com/vbsdb.htm

Function GetCustomerName(CustID)
Dim SQLString

set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "Driver={INFORMIX 3.30 32 BIT};Host=hostname;Server=myserver;Service=service-name;Protocol=olsoctcp;Database=mydb;UID=username;PWD=myPwd"

SQLString = "select * from Customers where CustomerID = '" & CustID & "'"
set rsCustomers = MyDB.Execute(SQLString)
etc
etc

Alternatively set up a named datasource in Control Panel/Administrative Tools and then you only need to refer to it by name.

[unique12u]

DSN in my datasources, I do not need the connect string at all? DUH!

Next question related to this script is once I do that, how do I return the SQL data to a CSV file and modify the date formats in the query?

[Marcus Tettmar]

Found another useful page for connection strings:

http://www.carlprothman.net/Default.aspx?tabid=81

This shows my example was slightly wrong. Needs "Dsn='';" & in front of it.

[Marcus Tettmar]

Next question related to this script is once I do that, how do I return the SQL data to a CSV file and modify the date formats in the query?

A number of ways to do it. What I do is construct three VBScript functions. One that opens the datasource and creates a recordset object. The datasource and recordset objects should be declared globally to the VBScript functions. Another function closes and frees the objects. The third implements a MoveNext and returns a semi-colon delimited string of fields. This approach means I can control the process from outside of VBScript and run the MoveNext function in a loop and write the result using WriteLn to create the CSV file.