The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.

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