DBI prepare ?,?,? execute @param problem

[mlibeson]

Below is the error message I get:

DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '@P1'. (SQL-42000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (SQL-42000)(DBD: st_execute/SQLExecute err=-1) at perl.pl line 7263, <SOURCE> line 420.

Below are the modules I use in the script:

use strict;
use Socket;
use Tk;
use Tk::ProgressBar;
use DBI;
use Parallel::ForkManager;

use Date::Manip;

Below is the output from perl -V:

Summary of my perl5 (revision 5 version 8 subversion 6) configuration:
  Platform:
    osname=MSWin32, osvers=4.0, archname=MSWin32-x86-multi-thread
    uname=''
    config_args='undef'
    hint=recommended, useposix=true, d_sigaction=undef
    usethreads=define use5005threads=undef useithreads=define usemultiplicity=define
    useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
    use64bitint=undef use64bitall=undef uselongdouble=undef
    usemymalloc=n, bincompat5005=undef
  Compiler:
    cc='cl', ccflags ='-nologo -Gf -W3 -MD -Zi -DNDEBUG -O1 -DWIN32 -D_CONSOLE -
DNO_STRICT -DHAVE_DES_FCRYPT  -DNO_HASH_SEED -DPERL_IMPLICIT_CONTEXT -DPERL_IMPL
ICIT_SYS -DUSE_PERLIO -DPERL_MSVCRT_READFIX',
    optimize='-MD -Zi -DNDEBUG -O1',
    cppflags='-DWIN32'
    ccversion='', gccversion='', gccosandvers=''
    intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234
    d_longlong=undef, longlongsize=8, d_longdbl=define, longdblsize=10
    ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='__int64', lseeksize=8
    alignbytes=8, prototype=define
  Linker and Libraries:
    ld='link', ldflags ='-nologo -nodefaultlib -debug -opt:ref,icf  -libpath:"C:
\Perl\lib\CORE"  -machine:x86'
    libpth=\lib
    libs=  oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib  comdlg32.lib advapi32.lib shell32.lib ole32.lib oleaut32.lib  netapi32.lib uuid.lib ws2_
32.lib mpr.lib winmm.lib  version.lib odbc32.lib odbccp32.lib msvcrt.lib
    perllibs=  oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib  comd
lg32.lib advapi32.lib shell32.lib ole32.lib oleaut32.lib  netapi32.lib uuid.lib
ws2_32.lib mpr.lib winmm.lib  version.lib odbc32.lib odbccp32.lib msvcrt.lib
    libc=msvcrt.lib, so=dll, useshrplib=yes, libperl=perl58.lib
    gnulibc_version='undef'
  Dynamic Linking:
    dlsrc=dl_win32.xs, dlext=dll, d_dlsymun=undef, ccdlflags=' '
    cccdlflags=' ', lddlflags='-dll -nologo -nodefaultlib -debug -opt:ref,icf  -
libpath:"C:\Perl\lib\CORE"  -machine:x86'


Characteristics of this binary (from libperl):
  Compile-time options: MULTIPLICITY USE_ITHREADS USE_LARGE_FILES PERL_IMPLICIT_CONTEXT PERL_IMPLICIT_SYS
  Locally applied patches:
        ActivePerl Build 811
        21540 Fix backward-compatibility issues in if.pm
        23565 Wrong MANIFEST.SKIP
  Built under MSWin32
  Compiled at Dec 13 2004 09:52:01
  @INC:
    C:/Perl/lib
    C:/Perl/site/lib
    .

Here is the connection code:

my $DSN = 'driver=' . $dbdriver . ';Server=' . $main::ds . ';uid=' . $main::usr . ';pwd=' . $main::pass . ';';

$main::dbh  = DBI->connect("DBI:ODBC:$DSN", { AutoCommit => 1 }) or die "$DBI::errstr\n";

The prepare and execute code snippets exist within a subroutine that is called for every record processed, but is only run every X number of records processed.

Here is the prepare code:

     $sql = 'EXECUTE proc_RECINSERT(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';

     $sh = $main::dbh->prepare($sql);

Here is the execute part:

               foreach my $s (@main::recbufferstr) {
                    my @ps = (split(/#=#=#/,$s));
                    $sh->execute(q{$ps[0]},q{$ps[1]},$ps[2],$ps[3],$ps[4],$ps[5],$ps[6],$ps[7],$ps[8],$ps[9],$ps[10],$ps[11],$ps[12],$ps[13],$ps[14],$ps[15],$ps[16],$ps[17],$ps[18],$ps[19],$ps[20],$ps[21],$ps[22],$ps[23],$ps[24],$ps[25],$ps[26],$ps[27],$ps[28]);
#                    $sh->execute(@ps);
               print STDOUT "[$s]\n";sleep(5);
               }

Does anyone know why I am getting the error message?

Any help is appreciated. Thank you.


Michael Libeson

 

[PaulTEG]

q{$ps[0]}

I've not seen this construct before, mixed languages perhaps?
--Paul



cigless ...

 

[mlibeson]

PaulTEG,

q{something} = 'something'


Michael Libeson

 

[dmazzini]

Guessing:
1. Reserved Words???
can you verify the data that you are inserting, sometimes if we use reserved words it fails,for instance the next example fails:
*******************************************************
sub insertEnduserCompanyTable
{
my $leadID = $_[0];
my $company = $_[1];
my $dateos = $_[2];
my $website = $_[3];
my $addr = $_[4];
my $city = $_[5];
my $state = $_[6];
my $zip = $_[7];
my $contact = $_[8];
my $email = $_[9];
my $PhoneNumber = $_[10];
my $ext = $_[11];
my $FaxNumber = $_[12];
my $calltime = $_[13];
my $business = $_[14];
my $installdate = $_[15];
my $comments = $_[16];


my $SQL;

my $temp1 = "INSERT INTO EnduserCompanyTable ";
my $temp2 = "(LEAD_ID,COMPANY,DATE,WEBSITE,ADDRESS,CITY,STATE,ZIP,CONTACT,EMAIL,PHONE,EXT,FAX,CALL_TIME,BUSINESS,INSTALL_DATE,COMMENTS) ";
my $temp3 = "VALUES ";
my $temp4 = "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";

$SQL = $temp1.$temp2.$temp3.$temp4;

$sth = $dbh->prepare( $SQL );

#syntax error is reported on the next line:$sth->execute.
$sth->execute($leadID,$company,$dateos,$website,$addr,$city,$state,$zip,$contact,$email,$PhoneNumber,$ext,$FaxNumber,$calltime,$business,$installdate,$comments);

}# of insertEnduserCompanyTable

**********************************************************
The problem is that DATE is a reserved word for this SQL engine Solution write as [DATE]

2. Data Conversion. Just compare columns definition format vs data format...???





dmazzini
GSM System and Telecomm Consultant

 

[PaulTEG]

You don't need to quote placeholders though ...
--Paul

cigless ...

 

[mlibeson]

This segment is to add a little clarity.

I am trying to use the prepare and execute pair in order to improve effeciency in my code by using the placeholders. Previously, I ran the same code with the same data without problems except that I was using the do function instead. I used all the proper quotations where they were needed, but am not able to get the "?" placeholder to work with the prepare and execute.

Here is the do call:

     $sql = 'EXECUTE proc_RECINSERT ';
     $sql .= &gethost($$field[$main::HOST_ADDRESS]) . ",";
     $sql .= "'" . &getauthuser("$$field[$main::AUTHUSER]") . "',";
     $sql .= "$main::sum_year,";
     $sql .= "$main::sum_month,";
     $sql .= "$main::sum_day,";
     $sql .= "$main::sum_hour,";
     $sql .= "$main::sum_min,";
     $sql .= "$main::sum_sec,";
     $sql .= "'" . &gettzsign("$main::set_TZ_conv") . "',";
     $sql .= &gettzhour("$main::set_TZ_conv") . ",";
     $sql .= &gettzmin("$main::set_TZ_conv") . ",";
     $sql .= "'" . &getquery($$field[$main::METHOD]) . "',";
     $sql .= "'" . &getpage($$field[$main::PAGE_REQUEST]) . "',";
     $sql .= "'" . &getquery($$field[$main::QUERY]) . "',";
     $sql .= &getstatus("$$field[$main::HTTP_STATUS]") . ",";
     $sql .= &getbytes("$$field[$main::BYTES]") . ",";
     $sql .= &getrefer($$field[$main::REFER]) . ",";
     $sql .= &getagent($$field[$main::USER_AGENT]) . ",";
     $sql .= "'" . &getcookie("$$field[$main::COOKIE]") . "',";
     $sql .= "'" . $main::line[$main::SERVERHOST] . "',";
     $sql .= "'" . $main::sourcefile . "',";
     $sql .= "$main::exclude,";
     $sql .= "'other'";

          push(@main::bufferstr, "$sql");
          if (scalar(@main::bufferstr) >= $main::buffercount) {
               $sh = $main::dbh->do(join("\n", @main::bufferstr));
               @main::bufferstr = ();
          }

Thank you for the posts so far.


Michael Libeson

 

[PaulTEG]

@P1 isn't mentioned in your code, so I'm guessing it's deep into the code of the DBD::ODBC driver.

I was always give to the impression that you didn't have to quote when using placeholder that the driver looked after all that for you.

Back to basics

Build up the select statement without placeholders, ie actual data
ie

     $sql = 'EXECUTE proc_RECINSERT($p[0],$p[1], ..., $p[n])';
     $sh = $main::dbh->prepare($sql) or warn $DBI->errstr;
     $sh ->execute() or warn $DBI->errstr;

Try running the same command in an SQL console.

BTW, I'm not sure that you can fire stored procedures over ODBC, unless they're triggers on the DB - could be wrong, it's happened before ;-)
--Paul

 

[mlibeson]

PaulTEG,

Thanks for the feedback. I am going to try and find @P1 in the DBI or DBD::ODBC modules.

As for the latter part of your posting, I have already been able to successfully execute the stored procedure with the same data. I am trying to improve the efficiency of the code as it takes longer than it should. The problem only occurs when I convert the do call to a prepare and execute with placeholders.

You are correct about not placing any quotes around the ? placeholders. It still does not work.

I was hoping by posting here I could avoid looking for the needle in the hay stack. Oh well, here I go.

If I find a solution, I will definitely post a reply.

Thanks again all for your help.


Michael Libeson

 

[mlibeson]

OK, turns out that when you are using a stored procedure in a prepare statement, you must specify the binding variables with their respective cast types. I did this, but now I am getting a cast error. The message does not tell me which element has the problem. Any ideas on how to find the problem from here?


Michael Libeson

 

[mlibeson]

OK it works now.

I removed the parens from the EXECUTE SQL statement as they do not belong.

I used $sh->execute();

And, I use the bind for each value/parameter within my foreach loop as it sets the value the first time you bind the variable and is not updateable.

Ex bind:

$sh->bind_param(1,$ps[0],{TYPE => "SQL_SMALLINT"});

1 => identifies the binding of the first parameter.

$ps[0] => binds the value of the first element of @ps to the first parameter in the stored procedure.

(TYPE => "SQL_SMALLINT"} => Tells DBI the type of value to pass to the stored procedure. By default DBI passes SQL_VARCHAR, but I forced it anyway just to make sure.



Michael Libeson