Re: DBperl. anyone using it?

Andy Kent (akent@cix.compulink.co.uk) wrote: : > I don't know about DBperl, but isqlperl is marvelous. Use it all the : time for : > both quick hacks and largish systems. : So what does it (isqlperl) do then? ISQLPERL is an extention to PERL. Both are valuable tools. Below is the top of each of the man pages for the isqlperl and perl. Enjoy... PERL(1) USER COMMANDS PERL(1) NAME perl - Practical Extraction and Report Language SYNOPSIS perl [options] filename args DESCRIPTION Perl is an interpreted language optimized for scanning arbi- trary text files, extracting information from those text files, and printing reports based on that information. It's also a good language for many system management tasks. The language is intended to be practical (easy to use, effi- cient, complete) rather than beautiful (tiny, elegant, minimal). It combines (in the author's opinion, anyway) some of the best features of C, sed, awk, and sh, so people familiar with those languages should have little difficulty with it. (Language historians will also note some vestiges of csh, Pascal, and even BASIC-PLUS.) Expression syntax corresponds quite closely to C expression syntax. Unlike most Unix utilities, perl does not arbitrarily limit the size of your data--if you've got the memory, perl can slurp in your whole file as a single string. Recursion is of unlimited depth. And the hash tables used by associative arrays grow as necessary to prevent degraded performance. Perl uses sophisticated pattern matching techniques to scan large amounts of data very quickly. Although optimized for scanning text, perl can also deal with binary data, and can make dbm files look like associative arrays (where dbm is available). Setuid perl scripts are safer than C programs through a dataflow tracing mechanism which prevents many stupid security holes. If you have a problem that would ordinarily use sed or awk or sh, but it exceeds their capa- bilities or must run a little faster, and you don't want to write the silly thing in C, then perl may be for you. There are also translators to turn your sed and awk scripts into perl scripts. OK, enough hype. ISQLPERL(1) USER COMMANDS ISQLPERL(1) NAME isqlperl - Perl interface to Informix Databases SYNOPSIS $status = &isql_execute($sttmnt_or_id, @params); $id = &isql_open($statement, @params); @titles = &isql_titles($id); $sttmnt = &isql_statement($id); $type = &isql_type($id); @types = &isql_columns($id); @row = &isql_fetch($sttmnt_or_id, @params); $status = &isql_close($id); $status = &isql_shutdown(); @databases = &isql_databases(); $errmsg = &isql_msg([$code]); @sqlca = &isql_ca(); $isql_code; $isql_autoclose; $isql_attrib; $isql_database; $isql_transactions; $isql_ansi; $isql_online; DESCRIPTION Isqlperl is a collection of usub extensions to perl, allow- ing direct access to Informix databases from within a perl script. Here's how it works. $status = &isql_execute($sttmnt_or_id, @params); performs the sql command $sttmnt_or_id, which can be either an SQL statement, or a numeric statement id returned from a call to &isql_open(). It returns the logical not of the resulting sqlca.sqlcode, so you can say: &isql_execute($cmd) || die "$isql_code"; It can be used to run any SQL command which is not a naked select statement. If the statement, or the string from which the id was prepared, contains variable place holders (question marks) the values should be supplied by the optional parameters. For example: &isql_execute(<<EOI, $blob, undef) || die "$isql_code"; insert into $table($blobcolname, $nullcolname) values(?, ?) EOI Using parameters is the only way to pass the ONLINE Blob data types to a statement. It can be also useful for supplying NULLs: an undefined $param is equivalent to a NULL. Executing a statement directly does not temporarily consume a statement id. $id = &isql_open($statement, @params); prepares the $statement for execution, and if it is a select statement, opens a cursor to it. Then it returns a numeric statement id $id in the range 1 ... MAXCURSORS which can be used to refer to the prepared statement or open cursor. &isql_open() returns 0 if the prepare or open failed. You can have up to MAXCUR- SORS statement ids open at any one time, and that number is configured when isqlperl is built. The optional parameters are used to fill in place hold- ers in the where clause while opening a select state- ment. They are ignored if the statement being opened is not a select statement, but the statement is still allowed to have place holders whose values are to be supplied by &isql_execute(). If the statement is to be run a number of times it is considerably more efficient to prepare a statement using &isql_open() and to execute the resulting $id. because it avoids the overhead of preparing the state- ment before, and closing it after, each execution. For example: $insert = &isql_open(<<EOI) || die "$isql_code"; insert into table1(key, val) values(?, ?) EOI while (($key, $val) = each %myassoc) { &isql_execute($insert, $key, $val) || die "$isql_code"; } &isql_close($id); ...... this goes on for 11 pages but you get the idea. The nut of the message is "isqlperl" is a valuable tool to have in your bag of tricks. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Jason Restad | jrestad@ftc.scs.ag.gov USDA, Soil Conservation Service | jasr@ftc.scs.ag.gov 2625 Redwing Rd., Suite 110 | Ft. Collins, Colorado 80526 |