SEG 3550 Fundamentals of Information System

1
SEG 3550 - Fundamentals of Information System

• Tutorial 8 ProC Programming

2
Outline

• ProC Programming
• Libraries to use
• Query execution in ProC
• Note to remember
• Pure Text Editor vi
• Makefile

3
Libraries to be used

• ltsqlda.hgt
• SQL Descriptor area
• ltsqlcpr.hgt
• Platform-specific ANSI prototypes for SQLLIB
• ltsqlca.hgt
• SQL Communication area (error handling)

4
Simple descriptions

• ltsqlda.hgt
• A collection of variables required for execution
  of the SQL DESCRIBE stmt.
• Carry the required data structure definition
• ltsqlca.hgt
• Driver Manager
• Return error info. back to the application

5
Connect to the Database

• EXEC SQL CONNECT username IDENTIFIED BY
  password
• All variables to be included in the SQL have to
  start with , e.g. username

6
Create Tables

• EXEC SQL CREATE TABLE studentRecord (id int, name
  varchar(15))
• Add keywords EXEC SQL and do the rest like you
  did before
• But remember to have the operation keywords be in
  capital

7
Insert and variables passing

• int x char y int z
• EXEC SQL INSERT INTO emp(empno, ename, deptno)
  VALUES(x, y, z)
• EXEC SQL INSERT INTO emp(empno, ename, deptno)
• VALUES(x 1,  / LEGAL the reference is
  to x /
•             'Big Shot',  / LEGAL but not
  really a host var/
• array2)/ LEGAL array element is fine /
• EXEC SQL INSERT INTO emp(empno, ename, deptno)
• VALUES(x, y, ((array2))) / ILLEGAL
  although it has an lvalue/
• EXEC SQL INSERT INTO emp(empno, ename, deptno)
• VALUES(x, y, get_deptno() )      / ILLEGAL
  no function calls/
• EXEC SQL INSERT INTO emp(empno, ename, deptno)
  VALUES(x, y, (get_depnoptr())) / ILLEGAL
  although it has an lvalue/

8
Query

• Get one record
• EXEC SQL SELECT id INTO id FROM studentRecord
  where name name
• Get list of records
• EXEC SQL DECLARE cursorRecord CURSOR FOR SELECT
  id, name FROM studentRecord
• EXEC SQL OPEN cursorRecord
• EXEC SQL WHENEVER NOT FOUND DO break
• for()
• EXEC SQL FETCH cursorRecord INTO id, name
• printf("ID d\tName s\n", id, name)
• EXEC SQL WHENEVER NOT FOUND CONTINUE

9
Error Handler
We should always handle the errors

• EXEC SQL WHENEVER SQLERROR DO functionName(arg)
• Define a function, E.g.
• void sql_error(char msg)
• char err_msg128
• size_t buf_len, msg_len
• EXEC SQL WHENEVER SQLERROR CONTINUE
• printf("\ns\n", msg)
• buf_len sizeof (err_msg)
• sqlglm(err_msg, buf_len, msg_len)
• printf(".s\n", msg_len, err_msg)
• EXEC SQL ROLLBACK RELEASE
• exit(EXIT_FAILURE)

Get full length error message
10
Points to note

• The file name for your ProC program must end
  with .pc
• Remember to source the oracle.csh before compile
• Type source /usr/local/bin/oracle.csh
• The executable of your program will be the file
  name you specified.

11
Always there text editor

• vi
• A text editor written by Bill Joy in 1976
• Always there in Unix/Linux and others
• De facto standard
• Control is as not difficult as it seems
• Used by most of the System admin
• More professional

12
Start vi

• Type vi to start a new file without name
• Type vi filename to start edit a file
• If the filename specify not exist, it will be
  created when you save the file
• Reference
• http//www.gnulamp.com/vi.html

13
Control of vi

• Text mode
• i, a, o enter the text mode by
  insert/append/insert in new line
• Esc to escape the text mode
• Dont more arrows in text mode

14
More control of vi

• Browse mode
• More the arrows or h, j, k, l left/down/up/right
• n dd, n yy, p delete/copy/paste n lines
• b, f page up/page down
• u, Ctrl-r undo/re-do
• x delete a char
• r replace the char the cursor is on

15
More and more control of vi

• Command mode
• w save file
• q quit
• n go to line n
• set nu shown line number very useful for
  debugging
• /pattern search pattern in the file

16
Makefile

• A script that help you compile your code
• Detail of writing a makefile will not be included
• We will provide a makefile for you

17
More on Makefile

• Just modify the first line of the file
• Type make in the command line

Replace with your own .pc file name but without
.pc
18
Other References

• http//infolab.stanford.edu/ullman/fcdb/oracle/or
  -proc.html
• http//lbdwww.epfl.ch/f/teaching/courses/oracle8i/
  server.815/a68022/toc.htm