ODBC/Data Driver

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