Database /odbc

[_pi]

do you have an example on how to connect to a database via odbc
so that i can make sql queries ?

thanks

[Gintaras]

Please wait several days.

[itsjustmatt]

SQL query from a QM will open up a lot of possibilities! I can't wait.
Matt B

[_pi]

currently i use my local apache to make odbc queries to a local mdb.

[Gintaras]

Edit: Now these examples are outdated. In QM 2.1.8.8, working with databases is much simpler using Database class.

Code:

Copy      Help

;Reads from a MS Access database.

str provider="Microsoft.Jet.OLEDB.4.0" ;;data provider for MS Access; part of Win2000 and later
str datasource="$personal$\db1.mdb"; datasource.expandpath ;;MS Access database ($personal$ = My Documents)
str sql="SELECT A, B FROM Sheet1" ;;select fields A and B from "Sheet1" table, all records

typelib ADODB {00000200-0000-0010-8000-00AA006D2EA4} 2.0 ;;the oldest version I've found on my computer

ADODB.Connection conn._create ;;connection object
conn.CursorLocation=ADODB.adUseClient ;;**
str cc.format("Provider='%s';Data Source='%s';" provider datasource) ;;connection string
conn.Open(cc "" "" -1) ;;open without user/password
ADODB.Recordset rs._create ;;*
rs.Open(sql conn ADODB.adOpenStatic ADODB.adLockBatchOptimistic ADODB.adCmdText) ;;execute SQL
rs.MarshalOptions=ADODB.adMarshalModifiedOnly ;;**
;IDispatch di; rs.ActiveConnection=di; conn.Close ;;use this to close connection and continue work with recordset

rep rs.RecordCount ;;for each record
,str s=rs.Fields.Item("A").Value ;;get value of field A
,out s
,rs.MoveNext
,
conn.Close ;;I think this is not necessary, although not documented

;* recordset object is used as container for the retrieved data; can contain multiple records (rows) and fields (columns)
;** not necessary if client and server is on same computer
;_____________________________

;ADO reference: search for "ActiveX Data Objects (ADO)" in http://msdn.microsoft.com/library/
;connection strings: http://www.carlprothman.net/Default.aspx?tabid=81


Code:

Copy      Help

;Reads from a MS Access database.

str provider="Microsoft.Jet.OLEDB.4.0"
str datasource="$personal$\db1.mdb"; datasource.expandpath
str sql="SELECT A, B FROM Sheet1"

typelib ADODB {00000200-0000-0010-8000-00AA006D2EA4} 2.0

ADODB.Recordset rs._create
str cc.format("Provider='%s';Data Source='%s';" provider datasource) ;;connection string
rs.Open(sql cc ADODB.adOpenStatic ADODB.adLockBatchOptimistic ADODB.adCmdText) ;;open connection and execute SQL

int r f
for r 0 rs.RecordCount ;;for each record
,out "-- record %i --" r+1
,for(f 0 rs.Fields.Count) out rs.Fields.Item(f).Value ;;display all fields
,rs.MoveNext

rs.Close ;;I think this is not necessary, although not documented


Code:

Copy      Help

;Adds a record in a MS Access database.

str provider="Microsoft.Jet.OLEDB.4.0"
str datasource="$personal$\db1.mdb"; datasource.expandpath
str sql="INSERT INTO Sheet1 VALUES(10,20,30)"

typelib ADODB {00000200-0000-0010-8000-00AA006D2EA4} 2.0

ADODB.Connection conn._create
str cc.format("Provider='%s';Data Source='%s';" provider datasource)
conn.Open(cc "" "" -1)
conn.Execute(sql @ -1)
conn.Close

Code:

Copy      Help

;Reads single record (row) from MS Access database db1.mdb table "Table1".
;Table structure: ID (string), A (long), B (string).
;Searches for ID="Hoonah" and retrieves whole record.

str datasource="$personal$\db1.mdb" ;;($personal$=My Documents)
str table="Table1"
str ID="Hoonah"
str sql.format("SELECT * FROM %s WHERE ID='%s'" table ID)

str provider="Microsoft.Jet.OLEDB.4.0"
str cc.format("Provider='%s';Data Source='%s';" provider datasource.expandpath) ;;connection string

typelib ADODB {00000200-0000-0010-8000-00AA006D2EA4} 2.0

ADODB.Connection conn._create
ADODB.Recordset rs._create
conn.Open(cc "" "" -1)

rs.Open(sql conn ADODB.adOpenStatic ADODB.adLockBatchOptimistic ADODB.adCmdText) ;;execute SQL

int i1=rs.Fields.Item(1).Value
str s2=rs.Fields.Item(2).Value
out i1
out s2

[ken gray]

WOW!
i know the 2nd one works great.

i did add an

Code:

Copy      Help

err

after the

Code:

Copy      Help

,for(f 0 rs.Fields.Count) out rs.Fields.Item(f).Value ;;display all fields


to keep the "Error (RT) in Macro: 0x80020005, Type mismatch." out of the results.

[_pi]

thanks.

[Nithin]

If a database has a password how can i pass on the password in the sql to fetch the data from a mdb file

[Gintaras]

In QM 2.1.8.8, you can use Database class. Type

Code:

Copy      Help

Database

, press F1, ....

[Nithin]

Thanks,
Nithin

[Nithin]

refer to the attachment

Iam not able to proceed.....

[Gintaras]

You don't need the code inside the function. Just call the function, like in examples in "Database Help" macro.