GO::SqlWrapper
helper functions for creating sql statements
use GO::SqlWrapper qw(:all); use GO::SqlWrapper qw(make_sql_select make_sql_insert make_sql_update sql_delete db_null sql_quote);
value to represent a database null column value
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"
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
Usage - Returns - Args -
Usage - use this to check the sanity of an SQL query Returns - void Args - sql string
escapes any quotes in a string, so that it can be passed to a sql engine safely
usage: sql_quote($col_value)
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'))
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
Usage - Returns - Args - dbh, name, tables, where, cols
as select hashlist, returns a list of scalars
as select hashlist, returns a list of arrays
parameters: statement handle
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
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 name/value pairs into a database table
parameters: dbh, table, values (hashref of name/value pairs)
synonym for insert_h
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 name/value pairs into a database table
parameters: dbh, table, values (hashref of name/value pairs), where (sql clause)
usage: orterm($t1, $t2, $t3, ..);
usage: andterm($t1, $t2, $t3, ..);
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 duplicate items from an array
usage: remove_duplicates(\@arr)
affects the array passed in, and returns the modified array
To install GO, copy and paste the appropriate command in to your terminal.
cpanm
cpanm GO
CPAN shell
perl -MCPAN -e shell install GO
For more information on module installation, please visit the detailed CPAN module installation guide.