ODBC/Data Driver
ODBC: Data Sources | Handles | Data Driver | Error Messages | API: Connect Read Update Create Bulk Insert Config
ODBC access is provided by the Data Driver verbs defined in script dd.ijs. Load as:
load 'dd'
The Data Driver verbs may be summarized as follows. Here, ch refers to a connection handle, and sh a statement handle. Note that SQL commands are not case-sensitive.
ddcnm column names of selected data
r=. ddcnm sh
ddcnt rowcount of last ddsql command
r=. ddcnt ''
ddcol column names and atrributes in a database
r=. 'tdata' ddcol ch
ddcolinfo similar to ddcol, but the argument is a statement handle of selected data.
r=. ddcolinfo sh
ddcom commit a transaction (after a ddtrn)
r=. ddcom ch
ddcon connect to ODBC data source. The result is a connection handle. The argument can set several parameters, separated by semicolons. Some are supported by all databases, and others have a meaning only for specific databases. Parameters recognized by most database systems are:
dsn ODBC data source name. Must be the first parameter if used dlg dlg=1 prompts for a connection string with a dialog box with entries for User Id and Password driver driver name for dsn-less connection dbq database file name of MS Access. TSN for Oracle uid user name pwd user password server host name or IP address of RDBMD server port port of RDBMD server modifysql set to 1 (the default) to use ODBC SQL grammar. Set to 0 to use native database grammar. rereadafterupdate set to 1 to force a re-read of a record after an update. This is useful for retrieving auto-updated values such as timestamps. rereadafterinsert set to 1 to force a re-read of a record after an insert
For example:
ch=. ddcon'dsn=mydata;uid=george;pwd=sesame'
Example of Dsn-less connection
ch=. ddcon'driver={Microsoft Access Driver (*.mdb)};dbq=c:\user\bill\jdata.mdb;uid=george;pwd=sesame'
ddconfig set global config, compatibility with JDD data drivers and is a no-op.
r=. ddconfig 'errret';0;'dayno';1;'unicode';1
dddbms return summary information of the current connection handle, containing
data_driver dsn user_id server database_name database_version driver_name driver_ver character_set character_div bug_flag
r=. dddbms ch For example: +----++-----+------+--------+----------+-----------+--------------------------------+-+-+-+ |ODBC||admin|ACCESS|MSACCESS|04.00.0000|ACEODBC.DLL|Microsoft Access database engine|3|1|8| +----++-----+------+--------+----------+-----------+--------------------------------+-+-+-+
dddis closes connection handle (disconnects from the data source)
r=. dddis ch
dddriver compatibility with JDD data drivers. The value returned is 'ODBC'.
r=. dddriver ''
dddrv list of available ODBC data drivers.
r=. dddrv ''
ddend closes statement handle
r=. ddend sh
dderr return error message on last command. An error message is given when a data driver verb returns _1.
r=. dderr ''
ddfet fetch next records from selected data. Note that after you have read a record, the next fetch will not read it again. If you need to read it again, you must select it again.
r=. ddfet sh NB. fetch next record (same as ddfet sh,1) r=. ddfet sh,5 NB. fetch next 5 records r=. ddfet sh,_1 NB. fetch all remaining records.
If you fetch all remaining records using ddfet sh,_1, or if your fetch returns fewer records that you requested (i.e. the fetch reads past the end of the file), then ddfet closes the statement handle. Otherwise, the statement handle remains open, and you should explicitly close it if you have finished reading the file.
ddfch as ddfet, but returns data in columns
r=. ddfch sh,n
ddfetch reads data to bound nouns. This is the most efficient way to read large tables.
r=. ddfetch sh
ddins bulk insert
r=. (selection;data) ddins ch
For example,
r=. ch ddisn~ 'select name,salary from tdata where 1=0'; (>'TOM';'WILLIAM') ,&< 1000 1200
ddparm parameterized query
r=. (query;datatype;data) ddparm ch
For example,
r=. ch ddparm~ 'insert into tdata(name,salary) values (?,?)'; (SQL_VARCHAR,SQL_INTEGER) ; (>'TOM';'WILLIAM') ,&< 1000 1200
ddrbk discards (rollbacks) a transaction (after a ddtrn)
r=. ddrbk ch
ddsel select data from a database, returning a statement handle
sh=. 'select * from tdata' ddsel ch
ddsparm simplified ddparm, no need to specify data type and data length.
r=. (query;data) ddsparm ch
For example,
r=. ch ddsparm~ 'insert into tdata(name,salary) values (?,?)'; (>'TOM';'WILLIAM') ,&< 1000 1200
ddsql execute an SQL statement
r=. 'create table mydata' ddsql ch
ddsrc data source names available from the ODBC manager. These names can be used as the dsn= argument to ddcon.
r=. ddsrc ''
ddtbl returns a statement handle for tables in the data source. Some ODBC drivers do not support this service and the result will be empty.
sh=. ddtbl ch
ddtblx returns all tables in the data source. This is easier to use than ddtbl
r=. ddtblx ch
ddtrn begin a transaction on a connection. Subsequent actions are not committed to the database until a ddcom is done. Actions since the ddtrn can be discarded by doing a ddrbk (rollback).
r=. ddtrn ch
ddttrn test if a handle is inside a state of transaction. It returns 1 if inside transaction, and 0 otherwise.
r=. ddtrn ch