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

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

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

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:/Program%20Files/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 <=0) exit program • if(srst(ofns,".csv")=0)compute ofns=ofns+'.csv' • display textbox "Enter username" response ok,user • if(ok <=0) exit program • display textbox "Enter password" secret response ok,pwd • if(ok <=0) exit program • call sysproc.ODBC.CONNECT • display textbox "Enter query" response ok,qtext • if(ok <=0) exit program • call sysproc.odbc.loop | ($C $) • perform procs • call sysproc.odbc.endloop • call sysproc.odbc.disconn • call sysproc.odbc.tail • report filename=consol/sort=rnum

41. before report string*4000 outline open ofn/dsnvar=ofns/write/lrecl=4000 compute outline="" for k=1,cnum compute outline=outline+'"'+trim(cname(k))+'"' if(k<cnum)compute outline=outline+',' end for write(ofn)outline break level 1, rnum detail block compute outline="" for k=1,cnum ifthen(exists(fields(k))) compute outline=outline+'"'+trim(fields(k))+'"' else compute outline=outline+'" "' endif if(k <cnum)compute outline=outline+',' 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 srce=‘3 Access' • compute user=‘dcd' • compute pwd=‘dcd‘ • Compute utext=‘update employee set ndepends=4’ • 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 srce=cgivarpn("srce") compute user=cgivarpn("user") compute pwd=cgivarpn("pwd") call sysproc.ODBC.CONNECT compute qtext=cgivarpn("qtext") call sysproc.odbc.loop ($C $) perform procs call sysproc.odbc.endloop ($C $) call sysproc.odbc.disconn call sysproc.odbc.tail report filename=consol/sort=rnum

46. before report string*4000 outline write(cgi)['<h2 >Output from '+srce+'</h2>'] write(cgi)['<h3 >Using query '+qtext+'</h3>'] compute outline="<table border=><tr >" for k=1,cnum compute outline=outline+'<th >'+trim(cname(k))+'</th>' end for compute outline=outline+'</tr>' write(cgi)outline break level 1, rnum detail block compute outline="<tr >" for k=1,cnum ifthen(exists(fields(k))) compute outline=outline+'<td >'+trim(fields(k))+'</td>' else compute outline=outline+'<td >&nbsp;</td>' endif end for compute outline=outline+'</tr>' write(cgi) outline at end block end break level after report write(cgi)'</table>' 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.