exchange by lanyuehua

VIEWS: 15 PAGES: 135

									Exchanging Data between SIR
     and other systems

               By
          Dave Doulton
    University of Southampton
Agenda
• Introduction
• Setting up ODBC sources
• ODBC import
• ODBC pql
• ODBC members
• Using sirweb.cgi
• ODBC and the sirweb.cgi
• ODBC and the PQLServer
• Conclusion
Introduction

• For years the only easy way to exchange
  data between SIR and other systems was
  through files.
• SIR procedures could be used to write files
  for specific systems i.e. SPSS,SAS dbase
  etc.
Introduction
Introduction

• SIR PQL can be written to create scripts and files
  to build databases in other systems such as
  Oracle and MySql
• SIR batch data input could be used to import
  data into SIR from other systems provided they
  could write fixed column data.
• SIR PQL can be written to read other data
  formats.
Introduction

• With the advent of ODBC life becomes a lot easier
  for all ODBC enabled systems.
• Another system that has become very popular is
  the web. Getting data to and from that is now very
  easy.
• Cross platform access is now possible for these
  technologies as well.
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.
• It will show how to access SIR data via ODBC from
  other packages.
• It will show how to exchange data using the web.
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
Setting up ODBC sources

• There is another type of DSN labelled File
  DSN this is an alternative way of storing the
  ODBC parameters. User and System store
  the details in the registry, File stores them
  as the name implies in a file, it can point to
  the registry for parameters if desired.
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
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
SIR
SIR different server
CSV
CSV options
CSV Define Format
Excel with options




•   To refer to data use the name of a named region i.e. select * from mydata
•   Or the sheet name followed by a $ and in double quotes i.e. select * from “Sheet1$”
Access with options
Access advanced options
FoxPro with options
dBase with options
Paradox with options
Oracle
MySql
All defined
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
Choose ODBC and choose data source
Select Source
This gives
Clicking Next gives:-
note sheet name with a $ and a range
Clicking Next gives
Change record name and choose roword as sortid. This gives each
row a new number
Clicking OK creates record and imports.
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.
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.
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]
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]
An Example

See the bottom of the help on ODBC at
file:///C:/Program%20Files/SIR2002/help/visua
   lpql_odbc.htm#statment


Something easier would clearly be nice.
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
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
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
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
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
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
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
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 $ appended and in
  double quotes 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
Accessing SIR data from Access

• To access SIR data via ODBC you need to set up
  an ODBC source to the SIR database as was done
  earlier.
• In an Access database choose file/get external
  data/import. In the drop down files of type box
  choose ODBC databases
• Select the source fill in the passwords etc.
• Choose the record type to import and click OK
Accessing SIR data from Excel
• Choose Data/Import External Data/New Database Query.
• On the Databases tab
• Choose the ODBC source
• Make sure the Use the Query Wizard to create/edit queries
  checkbox is unticked
• Click OK
• Fill in ODBC parameters
• Click OK
Accessing SIR data from Excel
• Choose the record type
• Click ADD
• Click Close
• Double Click on the * at the top of the fields list to get all fields
• or Double Click on the fields you want in the order you want
  them.
• Choose File/Return Data to Microsoft Office Excel
• Choose where you want the data placing and click OK
Accessing SIR data from other packages

• Either use the ODBC route if it exists.
• Or use the inbuilt procedures to create files
  that the package can use.
• Or use SIR pql and ODBC to update/insert
  data in the other package using the update
  or insert SQL statements instead of select.
Using sirweb.cgi

• Another way that you may wish to access data is
  through a web browser.
• Users do not have to have SIR or any ODBC
  software installed on their machines at all.
• The data can be displayed and entered in a
  multitude of ways to suit the application.
Using sirweb.cgi

 I will assume that you have a web server
 set up and that it is configured to allow SIR
 to work and that the permissions have
 been correctly set up.
Using sirweb.cgi

If this has been done you should have 2
directories a webroot directory and a
subdirectory of it called cgi-bin.
For the examples you also need 2 additional
subdirectories download and images.
The images directory should at least contain red.gif
  from the SIR images directory.
Using sirweb.cgi

• HTML HyperText Markup Language is a
  way of marking text for the web that allows
  for links to other documents and also
  importantly for dynamic pages a means to
  supply data to the server in order to
  influence the later pages.
Using sirweb.cgi

• A document has an overall structure with variations
  to the internals of the document.
• These are created by using tags within the text.
• These tags have the following format <[/]keyword
  [parameter].. [name=value].. [/] >
• Where the items in [ ] are optional and .. Means
  can be repeated.
Using sirweb.cgi

• Examples are
• <HTML> </HTML>
• <BR/>
• <OPTION SELECTED VALUE=“3”>
• <TH ALIGN=“LEFT” COLSPAN=“3”>
Using sirweb.cgi
• A TAG normally has a <KEYWORD … > and a </KEYWORD>
  surrounding some other text if there is no included text this can
  be reduced to <KEYWORD… />
• <BR/> just means put in a line break so has no included text.
• Browsers allow tags to be missed out so accept <BR> instead
  of <BR></BR> or <BR/>
• Even when there would be included text as in <P> the
  paragraph marker, the </P> is assumed when the next <P> is
  encountered.
Using sirweb.cgi

• Other TAGS that are common are in text
• <TABLE> and <TR>,<TH> and <TD> for drawing
  tables.
• <A …> as an anchor for a link to another document
• <PRE> to say this text is preformatted
• <B> To make bold <FONT …> to specify a font
• <OL> ,<UL> <LI>for lists
• <IMG …> for images
Using sirweb.cgi
• There are tags for form elements which we shall look at later.


• Other TAGS are for declaring things to the browser for effects
  on the client side <DIV> <LINK> <META> .


• <SCRIPT>to specify programs that work in the client (browser)
  which are useful for data cleaning/checking before
  transmission to the server
Using sirweb.cgi
• A Document has the following structure
<HTML>
<HEAD>
<TITLE> Title to appear on the window</TITLE>
</HEAD>
<BODY>
  ….. Other marked up text i.e. the main text.
</BODY>
</HTML>
Using sirweb.cgi

• A form is a page with a section delimited by
  <FORM> and </FORM> tags.
• Between these tags are input elements which are
  used to hold/collect data which is transmitted to the
  server when the form is submitted.
• Usually each item has a name and a value which
  are transmitted as a pair. The tag specifies the
  type of widget.
Using sirweb.cgi
• Common types of widget are
• Radio, checkbox and select for widgets that do not require text
  to be typed.
• Hidden is a special textual widget where the text is not typed
  and is not visible on the page.
• Text, textarea, password for normal textual widgets where text
  is typed.
• Images.
• Buttons for submit and reset.
Using sirweb.cgi
• The widgets are specified as follows
• Radio
<input type=“radio” name=“rad” value=“yes” checked>
<input type=“radio” name=“rad” value=“no”>


• If no value attribute is specified the default is on
• Checked specifies that that is the selected state when the page is

   opened or reset.
Using sirweb.cgi
• Check box
<INPUT type="checkbox" name="check" value=“ok”
  checked>
• Checked states that the box is ticked when the page
  opens or is reset.
• If no value attribute is present the value on is sent.
• Data in this case is only sent when the box is checked.
Using sirweb.cgi

• Hidden
<INPUT type="hidden" name="RUNFILE"
  value="sample.pql">
• The name and value are passed but the
  widget does not appear on the form.
Using sirweb.cgi
• Select (drop down box)
<SELECT name=“drop" >
<OPTION value=“yes” SELECTED>Yes
<OPTION value=“no” >No
</SELECT>
• Note not an INPUT type=
• SELECTED specifies the value selected when the page opens or is reset
• If no value attribute is specified the value sent is that after the <option >
   markup
Using sirweb.cgi
• Select (list box)
<SELECT name=“list“ size=2 >
<OPTION value=“yes” SELECTED>Yes
<OPTION value=“no”>No
</SELECT>
• Note not an INPUT type=
• Selected specifies the value selected when the page opens or is reset
• Size specifies number of items in the list.
• If no value attribute is specified the value sent is that after the <option >
   markup
Using sirweb.cgi

• Text
<INPUT type="text" name="text" value=“yes”>
• Password
<INPUT type="password" name="password" >
• Textarea
<TEXTAREA name="area" rows="5" >
Note not an <INPUT TYPE
Using sirweb.cgi
• Images
<INPUT type="image" name="image" value="picture"
  src="/images/menu.gif">
• SRC specifies where the image is stored on the server
• The value attribute has no effect.
• The values returned are the x and y coordinates with the
  image with .x and .y appended to the name
Using sirweb.cgi
• Buttons
• SUBMIT
<INPUT type="submit" name="submit" value="submit" >
• Sends the data to the server
• RESET
<INPUT type="reset" name="reset" value="reset" >
• Sets widgets back to the state they were in when the page was
  opened.
• The value attribute specifies the label on the button.
Using sirweb.cgi

• The FORM tag says what is going to perform the
  server processing and the method by which data is
  transmitted.
<FORM method="post" action="/cgi-bin/sirweb.cgi">
• The METHOD can be get, put or post
• The action for SIR is as above
Using sirweb.cgi

• The Common Gateway Interface (CGI)
  specifies how data is passed from the client
  to the server and server to client.
• There are 3 ways of getting data into any
  program and each of these is possible with
  CGI.
Using sirweb.cgi

1. Through an environment variable which
   the program accesses by a function call.
2. Reading from a file stream in this case
   Standard Input
3. As a command line argument to the
   program
Using sirweb.cgi

• 1 The data is placed in an environment variable
  QUERY_STRING in the format
  name=value&name=value&name=value
• The values are ALPHA-NUMERIC only plus %
• Non ALPHA-NUMERICS are coded as %xx where
  xx are the hex equivalent of the ASCII codes of the
  characters.
Using sirweb.cgi

• 2 The data is presented to standard input in
  the same way as it is stored in
  QUERY_STRING.
Using sirweb.cgi

• 3 the data is sent as a single value, with no
  name and is also stored in
  QUERY_STRING. This method is only used
  by <ISINDEX> which is a deprecated
  method which existed before forms.
Using sirweb.cgi
• Data is sent from the program as standard output from the
  server to the client.
• It has the form of a header followed by the HTML to create the
  next page in the browser.
• The header consists of 2 lines a content-type line and a blank
  line.
• The content-type specifies what sort of data the browser can
  expect to receive so that it knows what to do with it.
Using sirweb.cgi

• The default content-type for html is
• Content-Type: text/html
• However you may want to set other content
  types such as
• Content-Type: image/svg+xml
Using sirweb.cgi

• Environment variables are also used to pass
  system information to the script.
• These can have information on things like
  the page that called the script.
• The machine that called the script.
Using sirweb.cgi

• SIR provides a cgi script called sirweb.cgi.
• sirweb.cgi provides an interface to the CGI
  standard
• sirweb.cgi executes any PQL that would run
  OK in sirbatch but with some added
  functions
Using sirweb.cgi

• The added functionality consists of
• The cgi functions:
Cgivarpn,cgivarsv,cgibufpn and cgibufsv
• The file:
cgi
Using sirweb.cgi

• The cgixxxpn functions allow the PQL
  function to access the data passed from the
  form by name without needing to know how
  it got there i.e. as environmental parameters
  or as standard input
Using sirweb.cgi

• The cgivarpn function places the result in a
  variable whereas cgibufpn places it in a
  buffer.
• They take care of decoding the strings as
  well.
Using sirweb.cgi

• The cgixxxsv functions access server
  environmental variables by name.
• They are cgivarsv and cgibufsv
Using sirweb.cgi

• The cgi file is a special file that behaves
  differently depending on where it is used.
• If it is used in an program run from
  sirweb.cgi it writes to standard output so
  sends output to the browser whereas when
  used elsewhere the output goes to the file
  sircgi.htm
Using sirweb.cgi

• Note that output from write
i.e write ‘hello’
• And any system output gets sent back to the
  browser when using sirweb.cgi
i.e. print back on
Using sirweb.cgi

• How do you use sirweb.cgi?
• On a form set the action attribute to
“/cgi-bin/sirweb.cgi”
i.e.
<form name =“aform” method=“get”
  action=“/cgi-bin/sirweb.cgi” >
Using sirweb.cgi

• How does sirweb.cgi know what PQL to execute?
On the form have a hidden field whose name is
 sirapp and has as a value the name of a member
 in the sysproc procfile.
This is because SIR knows where the sysproc is on
  the machine and thus has a starting point.
Using sirweb.cgi

• What if I do not want to change the sysproc
  file?
• There are several members in the
  sysproc.cgi family that will help
• runfile, runmembr,dbrun
Using sirweb.cgi

• RUNFILE
Expects a field named RUNFILE that specifies
 the name of a file in the same directory as
 sirweb.cgi , usually cgi-bin.
Using sirweb.cgi

• RUNMEMBR
• Expects 2 fields
PROCFILE containing the full name of a procfile i.e.
“c:\programfiles\sir2002\test\company.sr4”
MEMBER containing the member in that procfile to
 execute
Using sirweb.cgi
•   DBRUN
•   Expects up to 6 fields
P containing the prefix of the database
DB containing the database name
PW containing the password
RS containing the read security
WS containing the write security
EX containing the member to call
Using sirweb.cgi

• The fields for these can be real fields that are
  entered or hidden fields if they are fixed values.
• However it is not a good idea to have passwords
  as hidden values as although they are not visible
  on screen they can be seen by using view source
  in the browser.
• Use password fields for these.
Using sirweb.cgi

• Now you can create an html document
  including form tags and input fields plus a
  submit button you can send data to a PQL
  program/retrieval in a file or member.
Using sirweb.cgi

• How do you get output back to the browser?
• There are 3 ways
• 1 Easy
• 2 Not quite so easy
• 3 Hard
Using sirweb.cgi

• 1 Easy
• Use a SIR procedure that has an HTML
  option
• i.e. descriptive
• DESCRIPTIVE VARIABLE=CURRPOS
/ FILENAME=CGI/HTML
Using sirweb.cgi
• 2 Not quite so easy
• Use write statements to standard output or file cgi
• File cgi writes to standard output from sirweb.cgi but to
  sirweb.htm in any batch or interactive use.
• Note that HTML code look like SIR global variables so
  you either need to make sure they have contain a non-
  alphanumeric a space will do or call
  sysproc.tools.htmlcode first.
Using sirweb.cgi
• 3 Hard
• Write the output to a file in the filestore and create a link on the
  output actually sent to the browser to let the user open the file.
• write(cgi)'<a href="/download/employee.csv">Click here for
  output </A><BR/>'
• csv save file VARIABLE=all/header/
/ FILENAME='c:\inetpub\wwwroot\download\employee.csv'
Using sirweb.cgi

• If you want to send other output to the
  browser you may have to play with the
  Content_type for example to send Scalable
  Vector Graphics in XML you need to set the
  Content_type to image/svg+xml by using
• <input type=hidden name=Content
  value="image/svg+xml">
Using sirweb.cgi

• The default Content_type is text/html
• If the Variable Content is set this is used as
  the Content_type unless it is set to PQL in
  which case the sirweb.cgi script sends
  nothing and the Program must send the full
  header including blank line.
Using sirweb.cgi

• Security
• There are several ways to make you data more
  secure including password protection set up either
  by the web server or through your own checks in
  PQL.
• i.e. have a starting form that asks for a username
  and password and not let the user any further if
  they are not correct.
Using sirweb.cgi

• Security
• Use the HTTP_REFERER to make sure this
  page is called from the page you expect it to
  be called from.
• Use REMOTE_ADDR or REMOTE_HOST
  to check where the request is coming from.
Using sirweb.cgi

•   A couple of examples of generic Web
    interfaces.
1. Edit
2. Graphics via svg and xml
ODBC and the sirweb.cgi

• This will allow you to access ODBC data from the
  web on a PC running a webserver and ODBC.
• It provides a means of reading Access and
  redisplaying the data on the web.
• Using SIR means you are using something you
  know about rather than having to learn asp or
  some other technology.
ODBC and the sirweb.cgi

• With slight alterations the ODBC templates
  can be used via the web as well.
• See the following example.
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
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
ODBC and the sirweb.cgi

  Web browser         PC
                   Web server

    PC                 PC
 ODBC Server       Sirweb.cgi
ODBC and the PQLServer

• You might well say this is OK if you run a
  PC based webserver but we use a Unix
  webserver so it cannot use the ODBC
  access to get at Access data.
• Here is where PQL server from SIR/XS
  comes into play
ODBC and the PQLServer

• Running PQLserver on the PC means that it
  can access the ODBC data and send it back
  to the sirweb.cgi running on the Unix
  webserver for display.
• This is all done with PQL we know and
  understand.
ODBC and the PQLServer

  Web browser      Linux
                  Web server

    PC              Linux
  PQLserver       Sirweb.cgi




      PC
     Access
ODBC and the PQLServer

  Web browser      Linux
                  Web server

    PC              Linux
  PQLserver       Sirweb.cgi

                    Linux
                    Oracle
ODBC and the PQLServer

  Web browser      Linux
                  Web server

    PC              Linux
  PQLserver       Sirweb.cgi

                    Linux
                    Mysql
Conclusion

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

								
To top