Trouble with substitution SQL statement in Perl

[dejohnny]

Here what I'm trying to do.

I'm trying to build a list of SELECT statements from a filehandle (FH) that reads in a data file (OI.dat). When the script runs, I only want to extract a value that's located at position 250, 10.

I can extract those values but, they're not being substituted in the OUT file (query list). There should be a value placed in $field variable from pos 250, 10, but it doesn't work.

OI.dat (file contents)
--------------------------------------------------------------

photo_file_name	Image_View	IndexNum	Primary_key	Pos 250, 10	Created Date
oid25441_book348_idx534.JPG	UNKNOWN	N/A	25441	534	201202250551
oid25815_book363_idx556.JP	UNKNOWN	N/A	25815	556	201203071017
oid18900_book394_idx594.JPG	UNKNOWN	N/A	18900	594	201204051159

single_sql.sql
Here's what the query list looks like:
-----------------------------------------------------
SELECT photo.photo FROM inmate_photo AS photo WHERE photo.photo_index = CAST( $field AS INTEGER );
SELECT photo.photo FROM inmate_photo AS photo WHERE photo.photo_index = CAST( $field AS INTEGER );
SELECT photo.photo FROM inmate_photo AS photo WHERE photo.photo_index = CAST( $field AS INTEGER );


Here is the script I created to generate the SQL from:

use strict;
use warnings;

open FH, "OI.dat" or die $!;
open OUT, ">>single_sql.sql" or die $!;

while(my $line = <FH>){
chomp $line;
# Fixed-width data
my $field = substr( $line , 250, 10);
my $stmt = 'SELECT photo.photo ' .
  'FROM inmate_photo AS photo ' .  
  'WHERE photo.photo_index = ' .
  'CAST( $field AS INTEGER );';

#create a file with sql statements in it
print OUT "$stmt \n";
}
close FH;
close OUT;




What am I missing in the Perl script??

[Tommy Ngo]

Tommy Ngo • Hey Darel,

Try qq() function. This will allow you subsitute value stored in variable
$field. Make sure that you have see the value stored in $field.


my $field = substr( $line , 250, 10);
my $stmt = qq { SELECT photo.photo FROM inmate_photo AS photo
WHERE photo.photo_index = CAST( $field AS INTEGER ); };


print OUT "$stmt \n";


www.linkedin.com/groupItem?view=&gid=106254&type=member&item=115407807&qid=8c082c42-8e2c-4f7e-8a60-6b10d573d908&trk=group_most_recent_rich-0-b-cmr&goback=.gmr_106254

[dejohnny]

Here's one solution:


use strict;
use warnings;

open FH, "OI.dat" or die $!;
open OUT, ">>single_sql.sql" or die $!;

while(my $line = <FH>){
chomp $line;
# Fixed-width data
my $field = substr( $line , 250, 10);
$field =~ s/^\s+|\s+$//g;
my $stmt = qq{\nSELECT photo.photo \nFROM inmate_photo AS photo
WHERE photo.photo_index = CAST( $field AS INTEGER );};

#create a file with sql statements in it
print OUT "$stmt \n";
}
close FH;
close OUT;


Generated SQL Statements


SELECT photo.photo
FROM inmate_photo AS photo
WHERE photo.photo_index = CAST( 534 AS INTEGER );

SELECT photo.photo
FROM inmate_photo AS photo
WHERE photo.photo_index = CAST( 556 AS INTEGER );

SELECT photo.photo
FROM inmate_photo AS photo
WHERE photo.photo_index = CAST( 594 AS INTEGER );

SELECT photo.photo
FROM inmate_photo AS photo
WHERE photo.photo_index = CAST( 607 AS INTEGER );

SELECT photo.photo
FROM inmate_photo AS photo
WHERE photo.photo_index = CAST( 609 AS INTEGER );

SELECT photo.photo
FROM inmate_photo AS photo
WHERE photo.photo_index = CAST( 616 AS INTEGER );



Thanks Tommy Ngo!

[Spike]

Is the CAST needed? If the perl var $field is unquoted it is a number not a string to most DBs.

[dejohnny]

May 16, 2012 3:50:45 GMT -5 @Spike said:

Is the CAST needed? If the perl var $field is unquoted it is a number not a string to most DBs.

Cast is needed simply because I'm connecting to a MS SQL 2008 database that stores the value as an Integer. However, the value in the data file is not. It's a string. So I have to convert it to an Int.

Also, I'm using the FreeTDS library that allows me connect to and query the database from Linux. There a is a bug in the library that prevents SQL parameter substitution from working in DBI. So, that's why I've created this script, simply to perform parameter substitution before the query gets executed.

[dejohnny]

Here's a code snippet from Alreich Fernandes via LinkedIn

Alreich Fernandes • well Darrel, i'm not sure if that CAST function is needed. if its not a numeric value, it'll get bombed when oracle executes it. 
you could just use the map function there as well, so,


open $fhOut,">>out.sql" or die $!;
open $fhIn,"sqlData.dat" or die $!;

print $fhOut
map { chomp;
qq{SELECT photo.photo
FROM inmate_photo AS photo
WHERE photo.photo_index = CAST( } . substr( $_ , 250, 10). qq{ AS INTEGER );} } <$fhIn>;
close $fhIn;
close $fhOut;