Studio/ODBC Basics

From J Wiki
Jump to navigation Jump to search

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
Retrieved from "https://code2.jsoftware.com/mediawiki/index.php?title=Studio/ODBC_Basics&oldid=7295"