Solved

Perl Win32 and SQL Statement

Posted on 1998-08-10
13
413 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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
 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Using Perl DBI to query oracle 3 41
To run CGI/perl on tomcat and connect to oracle database. 5 126
Perl Frameworks 1 90
Perl script to delete older files 6 85
There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

813 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

19 Experts available now in Live!

Get 1:1 Help Now