Solved

Access SQL Query from Perl using times with colons

Posted on 2010-11-26
10
325 Views
Last Modified: 2012-05-10
I have an sql query that I am trying to run in Perl. I have data stored every 6 hours and am trying to pull the data from a run 6 hours before the most up to date run

 
$sqlstatement = "Select Location FROM Forecasts WHERE RunExecuted=(SELECT max(RunExecuted) - #6:00# from Forecasts) ";

Open in new window


The #6:00# is to subtract 6 hours from the most recent executed time. I wrote this query in VBA and it works perfectly. However, in perl, the : and # causes problems because Perl is trying to read this in as a parameter. How do I get perl to ignore those symbols and run the SQL query as typed.

If i remove the - #6:00# the query works fine.
0
Comment
Question by:kyle972
  • 5
  • 3
  • 2
10 Comments
 
LVL 16

Expert Comment

by:jmatix
Comment Utility
Try putting the SQL in single quotes as:

$sqlstatement = 'Select Location FROM Forecasts WHERE RunExecuted=(SELECT max(RunExecuted) - #6:00# from Forecasts) ';
0
 

Author Comment

by:kyle972
Comment Utility
I tried single quotes and am still getting an error.

Anything else?
0
 
LVL 16

Expert Comment

by:jmatix
Comment Utility
Are you using Access ODBC driver? It tried a similar query and it worked fine. Here is the sample. replace with your database path and database name:


#! c:\perl\bin\perl



use Win32::ODBC;



$DriverType = "Microsoft Access Driver (*.mdb)";

$DSN = "Win32 ODBC";

$Dir = "C:\\DB\\acc";

$DBase = "Database11.mdb";





Win32::ODBC::ConfigDSN(ODBC_ADD_DSN, $DriverType,("DSN=$DSN", "Description=Win32 ODBC Test DSN for Perl", "DBQ=$Dir\\$DBase", "DEFAULTDIR=$Dir", "UID=", "PWD=")) or die "ConfigDSN(): Could not add temporary DSN" . Win32::ODBC::Error();



$db=new Win32::ODBC($DSN) or die "couldn't ODBC $DSN because ", Win32::ODBC::Error(), "\n";



$sqlstatement = "Select Location FROM Forecasts WHERE RunExecuted=(SELECT max(RunExecuted) - #6:00# from Forecasts) ";

!$db->Sql($sqlstatement) or die "couldn't do $query because ", $db->Error(), "\n";



while($db->FetchRow())

{

 my %Data = $db->DataHash();

 foreach my $key(keys(%Data)){

   print $key," -> ",$Data{$key};

 }  

}





Win32::ODBC::ConfigDSN(ODBC_REMOVE_DSN, $DriverType, "DSN=$DSN") or die "ConfigDSN(): Could not remove temporary DSN because ", Win32::ODBC::Error();

Open in new window

0
 

Author Comment

by:kyle972
Comment Utility
I am using DBI.

I am now curious how to just use a colon in a normal text string that you are passing to another function.

Example:

$Var="Location : Test"

Function($Var)

The colon will cause the function to not work properly. How would I pass the text string to the function and have it remain as one long text string.

This is the solution to my sql problem as well, but is a much simpler question.



0
 
LVL 16

Expert Comment

by:jmatix
Comment Utility
As far as I know colon (:) does not have any special meaning in Perl and need not be escaped in a string. For example, try this and it will print the value fine:

$var="Location : Test";
test ($var);
exit;

sub test
{
  my $param = shift;
  print $param;
}

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:kyle972
Comment Utility

   Here is an example. I am reading the contents out of a txt file and then sending an email with the contents.

If there is a : in the text file it will cause issues.


my $mailer = new Net::SMTP::TLS(  
       'smtp.gmail.com',  
       Hello   =>      'smtp.gmail.com',  
       Port    =>      587,  
       User    =>      'xxx',  
       Password=>      'xxx';  
   $mailer->mail('xxx');  
   $mailer->to('xxx');  
   $mailer->data;  
   $mailer->datasend("Subject: ");
   
   open (MYFILE, 'Export.txt'); 
while (<MYFILE>) { 
 	chomp;
 	$string=$string."$_\n" ;
	 
	 }


   $mailer->datasend($string);  
   $mailer->dataend;  
   $mailer->quit;

Open in new window

0
 
LVL 28

Expert Comment

by:FishMonger
Comment Utility
If you're having a problem with sending the email, then try a different mail module.  I prefer to use MIME::Lite for most of my email needs.

#!/usr/bin/perl



use strict;

use warnings;

use MIME::Lite;



open my $fh, '<', 'Export.txt' or die "failed to open 'Export.txt' $!";



$/ = undef;

my $file_contents = <$fh>;

close $fh;



MIME::Lite->send('smtp', 'smtp.gmail.com',

          Timeout  => 60,

          Port     => 587,

          AuthUser => 'xxx',

          AuthPass => 'xxx'

);



my $msg = MIME::Lite->new(

        From     => 'me@myhost.com',

        To       => 'you@yourhost.com',

        Subject  => 'Your Subject',

        Data     => $file_contents,

    );



$msg->send;

Open in new window

0
 
LVL 28

Expert Comment

by:FishMonger
Comment Utility
Your initial question was related to your sql statement, then you switched to having an email issue.  I don't know which problem you're experiencing, but for the sql issue, have you tried using one of DBI's quote methods?

http://search.cpan.org/~timb/DBI-1.615/DBI.pm#quote
0
 

Accepted Solution

by:
kyle972 earned 0 total points
Comment Utility
I accomplished the same thing by subtracting .25. Using .25 instead of #6:00# corrected the problem.
0
 

Author Closing Comment

by:kyle972
Comment Utility
It works
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

744 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

15 Experts available now in Live!

Get 1:1 Help Now