Making ODBC easier

1
Making ODBC easier

• By
• Dave Doulton
• University of Southampton

2
Agenda

• Introduction
• Setting up ODBC sources
• ODBC import
• ODBC pql
• ODBC members
• Conclusion

3
Introduction

• ODBC Open DataBase Connectivity
• This session will show you how to set up ODBC
  sources and use them from SIR.
• It will show the simplest way to go about this
  with PQL members available from SUG website.

4
Setting up ODBC sources

• ODBC sources are set up using Data Sources from
  the Administrative Tools Menu
• Sources can be User, available to that user only
  or System, available to all users both are
  configured in exactly the same way but from
  different tabs

5
(No Transcript)
6
(No Transcript)
7
Setting up ODBC sources

• I shall show the steps in setting up some System
  DSNs
• I have previously set them all up as User DSNs
  for use in the demonstrations
• Click add on the screen shown prior to this

8
(No Transcript)
9
Setting up ODBC sources

• Choose the source and click Finish.
• You will then get a configuration screen which
  will vary according to the source.
• The next screens will show various different types

10
SIR
11
SIR different server
12
CSV
13
CSV options
14
CSV Define Format
15
Excel with options
16
Access with options
17
Access advanced options
18
FoxPro with options
19
dBase with options
20
Paradox with options
21
Oracle
22
All defined
23
ODBC import

• Data can be imported into a SIR database from an
  ODBC source very easily especially into a
  caseless database as there is no Case Id to worry
  about.
• Create a caseless database in this case TESTODBC
  and choose import records from the database menu

24
(No Transcript)
25
Choose ODBC and choose data source
26
Select Source
27
This gives
28
Clicking Next gives- note sheet name with a
and a range
29
Clicking Next gives
30
Change record name and choose roword as sortid.
This gives each row a new number
31
Clicking OK creates record and imports.
32
ODBC import

• This is fine if you want all the data in a
  database.
• But I find that I want to read a data file select
  some data from that file and then do something
  with it without storing it in another database.
  E.g. for running procedures on an Oracle
  database.
• In these cases PQL is the answer.

33
ODBC pql

• The ODBC import is based on a set of pql
  statements.
• These seem a bit daunting at first.
• See the next screens for details.

34
Connect and Disconnect

• CONNECT conid SERVER name DATABASE name
  TABFILE name USER name PASSWORD name PREFIX
  name UPDATE READ ERROR errid
• DISCONNECT conid ERROR name

35
Statement, Prepare Bind Execute and Delete

• STATEMENT statid CONNECT conid ERROR name
• PREPARE STATEMENT statid CONNECT conid COMMAND
  text_expression BUFFER buffer_name ERROR
  name
• BIND STATEMENT statid CONNECT conid
  (param_no,value)
• EXECUTE STATEMENT statid CONNECT conid ERROR
  name
• DELETE STATEMENT statid CONNECT conid ERROR
  name

36
An Example

• See the bottom of the help on ODBC at
• file///C/Program20Files/SIR2002/help/visualpql_
  odbc.htm
• Something easier would clearly be nice.

37
ODBC members

• I have created a simple set of members that can
  be put in the sysproc ODBC family and make life a
  lot easier.
• It includes a simple template member as shown on
  the next screen

38
Template

• program
• call sysproc.odbc.header
• compute srce''
• compute user''
• compute pwd''
• call sysproc.odbc.connect
• compute qtext''
• call sysproc.odbc.loop (C )
• write cname(1) 5e
• write fields(1) 5e
• call sysproc.odbc.endloop (C )
• call sysproc.odbc.disconn
• call sysproc.odbc.tail
• end program

39
The template filled in

• program
• call sysproc.odbc.header
• compute srce'1 CSV'
• compute user'dcd'
• compute pwd'dcd'
• call sysproc.odbc.connect
• compute qtext'select from employee.csv'
• call sysproc.odbc.loop (C )
• write cname(1) 5e
• write fields(1) 5e
• call sysproc.odbc.endloop (C )
• call sysproc.odbc.disconn
• call sysproc.odbc.tail
• end program

40
A more complicated example

• program
• DEFINE PROCEDURE VARIABLES ARRAYS
• string ofns
• call sysproc.odbc.header
• execute subroutine sysproc.menu.odbclist
  returning(srce)
• display textbox "Enter output filename" response
  ok,ofns
• if(ok lt0) exit program
• if(srst(ofns,".csv")0)compute ofnsofns'.csv'
• display textbox "Enter username" response ok,user
• if(ok lt0) exit program
• display textbox "Enter password" secret response
  ok,pwd
• if(ok lt0) exit program
• call sysproc.ODBC.CONNECT
• display textbox "Enter query" response ok,qtext
• if(ok lt0) exit program

41
before report string4000 outline open
ofn/dsnvarofns/write/lrecl4000 compute
outline"" for k1,cnum compute
outlineoutline'"'trim(cname(k))'"' if(kltcnum)c
ompute outlineoutline',' end for write(ofn)outli
ne break level 1, rnum detail block compute
outline"" for k1,cnum ifthen(exists(fields(k)))
compute outlineoutline'"'trim(fields(k))'"' el
se compute outlineoutline'" "' endif if(k
ltcnum)compute outlineoutline',' end
for write(ofn) outline end break level after
report close ofn pql escape '"c\windows\system32
\cmd" /c "start 'ofns'" ' wait 0 minimize
1 end report end program
42
An update example

• program
• call sysproc.odbc.header
• compute srce''
• compute user''
• compute pwd'
• Compute utext
• call sysproc.odbc.connect
• call sysproc..odbc.update
• call sysproc.odbc.disconn
• call sysproc.odbc.tail
• end program

43
Update filled in

• program
• call sysproc.odbc.header
• compute srce3 Access'
• compute userdcd'
• compute pwddcd
• Compute utextupdate employee set ndepends4
• call sysproc.odbc.connect
• call sysproc..odbc.update
• call sysproc.odbc.disconn
• call sysproc.odbc.tail
• end program

44
Web access

• With slight alterations these can be used via the
  web as well.
• See the following example.

45
program DEFINE PROCEDURE VARIABLES ARRAYS string
ofns call sysproc.odbc.header compute
srcecgivarpn("srce") compute usercgivarpn("user"
) compute pwdcgivarpn("pwd") call
sysproc.ODBC.CONNECT compute qtextcgivarpn("qtext
") call sysproc.odbc.loop (C ) perform
procs call sysproc.odbc.endloop (C ) call
sysproc.odbc.disconn call sysproc.odbc.tail report
filenameconsol/sortrnum
46
before report string4000 outline write(cgi)'lth2
gtOutput from 'srce'lt/h2gt' write(cgi)'lth3
gtUsing query 'qtext'lt/h3gt' compute
outline"lttable bordergtlttr gt" for
k1,cnum compute outlineoutline'ltth
gt'trim(cname(k))'lt/thgt' end for compute
outlineoutline'lt/trgt' write(cgi)outline break
level 1, rnum detail block compute outline"lttr
gt" for k1,cnum ifthen(exists(fields(k))) compute
outlineoutline'lttd gt'trim(fields(k))'lt/tdgt' el
se compute outlineoutline'lttd
gtnbsplt/tdgt' endif end for compute
outlineoutline'lt/trgt' write(cgi) outline at end
block end break level after report write(cgi)'lt/ta
blegt' end report end program
47
Notes

• When using sources that can have different file
  extensions then use the extension in the
  tablename for select.
• In Excel use the sheet name with a or a named
  range
• In SIR the password can be all 4 passwords
  separated by commas
• i.e. userpass,company,high,high userpass can be
  anything but is needed always

48
Conclusion

• I hope you will all have a try of these routines
  to speed up your access to other data.
• I know it has speeded up my development work no
  end.