/ 
go-db-perl-0.04
River stage zero No dependents
/ GO::SqlWrapper

NAME

GO::SqlWrapper

SYNOPSIS

helper functions for creating sql statements

USAGE

  use GO::SqlWrapper qw(:all);
  use GO::SqlWrapper qw(make_sql_select 
			     make_sql_insert 
			     make_sql_update 
			     sql_delete
                	     db_null 
			     sql_quote);

db_null

value to represent a database null column value

get_autoincrement_val

args: dbh

returns the id created for the latest insert (serial cols under informix, auto_increment under mysql)

the default is informix; for this to work under mysql, the env variable $DBMS must be set to "mysql"

make_sql_select

 usage: make_sql_select({select_arr=>\@columns,
			 table_arr=>\@tables,
			 where_arr=>\@and_clauses},
		         order_arr=>\@order_columns);

returns: sql string

will remove duplicate items in the above arrays

sqlin

Usage   -
Returns -
Args    -

sql_sanity_check

Usage   - use this to check the sanity of an SQL query
Returns - void
Args    - sql string

sql_quote

escapes any quotes in a string, so that it can be passed to a sql engine safely

usage: sql_quote($col_value)

make_sql_insert

usage: my $sql_stmt = make_sql_insert($table, \%entry_h)

 given a list of name/values pairs for the entry hash, turns
 it into an SQL statement.

all values will be sql-quoted (surrounded by single quotes, actual quotes are escaped by preceeding the quote with another quote). in cases where you do not want the value quoted (e.g. if the values you are inserting must be dynamically fetched with an sql statement), then you should pass the values as a hash, rather than a string. the hash keys should by 'type' and 'val'. if 'type' is char or varchar, the string is quoted, other wise it is unquoted.

for example:

my $sql = make_sql_insert("seq2ext_db", {seq_id=>900, name=>"AC000052", ext_db_id=>{type=>"sql", val=>"(select id from ext_db ". "where name = 'genbank')"}});

will produce:

insert into seq2ext_db (seq_id, name, ext_db_id) values ('900', 'AC000052', (select id from ext_db where name = 'genbank'))

make_sql_update

select_hashlist

selects rows from the database and returns the results as an array of hashrefs

parameters: dbh, tables, where, columns

the sql parameters can be either strings or arrays of strings

eg

select_hashlist($dbh, "clone");     # gets all results from clone table

or

  select_hashlist($dbh, 
		  ["seq", "seq_origin"], 
		  ["seq.id" = "seq_origin.seq_id"],
		  ["seq.id"]);    # gets a list of all seq_ids with origin

select_hash

select_structlist

Usage   -
Returns -
Args    - dbh, name, tables, where, cols

select_vallist

Usage   -
Returns -
Args    -

as select hashlist, returns a list of scalars

select_val

Usage   -
Returns -
Args    -

select_rowlist

Usage   -
Returns -
Args    -

as select hashlist, returns a list of arrays

get_hashrow

parameters: statement handle

get_iterator

parameters: as for select_hashlist

gets a statement handle for a query. the results can be queried a row at a time with get_hashrow

sql_delete

parameters: dbh, table, where

the "where" parameters can be either a string representing the where clause, or an arrayref of clauses to be ANDed.

insert_h

insert name/value pairs into a database table

parameters: dbh, table, values (hashref of name/value pairs)

insert_hash_wp

synonym for insert_h

insert_hash

parameters: dbh, table, values (hashref of name/value pairs)

returns: new primary key val (if the primary key is of type informix-serial)

all values will be automatically sql-quoted (this may not be the semantics you want - consider using insert_h() instead)

does not use DBI placeholders; the consequence of this is that it cannot be used to insert BYTE or TEXT fields. Use insert_h() instead. I would deprecate this method for the sake of aesthetics, except a lot of code uses it.

note:

update_h

update name/value pairs into a database table

parameters: dbh, table, values (hashref of name/value pairs), where (sql clause)

get_result_column

orterm

usage: orterm($t1, $t2, $t3, ..);

andterm

usage: andterm($t1, $t2, $t3, ..);

rearrange()

Usage    : n/a
Function : Rearranges named parameters to requested order.
Returns  : @params - an array of parameters in the requested order.
Argument : $order : a reference to an array which describes the desired
                    order of the named parameters.
           @param : an array of parameters, either as a list (in
                    which case the function simply returns the list),
                    or as an associative array (in which case the
                    function sorts the values according to @{$order}
                    and returns that new array.

Exceptions : carps if a non-recognised parameter is sent

remove_duplicates

remove duplicate items from an array

usage: remove_duplicates(\@arr)

affects the array passed in, and returns the modified array