asked on

Perl Win32 and SQL Statement

I want to connect to a Access97 Database and be able to fetch a entire row of data that falls within a certain time frame using a SQL statement. I can create the statement which works inside Access97 but I don't know how to use the similar SQl statment using the PERL Win32.

use Win32::ODBC;

if (!($db=new Win32::ODBC("DSN=xxx;UID=xxx"))) {
print "Error connecting to $DSN\n";
print "Error: " . Win32::ODBC::Error() . "\n";} else {
print "Database being searched...\n";
}

$SqlStatement = "SELECT tbl_SNAS_TPP_Data.*, tbl_SNAS_TPP_Data.DateTimeOut
FROM tbl_SNAS_TPP_Data
WHERE (((tbl_SNAS_TPP_Data.DateTimeOut) Between [Start] And [Stop]));

if ($db->Sql($SqlStatement)) {
print "SQL failed.\n";
print "Error: " . $db->Error() . "\n";
} else {

while($db->FetchRow()) {
%Data = $db->DataHash();

..some process

}
$db->Close();
exit;

I know that I would have to have some values in the [Start] and [Stop] but I don't know what the syntax would work in the PERL Win32 module.

Any ideas?

Mike

kaijen

Hi,

could you please specify which datatype you use for tbl_SNAS_TPP_Data.DateTimeOut ?

The syntax of the statement doesn't differ from what you use in normal SQL. Probably you have to format $start and $stop accordingly to your datatype.

By the way:
$SqlStatement = <<END;
SELECT tbl_SNAS_TPP_Data.*, tbl_SNAS_TPP_Data.DateTimeOut
FROM tbl_SNAS_TPP_Data
WHERE (((tbl_SNAS_TPP_Data.DateTimeOut) Between [Start] And [Stop]));
END

is much easier to work with.

Best regards,
Kai.

brewman

ASKER

Kai,
The data type is date/time. Thanks

Mike

brewman

ASKER

Kai,
BTW, The format is mm/dd/yy hh:nn along with having date/time as the datatype. Thanks
Mike

mitek

First thing I would try would be:

$start = "12/12/97";
$stop = "15/12/97";

$SqlStatement = "SELECT tbl_SNAS_TPP_Data.*, tbl_SNAS_TPP_Data.DateTimeOut
FROM tbl_SNAS_TPP_Data
WHERE (((tbl_SNAS_TPP_Data.DateTimeOut) Between '$start' And '$stop'));

brewman

ASKER

Mitek,

This is the error I get after trying.

Can't call method "Sql" without a package or object reference at script line 19.
Error connecting to
Error: [911] [] "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

Michael.C.Lammon@mci.com

brewman

ASKER

notanexpert

I don't know much about ODBC except that there is a control panel for it where you might have to set up some things as 'sources' if I remember correctly. Your problem is obviously that Perl can't find your database .. apart from the obvious things like making sure you have your host and port name correct, try taking a peek at the ODBC control panlel and setting up your database as a System, File, or User DSN (Each simply offers increasing levels of isolation from people at various levels of the network and machine from accessing your database.) Choose File DSN, click add, and follow the wiz. If you've already done all this .. <shrug>

brewman

ASKER

Actually the above was incorrect. I left out the correct DSN name.
This is the error I get now:
SQL failed.
Error: [-3030] [1] [0] "[Microsoft][ODBC Microsoft Access 97 Driver] Data type mismatch in criteria expression."

Any ideas?

brewman

ASKER

You are answering my second problem which I have since figured I made a mistake.

ASKER CERTIFIED SOLUTION

mitek

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mitek

if this doesn't work, i would try

$SqlStatement = qq(
Dim date_start, date_stop
date_start = "$start"
date_stop = "$stop"
SELECT tbl_SNAS_TPP_Data.*, tbl_SNAS_TPP_Data.DateTimeOut
FROM tbl_SNAS_TPP_Data
WHERE tbl_SNAS_TPP_Data.DateTimeOut Between CDate(date_start) And CDate(date_stop)
);

brewman

ASKER

Mitek,

This one worked awesome!! You are the best and thanks for
sticking with me. I'm not sure what the # did but it definently
works for me. I guess I need to learn more about SQL.

Again .. Thanks... Now I need to continue on and see what I can do with the data...

Michael.C.Lammon@mci.com

$SqlStatement = "SELECT tbl_SNAS_TPP_Data.*, tbl_SNAS_TPP_Data.DateTimeOut
FROM tbl_SNAS_TPP_Data
WHERE (((tbl_SNAS_TPP_Data.DateTimeOut) Between CDate(#$start#) And CDate(#$stop#)));

mitek

Actually, #'s are not specific to SQL in general, i think it's Access-specific string delimiter, instead of single quotes.
In SQL Server, it would be single quotes instead of #'s.

Also, it's better to use generalized double quote qq( ) for SQL statements, as i did in the very last comment. Then, you'll be able to write them in more readable format.

Good luck :)