Solved

Perl Win32 and SQL Statement

Posted on 1998-08-10
13
410 Views
Last Modified: 2006-11-17
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
Comment
Question by:brewman
13 Comments
 

Expert Comment

by:kaijen
ID: 1209189
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
 

Author Comment

by:brewman
ID: 1209190
Kai,
The data type is date/time.  Thanks

Mike
0
 

Author Comment

by:brewman
ID: 1209191
Kai,
BTW, The format is mm/dd/yy hh:nn along with having date/time as the datatype.  Thanks
Mike
0
 
LVL 4

Expert Comment

by:mitek
ID: 1209192
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
 

Author Comment

by:brewman
ID: 1209193
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
 

Author Comment

by:brewman
ID: 1209194
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 1

Expert Comment

by:notanexpert
ID: 1209195
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
 

Author Comment

by:brewman
ID: 1209196
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
 

Author Comment

by:brewman
ID: 1209197
You are answering my second problem which I have since figured I made a mistake.  
0
 
LVL 4

Accepted Solution

by:
mitek earned 320 total points
ID: 1209198
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
 
LVL 4

Expert Comment

by:mitek
ID: 1209199
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
 

Author Comment

by:brewman
ID: 1209200
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
 
LVL 4

Expert Comment

by:mitek
ID: 1209201
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Many time we need to work with multiple files all together. If its windows system then we can use some GUI based editor to accomplish our task. But what if you are on putty or have only CLI(Command Line Interface) as an option to  edit your files. I…
Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now