Trouble with Update and Placeholders...

--=_alternative 0071111C85256F26_=
Content-Type: text/plain; charset="US-ASCII"

Please forgive me if this is a newbie question, but I have searched all
over and can't seem to find the answer I am looking for.

I am getting DataType mismatch errors when posting to a Lotus Notes DB
through ODBC using placeholders. The same code works fine if I do not use
a placeholder. I would like to use the placeholder feature of DBI because
I will do several thousand updates using the same query and don't want to
waste time preparing the statement every time.

This Works: (Note no placeholder for $passwordAge)
$SqlString = "UPDATE tblPasswd SET fullName = ?, distinguishedName = ?,
topLevelOU = ?, accountStatus = ?, passwordAge =$passwordAge WHERE
samAccountName = ?";
$sqlUpdateUnChanged = $dbPasswd->prepare($SqlString);
$sqlUpdateUnChanged->execute($fullName, $distinguishedName, $topLevelOU,
$accountStatus, $samAccountName);


This fails:
$SqlString = "UPDATE tblPasswd SET fullName = ?, distinguishedName = ?,
topLevelOU = ?, accountStatus = ?, passwordAge =? WHERE samAccountName =
?";
$sqlUpdateUnChanged = $dbPasswd->prepare($SqlString);
$sqlUpdateUnChanged->execute($fullName, $distinguishedName, $topLevelOU,
$accountStatus, $passwordAge, $samAccountName);

The Error message returned is:
DBD::ODBC::st execute failed: [Lotus][ODBC Lotus Notes]Value must match
column data type (SQL-42000)(DBD: st_execute/SQLExecute err=-1) at
passwordAge.pl line 247, line 283.

Any help would be appreciated.

Thanks!

Chris Gehring
Network Operations
703.841.7937
--=_alternative 0071111C85256F26_=--

RE: Trouble with Update and Placeholders...

>=20
>=20
> Please forgive me if this is a newbie question, but I have=20
> searched all=20
> over and can't seem to find the answer I am looking for.
>=20
> I am getting DataType mismatch errors when posting to a Lotus=20
> Notes DB=20
> through ODBC using placeholders. The same code works fine if=20
> I do not use=20
> a placeholder. I would like to use the placeholder feature=20
> of DBI because=20
> I will do several thousand updates using the same query and=20
> don't want to=20
> waste time preparing the statement every time.
>=20
> This Works: (Note no placeholder for $passwordAge)
> $SqlString =3D "UPDATE tblPasswd SET fullName =3D ?,=20
> distinguishedName =3D ?,=20
> topLevelOU =3D ?, accountStatus =3D ?, passwordAge =3D$passwordAge =
WHERE=20
> samAccountName =3D ?";
> $sqlUpdateUnChanged =3D $dbPasswd->prepare($SqlString);=20
> $sqlUpdateUnChanged->execute($fullName, $distinguishedName,=20
> $topLevelOU,=20
> $accountStatus, $samAccountName);
>=20
>=20
> This fails:
> $SqlString =3D "UPDATE tblPasswd SET fullName =3D ?,=20
> distinguishedName =3D ?,=20
> topLevelOU =3D ?, accountStatus =3D ?, passwordAge =3D? WHERE=20
> samAccountName =
> ?";
> $sqlUpdateUnChanged =3D $dbPasswd->prepare($SqlString);=20
> $sqlUpdateUnChanged->execute($fullName, $distinguishedName,=20
> $topLevelOU,=20
> $accountStatus, $passwordAge, $samAccountName);
>=20
> The Error message returned is:
> DBD::ODBC::st execute failed: [Lotus][ODBC Lotus Notes]Value=20
> must match=20
> column data type (SQL-42000)(DBD: st_execute/SQLExecute err=3D-1) at=20
> passwordAge.pl line 247, line 283.

Chris,

I'm guessing a few things:
1) that passwordAge is a numeric type in Notes.
2) DBD::ODBC is *trying* to bind SQL_INTEGER type (see the trace file)
3) You can try forcing the bound variable to a specific type by
$sth->bind_param(index, SQL_INTEGER, value) or $sth->bind_param(index, =
SQL_VARCHAR, value)
(note you should use DBI (:sql_types); to get SQL_XXX constants =
imported) to see which
works for Notes. Some drivers do not necessarily like the implicit =
conversions from
varchar to number or vice-versa. With the NotesSQL driver, YMMV.

Turn on DBI tracing level 9 (just to be safe) and see what DBD::ODBC =
thinks the type is.
You can send the it to me privately, if you want help interpreting.

Regards,

Jeff


>=20
> Any help would be appreciated.
>=20
> Thanks!
>=20
> Chris Gehring
> Network Operations
> 703.841.7937
>=20

Re: Trouble with Update and Placeholders...

On Thu, Oct 07, 2004 at 06:47:29PM -0400, Jeff Urlwin wrote:
> 3) You can try forcing the bound variable to a specific type by
> $sth->bind_param(index, SQL_INTEGER, value) or $sth->bind_param(index, SQL_VARCHAR, value)

Typo, swap the last two args in each example. The type comes last.

Tim.

RE: Trouble with Update and Placeholders...

Doh.

Thanks Tim.

Jeff


> -----Original Message-----
> From: Tim Bunce [mailto:Tim.Bunce@pobox.com]
> Sent: Friday, October 08, 2004 5:30 AM
> To: Jeff Urlwin
> Cc: 'Chris Gehring'; dbi-users@perl.org
> Subject: Re: Trouble with Update and Placeholders...
>
>
> On Thu, Oct 07, 2004 at 06:47:29PM -0400, Jeff Urlwin wrote:
> > 3) You can try forcing the bound variable to a specific type by
> > $sth->bind_param(index, SQL_INTEGER, value) or
> $sth->bind_param(index,
> > SQL_VARCHAR, value)
>
> Typo, swap the last two args in each example. The type comes last.
>
> Tim.
>