Perl-Win32-Database FAQ

This is a result of seeing too many questions about the same subject
in this mailing list. So I put pen to paper and came up with this.
Most of it is based around Win32::ODBC. If there are any more
questions and answers you want, then send them to me (include the
answer or it won't get added).

1. How do I handle apostrophes
2. How do I handle dates/times
3. How do I call a stored procedure
4. How do I get the number of rows
5. ADODB
5.1   ADODB and Stored Procedures with OUT parameters.


1. How do I handle apostrophes
==============================
Q: I have a script that inserts into a database, it works fine until
I want to insert an apostrophe ' into a string column. I've tried
everything - escaping it with a \, escaping it with \\, etc.

A: You obviously didn't read your DBMS documentation did you. Usually
in SQL apostrophes are escaped by putting them twice. Use a regular
expression such as s/\'/\'\'/g on your string. Note that when you do
a SELECT the apostrophes will be single not escaped.

2. How do I handle dates/times
==============================
Q: I've tried every way of inserting a date into my date column but I
just can't get it to work.

A: ODBC has a special way of handling dates which is generic for all
DBMS's.
You should use the following in your statement:
Dates: {d 'YYYY-MM-DD'}
Times: {t 'HH:MM:SS'}
TimeStamp or DateTime: {ts 'YYYY-MM-DD HH:MM:SS'}

The best way to handle this is to use sprintf:
$ODBCDate = sprintf("{d '%d-%d-%d'}", $year, $mon, $day);

Note: Years must be 4 digits.
Note2: When you do a SELECT the dates get returned in whatever format
is set for the database, not the ODBC format.

3. How do I call a stored procedure
===================================
Q: Calling my stored procedure with "EXECUTE sp_name param1,
param2..." doesn't seem to work. How can I call my SP?

A: ODBC has a calling convention for SP's too.
{call ?=sp_name(param1, param2,...)}
See also Q 5.2

Note: With Win32::ODBC you cannot get the result of the SP. This is
set to change soon. You can get a result set back though if you are
using Transact SQL (MS SQL Server, Sybase SQL Server or SQL
Anywhere).

4. How do I get the number of rows
==================================
Q: The documentation for Win32::ODBC mentions a function called
RowCount which returns the number of rows affected in the last
transaction. It doesn't seem to work. Why?

A: Some DBMS's ODBC drivers don't support this function. Most notably
MS Access, MS SQL Server, Oracle. Use an alternative method such as
counting as you loop through the rows in a select statement or
selecting count(*).

Note: If you use SQL Anywhere this option needs to be turned on in
the options for the particular database. Use SQL Central to do this.

5. How do I use ADODB
=====================
Q: I've heard that ADODB might be faster and certainly more flexible
than Win32::ODBC. How on earth do I use it.

A: Here's a quickie intro:

__BEGIN__
use OLE; # use Win32::OLE if using the Std Distribution
$Conn = CreateObject OLE "ADODB.Connection";

$Conn-> Open("DSN=MyDSN;UID=MyUID;PWD=MyPwd") or die "Cannot connect";

$RS = $Conn-> Execute("SELECT * FROM Pubs");

if(!$RS) {
	$Errors = $Conn-> Errors();
	print "Errors:\n";
	foreach $error (keys %$Errors) {
		print $error-> {Description}, "\n";
	}
	die;
}

while ( !$RS-> EOF ) {
	my($Author, $Title, $ISBN) = (
		$RS-> Fields('Author')->value,
		$RS-> Fields('Title')->value,
		$RS-> Fields('ISBN')->value );

	print $Author, " : ", $Title, " : ", $ISBN, "\n";
	$RS-> MoveNext;
}

$RS-> Close;
$Conn-> Close;
__END__

Note that there is a neat little function $Conn-> SQLEncode which will
escape your apostrophes for you.

5.1 ADODB and Stored Procedures with OUT parameters.
====================================================
Q: How do I use SP parameters with ADO?

A: Code snippet follows. Note that this uses my wonderful Win32::ADO
module (which is just a load of constants). Get it from
http://www.geocities.com/SiliconValley/Way/6278/perl.html

Using Stored Procedures
ADO supports accessing a stored proc with parameters. Here's an example:

__BEGIN__
use OLE; # use Win32::OLE for std distribution
use Win32::ADO;

$con = CreateObject OLE "ADODB.Connection";
$cmd = CreateObject OLE "ADODB.Command";
$rs = CreateObject OLE "ADODB.Recordset";

$con-> Open("DSN=Sax;UID=apl;PWD=oxygen;DBNAME=apllive");

$con or die "Cannot connect";

$con-> Execute("drop table texttable");
$con-> Execute("drop procedure textproc");

$con-> Execute("create table texttable(text varchar(255))");
$con-> Execute("create procedure textproc \@text varchar(255) as insert 
texttable values(\@text)");

$cmd-> {ActiveConnection} = $con;
$cmd-> {CommandText} = "textproc";
$cmd-> {CommandType} = adCmdStoredProc;
$cmd-> Parameters->Append($cmd->CreateParameter("foo", adsubChar, 
adParamInput, 255));

$cmd-> Parameters->Item(0)->{value} = ("A" x 200);
$cmd-> Execute;

$cmd-> Parameters->Item(0)->{value} = "B" x 100;
$cmd-> Execute;

$rs = $con-> Execute("select text from texttable");
while (! $rs-> EOF) {
	$v = $rs-> Fields(0)->value;
	print length($v), $v;
	$rs-> MoveNext;
}
__END__

Using Input/Output Parameters
The following example demonstrates using input and output parameters
& assumes that you have a DSN named MyPubs that gets you to an SQL
Server Pubs database.

....
use Win32::ADO;
use OLE;

$cmd = CreateObject OLE "ADODB.Command";
$con = CreateObject OLE "ADODB.Connection";
$con-> Open "MyPubs", "sa", "";

$con-> Execute "drop procedure MySPOut";
$con-> Execute "create procedure MySPOut( @in char(200), @out char(200) out 
) as select @out = @in";

$cmd-> {ActiveConnection} = $con;
$cmd-> CommandText = "{call MySPOut(?, ?)}";

# specify parameter info
$cmd-> Parameters->Append $cmd->CreateParameter("in", adChar, , 200, "foo");
$cmd-> Parameters->Append $cmd->CreateParameter("out", adChar, 
adParamOutput, 200);

$cmd-> Execute;
....

-----------------------------------------------------------

This FAQ is owned by Matt Sergeant. You may copy it, destroy it, save
it, read it, or ignore it. I reserve the right to be completely
incorrect on any or all of the above points.
Mailto:Sergeant@[...].com for praise/flames/criticism or
just to drop me a line.


Matt
--
Keyboard missing - press F3 to continue
-- Message seen on an Apricot PC


......................................
To leave this list, send an email message to ListManager@[...].com
with the following text in the body: leave Perl-Win32-Database
For support related to the Mailing Lists, send email to ListHelp@[...].com
.