steve2312
asked on
Prepare statement failing when using a command line argument for date value
I am executing my script on the command line with parameter ('?') values for $firstday and $lastday. I am passing values sysdate-60 and sysdate for these 2 values of '?' These values are called from a oracle database function of a DATE datatype. But the script is failing with on a non-numeric character found on '?'. When I run the SELECT query by itself on the database, it works and returns rows.
Is the way I have written the prepare statement in call a database function date parameter, the issue?
My prepare and bind/execute statements are:
Is the way I have written the prepare statement in call a database function date parameter, the issue?
My prepare and bind/execute statements are:
my ($db, $user, $pw, $wkdir, $firstday, $lastday) = @ARGV;
..
...
...
my $gen_text = $dbh->prepare(qq(SELECT col1, col2, col3 FROM region WHERE status = 'Y' and partition_key = (Select common.get_region('REGION',?, ?) from dual)))
or die("Could not get data from table '$_'".DBI->errstr);
...
$gen_text->bind_param(1,$firstday);
....
$gen_text->bind_param(2,$lastday);
..
$gen_text->execute();
Have you tried to escape the "?" with a backslash "\"?
ASKER
Thanks for your suggestion. I did try to use the backslash with ?, the prepare fails with error message...
Code used:
Error:
Code used:
SELECT common.get_region('REGION',"\"?, "\"?) from dual
Error:
illegal zero-length identifier (DBD ERROR: OCIStmtPrepare)
I'm not sure. Any command that works talking directly to the sql database should work with the proper escaping and quoting from within a perl script.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Found out the issue was on the database parameter format and not with Perl Prepare statement error.