JDD/Data Driver
JDD: Driver Locale | Handles | Data Sources | Data Driver | Error Messages | API: Connect Read Update Create Bulk Insert
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