Studio/ODBC Basics
Lab: ODBC: Basic ODBC Author: John D. Baker bakerjd@kos.net
What is ODBC?
ODBC (Open DataBase Connectivity) is a standard API for accessing relational data. ODBC is commonly used with SQL databases but is also used to access data in spreadsheets and other files.
An ODBC application has three basic parts.
One: a datasource - usually an SQL database.
Two: an ODBC driver that reads & writes the datasource.
Three: an application interface that calls the driver.
This lab shows how to use the basic facilities of the J ODBC interface. First load the basic interface script.
load 'dd'
Check DSN registrations?
NOTE: This lab uses the following ODBC datasources. If these datasoures are not registered use the ODBC control panel applet to register them.
DSN FILE, DIRECTORY or DATABASE
--------------------------------------------- jdata FoxPro
or dBase driver
jpath '~system\examples\data
jaccess ACCESS driver
jpath '~system\examples\data\jdata.mdb
NB. check registrations - will be list of 1's
NB. if all DSN's are registered.
(;:'jdata jaccess') e. {."1 ddsrc 0
1 1
Connection handles
The first step to using ODBC is to connect to a datasource. The verb (ddcon) connects datasources and returns an integer connection handle.
NB. connect the jdata source - assign connection handle ch ]ch =: ddcon 'dsn=jdata' 51975488 NB. If ch is _1 an error occurred - otherwise NB. there are no errors and the result of dderr is empty NB. If you get an error check your ODBC registrations. dderr ''
Listing tables and views in a datasource
After establishing a datasource connection you frequently want to find out what tables and views you can access.
NB. list tables, views and queries in connected datasource ddtblx ch ┌────────────────────────────────┬┬─────┬─────┬┐ │C:\NSG\J601\SYSTEM\EXAMPLES\DATA││addr │TABLE││ │C:\NSG\J601\SYSTEM\EXAMPLES\DATA││dept │TABLE││ │C:\NSG\J601\SYSTEM\EXAMPLES\DATA││emp │TABLE││ │C:\NSG\J601\SYSTEM\EXAMPLES\DATA││loc │TABLE││ │C:\NSG\J601\SYSTEM\EXAMPLES\DATA││tdata│TABLE││ └────────────────────────────────┴┴─────┴─────┴┘
Listing columns in tables
It is also useful to determine what columns are in particular tables.
NB. show the columns of tdata 'tdata' ddcol ch ┌────────────────────────────────┬───────────┬──────────┬───────────┬─────────┬─────────┬───────────┬─────────────┬──────────────┬──────────────┬────────┬───────┬──────────┬─────────────┬────────────────┬─────────────────┬────────────────┬───────────┬─────... │TABLE_CAT │TABLE_SCHEM│TABLE_NAME│COLUMN_NAME│DATA_TYPE│TYPE_NAME│COLUMN_SIZE│BUFFER_LENGTH│DECIMAL_DIGITS│NUM_PREC_RADIX│NULLABLE│REMARKS│COLUMN_DEF│SQL_DATA_TYPE│SQL_DATETIME_SUB│CHAR_OCTET_LENGTH│ORDINAL_POSITION│IS_NULLABLE│ORDIN... ├────────────────────────────────┼───────────┼──────────┼───────────┼─────────┼─────────┼───────────┼─────────────┼──────────────┼──────────────┼────────┼───────┼──────────┼─────────────┼────────────────┼─────────────────┼────────────────┼───────────┼─────... │C:\NSG\J601\SYSTEM\EXAMPLES\DATA│ │tdata │NAME │12 │Char │14 │28 │ │ │1 │ │ │12 │ │28 │1 │YES │1 ... ├────────────────────────────────┼───────────┼──────────┼───────────┼─────────┼─────────┼───────────┼─────────────┼──────────────┼──────────────┼────────┼───────┼──────────┼─────────────┼────────────────┼─────────────────┼────────────────┼───────────┼─────... │C:\NSG\J601\SYSTEM\EXAMPLES\DATA│ │tdata │SEX │12 │Char │1 │2 │ │ │1 │ │ │12 │ │2 │2 │YES │2 ... ├────────────────────────────────┼───────────┼──────────┼───────────┼─────────┼─────────┼───────────┼─────────────┼──────────────┼──────────────┼────────┼───────┼──────────┼─────────────┼────────────────┼─────────────────┼────────────────┼───────────┼─────... │C:\NSG\J601\SYSTEM\EXAMPLES\DATA│ │tdata │DEPT │12 │Char │4 │8 │ │ │1 │ │ │12 │ │8 │3 │YES │3 ... ├────────────────────────────────┼───────────┼──────────┼───────────┼─────────┼─────────┼───────────┼─────────────┼──────────────┼──────────────┼────────┼───────┼──────────┼─────────────┼────────────────┼─────────────────┼────────────────┼───────────┼─────... │C:\NSG\J601\SYSTEM\EXAMPLES\DATA│ │tdata │DOB │8 │Numeric │53 │8 │ │2 │1 │ │ │8 │ │ │4 │YES │4 ... ├────────────────────────────────┼───────────┼──────────┼───────────┼─────────┼─────────┼───────────┼─────────────┼──────────────┼──────────────┼────────┼───────┼──────────┼─────────────┼────────────────┼─────────────────┼────────────────┼───────────┼─────... │C:\NSG\J601\SYSTEM\EXAMPLES\DATA│ │tdata │DOH │8 │Numeric │53 │8 │ │2 │1 │ │ │8 │ │ │5 │YES │5 ... ├────────────────────────────────┼───────────┼──────────┼───────────┼─────────┼─────────┼───────────┼─────────────┼──────────────┼──────────────┼────────┼───────┼──────────┼─────────────┼────────────────┼─────────────────┼────────────────┼───────────┼─────... │C:\NSG\J601\SYSTEM\EXAMPLES\DATA│ │tdata │SALARY │8 │Numeric │53 │8 │ │2 │1 │ │ │8 │ │ │6 │YES │6 ... └────────────────────────────────┴───────────┴──────────┴───────────┴─────────┴─────────┴───────────┴─────────────┴──────────────┴──────────────┴────────┴───────┴──────────┴─────────────┴────────────────┴─────────────────┴────────────────┴───────────┴─────... NB. show the columns of addr 'addr' ddcol ch ┌────────────────────────────────┬───────────┬──────────┬───────────┬─────────┬─────────┬───────────┬─────────────┬──────────────┬──────────────┬────────┬───────┬──────────┬─────────────┬────────────────┬─────────────────┬────────────────┬───────────┬─────... │TABLE_CAT │TABLE_SCHEM│TABLE_NAME│COLUMN_NAME│DATA_TYPE│TYPE_NAME│COLUMN_SIZE│BUFFER_LENGTH│DECIMAL_DIGITS│NUM_PREC_RADIX│NULLABLE│REMARKS│COLUMN_DEF│SQL_DATA_TYPE│SQL_DATETIME_SUB│CHAR_OCTET_LENGTH│ORDINAL_POSITION│IS_NULLABLE│ORDIN... ├────────────────────────────────┼───────────┼──────────┼───────────┼─────────┼─────────┼───────────┼─────────────┼──────────────┼──────────────┼────────┼───────┼──────────┼─────────────┼────────────────┼─────────────────┼────────────────┼───────────┼─────... │C:\NSG\J601\SYSTEM\EXAMPLES\DATA│ │addr │FIRST_NAME │12 │Char │8 │16 │ │ │1 │ │ │12 │ │16 │1 │YES │1 ... ├────────────────────────────────┼───────────┼──────────┼───────────┼─────────┼─────────┼───────────┼─────────────┼──────────────┼──────────────┼────────┼───────┼──────────┼─────────────┼────────────────┼─────────────────┼────────────────┼───────────┼─────... │C:\NSG\J601\SYSTEM\EXAMPLES\DATA│ │addr │LAST_NAME │12 │Char │10 │20 │ │ │1 │ │ │12 │ │20 │2 │YES │2 ... ├────────────────────────────────┼───────────┼──────────┼───────────┼─────────┼─────────┼───────────┼─────────────┼──────────────┼──────────────┼────────┼───────┼──────────┼─────────────┼────────────────┼─────────────────┼────────────────┼───────────┼─────... │C:\NSG\J601\SYSTEM\EXAMPLES\DATA│ │addr │EMP_ID │12 │Char │6 │12 │ │ │1 │ │ │12 │ │12 │3 │YES │3 ... ├────────────────────────────────┼───────────┼──────────┼───────────┼─────────┼─────────┼───────────┼─────────────┼──────────────┼──────────────┼────────┼───────┼──────────┼─────────────┼────────────────┼─────────────────┼────────────────┼───────────┼─────... │C:\NSG\J601\SYSTEM\EXAMPLES\DATA│ │addr │STREET │12 │Char │20 │40 │ │ │1 │ │ │12 │ │40 │4 │YES │4 ... ├────────────────────────────────┼───────────┼──────────┼───────────┼─────────┼─────────┼───────────┼─────────────┼──────────────┼──────────────┼────────┼───────┼──────────┼─────────────┼────────────────┼─────────────────┼────────────────┼───────────┼─────... │C:\NSG\J601\SYSTEM\EXAMPLES\DATA│ │addr │CITY │12 │Char │10 │20 │ │ │1 │ │ │12 │ │20 │5 │YES │5 ... ├────────────────────────────────┼───────────┼──────────┼───────────┼─────────┼─────────┼───────────┼─────────────┼──────────────┼──────────────┼────────┼───────┼──────────┼─────────────┼────────────────┼─────────────────┼────────────────┼───────────┼─────... │C:\NSG\J601\SYSTEM\EXAMPLES\DATA│ │addr │STATE │12 │Char │2 │4 │ │ │1 │ │ │12 │ │4 │6 │YES │6 ... ├────────────────────────────────┼───────────┼──────────┼───────────┼─────────┼─────────┼───────────┼─────────────┼──────────────┼──────────────┼────────┼───────┼──────────┼─────────────┼────────────────┼─────────────────┼────────────────┼───────────┼─────... │C:\NSG\J601\SYSTEM\EXAMPLES\DATA│ │addr │ZIP │8 │Numeric │53 │8 │ │2 │1 │ │ │8 │ │ │7 │YES │7 ... └────────────────────────────────┴───────────┴──────────┴───────────┴─────────┴─────────┴───────────┴─────────────┴──────────────┴──────────────┴────────┴───────┴──────────┴─────────────┴────────────────┴─────────────────┴────────────────┴───────────┴─────...
Statement handles
It us handy to know how many rows are in a table. To do this submit an SQL query to the database using the (ddsel) verb. The result of (ddsel) is an integer called a statement handle.
NB. count the rows in tdata - assign statement handle sh sh =: 'select count(*) from tdata' ddsel ch
Fetching data
The statement handle is a reference to selected data. To access the data it must be fetched. The following fetch operation returns a single boxed row count number.
NB. fetch the data associated with statement handle sh ddfch sh ┌──┐ │25│ └──┘ NB. close the statement handle ddend sh 0
Now select all the rows of tdata and fetch the first 10 rows.
NB. select all rows of tdata sh =: 'select * from tdata' ddsel ch NB. fetch first 10 rows - arg is stmt and rows ]dat =: ddfch sh,10 ┌──────────────┬─┬────┬─────────┬─────────┬─────┐ │Macdonald B │F│D101│1.95906e7│1.97805e7│32591│ │Genereaux S │F│D103│1.94503e7│1.96602e7│95415│ │Koebel R │M│D101│1.93711e7│1.98009e7│63374│ │Keller J │F│D101│1.95105e7│1.97404e7│48898│ │Twa L │F│D108│1.95507e7│1.98004e7│49075│ │Blamire J │F│D101│1.96008e7│1.97912e7│46469│ │Gordon E │F│D103│1.95202e7│1.97908e7│29960│ │Denny D │F│D101│1.94908e7│1.98004e7│46939│ │Newton R │M│D108│1.95601e7│1.97902e7│73368│ │Miller S │F│D103│1.96501e7│1.98303e7│43418│ └──────────────┴─┴────┴─────────┴─────────┴─────┘
(dat) is a boxed list of tables.
$&.> dat ┌─────┬────┬────┬────┬────┬────┐ │10 14│10 1│10 4│10 1│10 1│10 1│ └─────┴────┴────┴────┴────┴────┘
Fetching all table data.
A second argument of _1 means return all the rows. If some rows have already been fetched _1 means all the remaining rows.
NB. get the remaining rows - statement handle is closed NB. when all the rows are returned. ddfch sh,_1 ┌──────────────┬─┬────┬─────────┬─────────┬──────┐ │Dingee S │M│D103│ 1.9641e7│1.98309e7│ 46877│ │Bugg P │F│D101│1.92011e7│1.95804e7│ 47165│ │Lafrance R │F│D101│1.95202e7│1.98302e7│ 47017│ │Rogerson G │M│D101│1.95712e7│1.98302e7│108777│ │Brando D │F│D108│1.95904e7│1.97708e7│ 44931│ │Anctil J │M│D108│1.94606e7│1.97906e7│ 60974│ │Bauerlein J │F│D103│1.96204e7│1.98409e7│ 33668│ │O'Keefe D │M│D101│1.93903e7│ 1.9671e7│ 66377│ │Beale D │F│D103│1.95703e7│1.97404e7│ 48023│ │Chesher D │F│D103│ 1.9561e7│1.98408e7│ 35184│ │Cahill G │M│D108│1.93205e7│ 1.9671e7│ 81358│ │Abbott K │M│D103│ 1.9631e7│1.98309e7│ 50817│ │McKee M │F│D103│1.96004e7│1.98104e7│ 43115│ │Livingston P │F│D101│1.95809e7│1.98008e7│ 50010│ │Holliss D │F│D101│1.96005e7│1.97707e7│ 46313│ └──────────────┴─┴────┴─────────┴─────────┴──────┘
Selecting and fetching a table subset
SQL is very useful for specifying subsets of tables
NB. compare male and female salaries sh =: 'select avg(salary) from tdata where sex = ''F''' ddsel ch female =: ,> ddfch sh,_1 sh =: 'select avg(salary) from tdata where sex = ''M''' ddsel ch male =: ,> ddfch sh,_1 male,female 68990.3 46364.2
Multiple datasources
More than one datasource can be connected.
ch2 =: ddcon 'dsn=jaccess' NB. list tables, views et cetera ddtblx ch2 ┌──────────────────────────────────────┬┬─────────────────┬────────────┬┐ │C:\NSG\J601\SYSTEM\EXAMPLES\DATA\jdata││MSysACEs │SYSTEM TABLE││ │C:\NSG\J601\SYSTEM\EXAMPLES\DATA\jdata││MSysModules │SYSTEM TABLE││ │C:\NSG\J601\SYSTEM\EXAMPLES\DATA\jdata││MSysModules2 │SYSTEM TABLE││ │C:\NSG\J601\SYSTEM\EXAMPLES\DATA\jdata││MSysObjects │SYSTEM TABLE││ │C:\NSG\J601\SYSTEM\EXAMPLES\DATA\jdata││MSysQueries │SYSTEM TABLE││ │C:\NSG\J601\SYSTEM\EXAMPLES\DATA\jdata││MSysRelationships│SYSTEM TABLE││ │C:\NSG\J601\SYSTEM\EXAMPLES\DATA\jdata││tdata │TABLE ││ └──────────────────────────────────────┴┴─────────────────┴────────────┴┘
Transactions and Modifying data
ODBC can also update and modify data. Before modifying the data of an important table it is often useful to start a transaction so the updates can be rolled back (cancelled) if necessary. Not all ODBC drivers support transactions. The Access driver is one that does.
NB. start a transaction
ddtrn ch2
0
NB. add a row to tdata
sql =: 'insert into tdata (name,sex,dept,dob,doh,salary) '
sql =: sql , ' values (''Mouse, Mickey'',''M'',''XDEP'',19320101,19990101,666666)'
NB. insert row
sql ddsql ch2
0
NB. number of rows affected
ddcnt ''
1
Deleting data
Wait a minute this is not a Mickey Mouse operation let us do a little corporate downsizing and turf the rodent. This can be down with a delete command.
NB. delete a record 'delete from tdata where dept = ''XDEP''' ddsql ch2 0 NB. number of rows affected ddcnt '' 1
Rolling back transactions
The company is about to make a public offerring. To drive up the stock price management decides to cut salaries. Column values can be modified with an update command.
NB. give everyone a pay cut sql =: 'update tdata set salary = salary - round(salary * 0.10)' sql ddsql ch2 0 NB. how many people took a hit ddcnt '' 25
It seems the union was not happy and managed a successful boycott of the main product of the company. To smooth feathers the paycut was rolled back.
NB. roll back all transactions ddrbk ch2 0
Committing transactions
Despite the worst quarter in the history of the company, bad PR, pending shareholder lawsuits and an embitterred workforce the CEO feels he has done a good job and gives himself a hefty raise. This is one transaction that will be committed.
NB. new transaction ddtrn ch2 0 sql =:'update tdata set salary = ' sql =: sql, 'salary + 50000 where name like ''Rogerson%''' sql ddsql ch2 0 ddcnt '' 1 NB. commit transaction ddcom ch2 0
You now have been introduced to ODBC basics. Other labs will introduce more advanced material.
NB. disconnect datasources dddis ch,ch2 0 0
ODBC error messages
The data driver verbs that open and close connections, and send SQL statements, all return a number. Typically, if the number is positive, it is a handle. If the number is 0, it means the operation completed successfully (but the function returns no handle). If the number is _1, it means there was some error. You can get more information about the errror using dderr. For example, try closing a non-existent statement handle:
ddend 42 _1 dderr ISI04 Bad statement handle