7 Reeds 7 Reeds - 3 months ago 20
Perl Question

Perl, DBI MS SQL: Can't bind unknown placeholder

I have Perl, DBI and ODBC drivers running on a RedHat box. I know that from a very, very recent sister question that the ODBC connection is working. I am having issues with the first

bind_param
in a query. I am given the error:

Can't bind unknown placeholder '1' at ./script.pl line 361.


That portion of the code looks like:

326: sub isStaff {
327: my ($MSdbh, $PersonLoginID, $start, $end) = @_;
328:
329: my $sth = undef;
330:
331: my $result = undef;
332:
333: my $tmp = <<EOF;
334: SELECT
335: P.PersonLoginID
336: FROM
337: Personnel.Persons AS P
338: INNER JOIN Personnel.Memberships AS M ON M.PersonId = P.PersonId
339: INNER JOIN Personnel.Groups AS G ON G.GroupId = M.GroupId
340: WHERE
341: UPPER(P.PersonLoginID) = UPPER('?')
342: AND (G.GroupAbbv = 'MyGroup'
343: OR G.GroupAbbv = 'SubGroup1'
344: OR G.GroupAbbv = 'SubGroup2')
345: AND (M.StartDate <= '?'
346: OR M.StartDate BETWEEN '?' AND '?')
347: AND (M.EndDate >= '?'
348: OR M.EndDate IS NULL)
349: EOF
350:
351: $MSdbh->trace(2);
352:
353: if ( !( $sth = $MSdbh->prepare($tmp) ) ) {
354: die "DB prepare failed";
355: }
356:
357: print STDERR "$PersonLoginID\n";
358: print STDERR "$start\n";
359: print STDERR "$end\n";
360:
361: $sth->bind_param( 1, $PersonLoginID, SQL_VARCHAR )
362: || die "bind_param 1:" . $sth->errstr;
363: $sth->bind_param( 2, $start, SQL_DATETIME )
364: || die "bind_param 2:" . $sth->errstr;
365: $sth->bind_param( 3, $start, SQL_DATETIME )
366: || die "bind_param 3:" . $sth->errstr;
367: $sth->bind_param( 4, $end, SQL_DATETIME )
368: || die "bind_param 4:" . $sth->errstr;
369: $sth->bind_param( 5, $end, SQL_DATETIME )
370: || die "bind_param 5:" . $sth->errstr;
371:
372: if ( !( $sth->execute() ) ) {
373: die "DB execute failed";
374: }
375:
376: $sth->bind_col(1, \$PersonLoginID )
377: || die "bind_columns:" . $sth->errstr;
378:
379: my $i = 0;
380: for ($i = 0; $sth->fetch; $i++) {
381: ;
382: }
383:
384: $sth->finish;
385:
386: if ($i == 1) {
387: return 1;
388: } else {
389: return 0;
390: }
391: }


The
DBI->trace()
and diagnostic print output is:

DBI::db=HASH(0x35c5420) trace level set to 0x0/2 (DBI @ 0x0/0) in DBI 1.634-ithread (pid 5792)
-> prepare for DBD::ODBC::db (DBI::db=HASH(0x35c54c8)~0x35c5420 'SELECT
P.PersonLoginID
FROM
Personnel.Persons AS P
INNER JOIN Personnel.Memberships AS M ON M.PersonId = P.PersonId
INNER JOIN Personnel.Groups AS G ON G.GroupId = M.GroupId
WHERE
UPPER(P.PersonLoginID) = UPPER('?')
AND (G.GroupAbbv = 'MyGroup'
OR G.GroupAbbv = 'SubGroup1'
OR G.GroupAbbv = 'SubGroup2')
AND (M.StartDate <= '?'
OR M.StartDate BETWEEN '?' AND '?')
AND (M.EndDate >= '?'
OR M.EndDate IS NULL)
') thr#299a010
<- prepare= ( DBI::st=HASH(0x3641e08) ) [1 items] at billing.cgi line 353
SomeLoginID
2016-03-10 13:54:44
2016-03-10 16:11:05
-> bind_param for DBD::ODBC::st (DBI::st=HASH(0x3641e08)~0x3641d78 1 'SomeLoginID' 12) thr#299a010
-> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x3641d78)~INNER) thr#299a010
<- DESTROY= ( undef ) [1 items] at billing.cgi line 361


The diagnostic prints show the values that should be going into the query.

357: print STDERR "$PersonLoginID\n"; --> SomeLoginID
358: print STDERR "$start\n"; --> 2016-03-10 13:54:44
359: print STDERR "$end\n"; --> 2016-03-10 16:11:05


Some DBI doc I have read in the last hour or so says that the
?
is the only "portable" placeholder. Does MS SQL prefer one of the others?

I have seen examples in which place holders for strings do not use single quotes around the placeholder character but that did not work for me.

ideas?

mob mob
Answer

Don't quote the placeholders, even for char/string types.

...
WHERE
     UPPER(P.PersonLoginID) = UPPER('?')
...

should just be

...
WHERE
     UPPER(P.PersonLoginID) = UPPER(?)
...