DBIx::MSSQLReporter - An module to connect Perl to MS SQL Server and MS Data Engine
See the programs in the examples/ directory. They have been updated to run against MS SQL Server V 8.
Warning: I could not get any of the MSDE examples to work with MS SQL Server V 8.
Note: sql8-Demo-3.pl is a CGI script.
DBIx::MSSQLReporter encapsulates the connection between Perl and MS SQL Server.
DBIx::MSSQLReporter
DBIx::MSSQLReporter was written so that I could teach myself about MS SQL Server and MSDE, and as part of my Perl tutorial series.
It should be clear from the name that this module is database-engine-specific. If you plan on writing code which is independent of any particular database, look elsewhere.
See the URI, below, for my demos sql7Demo[23].pl, which both use this module.
sql7Demo2.pl is a command-line program. sql7Demo3.pl is a CGI script.
Lastly, note that this module has a chequered future: I may well re-write it to fit under the umbrella of DBIx::Easy, or someone else working independently may have already released such a module.
You install DBIx::MSSQLReporter, as you would install any perl module, by running these commands:
perl Makefile.PL make make test make install
The constructor takes 1 parameter and 1 value for that parameter.
It croaks if it can't connect. Otherwise it returns an object you can use thus:
my($reporter) = DBIx::MSSQLReporter -> new(connexion => $connect); print join("\n", @{$reporter -> get_viewNames()}), "\n\n";
It croaks if it can't prepare() and execute() the given SQL.
It returns a statment handle, which you need for things like:
my($sth) = $reporter -> do($sql); $sth -> dump_results(); $sth -> finish();
dump_results() is built-in to DBI.
It croaks if it can't drop the given database.
$reporter -> dropDB($dbName);
It croaks if it can't drop the given table.
$reporter -> dropTable($tableName);
It returns a sorted list of user database names, all in lower case.
$sysDbCount is optional. It defaults to 4, which means this method ignores the 4 system tables. See get_sysDbNames(), below.
my($dbName) = $reporter -> get_dbNames(); print "User databases: \n"; print join("\n", @$dbName), "\n\n";
It returns a list of references to the names, types, and precisions, of the fields in the given table.
my($fieldName, $fieldType, $fieldPrecision) = $reporter -> get_fieldNames($tableName); print join("\n", map{"Field: $$fieldName[$_]. Type: $$fieldType[$_]. Precision: $$fieldPrecision[$_]"} 0 .. $#{$fieldName}), "\n\n";
It returns a sorted list of user table names, all in lower case. Recall, the DSN specified the database.
my($tableName) = $reporter -> get_tableNames(); print "User tables: \n"; print join("\n", @$tableName), "\n\n";
It returns a sorted list of user view names, all in lower case. Recall, the DSN specified the database.
my($viewName) = $reporter -> get_viewNames(); print "User views: \n"; print join("\n", @$viewName), "\n\n";
It returns a sorted list of system database names, all in lower case. On my system, I get master, model, msDb and tempDb.
$sysDbCount is optional. It defaults to 4, which means this method returns the 4 system tables. See get_dbNames(), above.
my($sysDbName) = $reporter -> get_sysDbNames(); print "System databases: \n"; print join("\n", @$sysDbName), "\n\n";
It returns a sorted list of system table names, all in lower case. Recall, the DSN specified the database.
my($sysTableName) = $reporter -> get_sysTableNames(); print "System tables: \n"; print join("\n", @$sysTableName), "\n\n";
It returns a sorted list of system view names, all in lower case. Recall, the DSN specified the database.
my($sysViewName) = $reporter -> get_sysViewNames(); print "System views: \n"; print join("\n", @$sysViewName), "\n\n";
Convert a hash reference, as returned by $reporter -> select($sql), into an HTML table. See select(), below, for details.
my($html) = $reporter -> hash2Table($select);
$sep is optional. It separates the values of different rows in each column. It defaults to $;.
$keyRef is optional. It is a hash reference used to specify the order of columns. It defaults to sorting the keys of %$select.
If you wish to use $keyRef, prepare it thus:
my(%key) = ( hostName => { someData => '', order => 2, }, userName => { someData => '', order => 1, }, ); my($html) = $reporter -> hash2Table($select, $;, \%key); The key 'order' is used to order the keys 'hostName' and 'userName', which are presumed to appear as keys in %$select. The key 'someData' is ignored.
$sep is optional. It defaults to $;.
It returns a reference to a hash, which hold the results of the select.
The keys of the hash are the names of the fields, which can be used for column headings.
The values of the hash are the values of the fields, which can be used for the column data. The values in each column are, by default, separated by Perl's $; variable.
my($select) = $reporter -> select($sql);
Warning: select() selects the whole table. Ideally we'd use DBIx::Recordset to page thru the table, but I had too many problems with various versions of DBIx::Recordset.
If you have binary data containing $;, you <must> set $sep to something else. Of course, with binary data, there may be no 'safe' character (string) which does not appear in your data.
Alternately, store your binary data in files, and put the file name or URI in the database.
The hash reference can be passed straight to hash2Table for conveting into an HTML table. Eg:
my($html) = $reporter -> hash2Table($select); print $html;
DBIx::MSSQLReporter was written by Ron Savage <ron@savage.net.au> in 2000.
Source available from http://savage.net.au/Perl.html.
Australian copyright (c) 1999-2002 Ron Savage.
All Programs of mine are 'OSI Certified Open Source Software'; you can redistribute them and/or modify them under the terms of The Artistic License, a copy of which is available at: http://www.opensource.org/licenses/index.html
To install DBIx::MSSQLReporter, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::MSSQLReporter
CPAN shell
perl -MCPAN -e shell install DBIx::MSSQLReporter
For more information on module installation, please visit the detailed CPAN module installation guide.