SQL

SQL package for PostgreSQL

The package has be 'beautified' 2002-07-15, and it's easier to understand now

This package is intended to provide a 'safe' interaction between a process and a PostgreSQL database. By introducing some rules, the programmer is forced to behave in a certain way, or he/she will get an exception raised. The way of working with the package is aimed to simulate the way of working with Oracle's 'Dynamic SQL method 4'.

First of all, this package is in an early alpha stage, but it works fairly well. Some features are lacking, such as a way of connecting to several databases at a time. It is also made by a large static array, which is a waste of memory... Another drawback is that sql.statement_type variables must be declared globally in packages. The current limit is 700 statement and max 60 bind variables per statement. But that should cover most needs, or alter it in the sql body. I like it though...

The main difference between this package and all other I've seen is that this package uses cursors on the serverside for reading from the database. So selecting all from a table containing 100 000 rows will only send the data for 1 row at a time, which ocuurs when calling 'Fetch'. The next 'Fetch' will bring you the next row.

Quick tutorial

For just reading...

Declare statement variables GLOBALLY in packages
My_statement : sql.statement_type;
Declare transaction varables (can be locally declared)
My_transaction : sql.transaction_type;
Connect to the database
sql.Connect(DB_Name => "my_db");
Prepare statement
sql.prepare(My_statement,"select COL1,COL2 from MY_TABLE where a = :A and b = :B");
Set values
sql.set(My_statement,"A",some_value);
sql.set(My_statement,"B",another_value);
Start a transaction
sql.start_read_write_transaction(My_transaction);
Open a cursor at the backend - This executes the query
sql.open_cursor(My_statement);
Fetch values from cursor
sql.fetch(My_cursor,End_of_set);
Get the wanted columns
If not End_of_set then
sql.get(My_cursor,"COL1",some_variable);
sql.get(My_cursor,"COL2",some_other_variable);
end if;
Close the cursor
sql.close_cursor(My_statement);
End the transaction
sql.commit(My_transaction);
Close the connection
sql.close_session;

To update..

Declare statement variables GLOBALLY in packages
My_statement2 : sql.statement_type;
Declare transaction varables (can be locally declared)
My_transaction2 : sql.transaction_type;
Connect to the database
sql.Connect(DB_Name => "my_db");
Prepare statement
sql.prepare(My_statement2,"update MY_TABLE set COL1 = :C where a = :A and b = :B");
Set values
sql.set(My_statement2,"A",some_value);
sql.set(My_statement2,"B",another_value);
sql.set(My_statement2,"C",a_third_value);
Start a transaction
sql.start_read_write_transaction(My_transaction2);
Execute the statement
sql.execute(My_statement2); or
sql.execute(My_statement2,Rows_affected);
End the transaction
sql.commit(My_transaction); or Rollback
sql.Rollback(My_transaction);
Close the connection
sql.close_session;

The difference between the two Execute are that if the first form is used and no row is affected, NO_SUCH_ROW will be raised. This can be handy to detect transaction conflicts, ie a record was read, but before update someone else updated it. This of course demands some form of strategy of keeping track of latest update time and updater AND to test on this in the execute. The other form will return number of affected rows. PostgreSQL provides another, better and safer, way of determing transaction conflicts, and it is to use TRANSACTION MODE SERIALIZABLE instead of standard READ_COMMITED. I plan to raise TRANSACTION_CONFLICT based on that, but haven't got the time yet. Nor have I had time to raise DUPLICATE_INDEX. You'll get another excpetion for that. (run sql_test3 a couple of times...)

Transactions can have two modes, read_write or read_only.
sql.start_read_write_transaction(My_transaction2)
sql.start_read_only_transaction(My_transaction2)
In a read_only transaction, execute is not allowed. This is to make sure that no updates occur. In read_write, update and inserts and deletes are allowed, of course. It is possible to start several transactions, but they all have to be of the same type or you are rewared with a TRANSACTION_ERROR exception. If you are not the owner of a transaction and you try to rollback you also get a TRANSACTION_ERROR.
The transaction varable that starts the first transaction is regarded as the owner of the transaction. Any started transactions after that, of the same type, is allowed but nothing happens when they commit. The database will only see the BEGIN, COMMIT and ROLLBACK of the first transaction. You'll get an exception if you rollback and is not the owner. Your application needs re-design if this happens :-)

A small sample program

with sql;
with text_io;
with gnat.traceback.symbolic;
procedure sql_leak_test is

  stm,stm2 : sql.statement_type;
  trans : sql.transaction_type;
  maxloop : constant integer := 2_000; --4_000;
  end_of_set : boolean := false;
  f1 : integer := 0;
  upd : constant string := "BNL";
begin
  sql.Connect(db_name => "bnl");
  for i in 1..maxloop loop
    if (i mod 100 = 0) then
      text_io.put_line ("i: " & i'img & " Total: " & maxloop'img);
    end if;
    sql.start_read_write_transaction(trans);
    sql.prepare(stm,"select * from MY_TABLE where LATEST_UPDATER = :UPDATER");
    sql.prepare(stm2,"update ISCONF set LATEST_UPDATER = 'BNL' where LATEST_UPDATER = :UPD"); 
    sql.set(stm2,"UPD",upd);    
    sql.set(stm,"UPDATER",upd);
    sql.open_cursor(stm);
    sql.fetch(stm,end_of_set);
    sql.close_cursor(stm);
    if not end_of_set then
      sql.get(stm,1,f1);  -- get first column by column order instaed by name
    end if;
    sql.execute(stm2); 
--    text_io.put_line ("f1: " & f1'img );
    sql.commit(trans); 
  end loop;
  sql.Close_Session;
  
exception
  when Event: others =>
  text_io.put_line(gnat.traceback.symbolic.symbolic_traceback(event));  
  
end sql_leak_test;

If you use it, and find bugs or ways of improve it, please mail me at bjorn dot lundin at swipnet dot se