JDD/Data Driver

JDD access is provided by the Data Driver verbs defined in various script data/dd[...].ijs. Load as:

for MySQL

load 'data/ddmysql'

or

for SQLite

load 'data/ddsqlite'

The Data Driver verbs may be summarized as follows. Here, ch refers to a connection handle, and sh a statement handle. Locale is ommitted for brevity. 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

dddcnt   row fetched by last ddfet/ddfch command, may not be available for some drivers.

r=. dddcnt ''

ddcom   commit a transaction (after a ddtrn)

r=. ddcom ch

ddcon   connect to JDD 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:

database	database name or file system data file name
uid	user name
pwd	user password
nocreate	(only used in sqlite) set to 1 (the default) to connect to existing database. Set to 0 to create empty database if not exists.
server	host name or IP address of RDBMD server
port	port of RDBMD server

Parameters not used by a particular driver will be ignored. If it fails, then first check if it will be OK to connect with the command line utility provided by RDBMS vendors.

For example:

ch=. ddcon 'database=mydata;uid=george;pwd=sesame'

ddconfig   set global config, may not be available in some drivers.

dayno	set to 0 (the default) to be J/ODBC compatible. Set to 1 to encode date/time in a J dayno format.
errret	set to 0 (the default) to be J/ODBC compatible. Set to 1 to return a listed box of return code and result.
unicode	set to 0 (the default) to be J/ODBC compatible. Set to 1 to return string in wide unicode format.

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:
+-----+++--------------------+-----+--------+--------------------+------+-+-+-+
|MYSQL|||127.0.0.1 via TCP/IP|MYSQL|5.1.49-3|libmysqlclient.so.16|5.1.49|2|3|0|
+-----+++--------------------+-----+--------+--------------------+------+-+-+-+

dddis   closes connection handle (disconnects from the data source)

r=. dddis ch

dddriver   return the name of the current JDD data drivers. The value for J/ODBC is 'ODBC'.

r=. dddriver ''

dddrv   list of available JDD data drivers. Actually exactly one row will be returned, just for compatibility with J/ODBC.

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.

Note for errret=1 (set by ddconfig), statement handles will not be closed automatically even if all records have been read. Statement must be close explicitly by applications so closed statement handle numbers will not be recycled without notice.

ddfch as ddfet, but returns data in columns

If _2 is supplied as the left argument, it returns data in raw format:

• numbers in rank-1 array
• character or binary data in rank-1 boxed array.

r=. ddfch sh,n

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. These names can be used as the database= argument to ddcon.

r=. ddsrc ch

ddtbl   returns a statement handle for tables in the data source. Some JDD 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