• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

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
0
brewman
Asked:
brewman
1 Solution
 
kaijenCommented:
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.
0
 
brewmanAuthor Commented:
Kai,
The data type is date/time.  Thanks

Mike
0
 
brewmanAuthor Commented:
Kai,
BTW, The format is mm/dd/yy hh:nn along with having date/time as the datatype.  Thanks
Mike
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mitekCommented:
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'));  


0
 
brewmanAuthor Commented:
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
0
 
brewmanAuthor Commented:
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
0
 
notanexpertCommented:
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>
0
 
brewmanAuthor Commented:
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?
0
 
brewmanAuthor Commented:
You are answering my second problem which I have since figured I made a mistake.  
0
 
mitekCommented:
Try this.


$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 CDate('$start') And CDate('$stop')));  

or, maybe

$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#)));  

0
 
mitekCommented:
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)
);


0
 
brewmanAuthor Commented:
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#)));    
0
 
mitekCommented:
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 :)
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now