[Tcl-tdbc] Parameter types in prepared statements for tdbc::postgres

[<to...@tu...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

Since Postgresql about 8.3 (maybe its 8.2, I'm not quite sure), type
guessing has become more stringent [1]. This has a nasty effect on prepared
statements as TDBC usually does them:

 |  tomas@floh:~$ tclsh8.6
 |  % package require tdbc
 => 1.0b14
 |  % package require tdbc::postgres
 => 1.0b14
 |  % set dbconn [tdbc::postgres::connection new -database husch -host localhost -user husch -password liuwah3U]
 => ::oo::Obj9
 |  % set stmt [$dbconn prepare {select id, b_name from besitzer where b_name = :foo}]
 => could not determine data type of parameter $1

What happens is that PostgreSQL sets the type of the parameter to
"unspecified" and has not enough hints to make better guesses. With psql
you can see something similar:

 |  tomas@floh:~$ psql husch
 => psql (8.4.5)
 => Type "help" for help.
 |  
 |  husch=# prepare foo as select id, b_name from besitzer where b_name = $1;
 => ERROR:  could not determine data type of parameter $1

But this, for example, works:

 |  husch=# prepare foo as select id, b_name from besitzer where b_name = '$1';
 |  PREPARE

I have an ugly work-around for that at the moment (concatenating strings
with '' as in SELECT ... WHERE b_name = :foo || ''. Traditional
PostgreSQL casting (à la :foo::VARCHAR) is even uglier and dies a
horrible death because the driver tries to make another parameter out of
that).

I've read about paramtype, but I'm stuck on how to use that *before*
I've got a statement object. Any hints?

Thanks
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFNoqRCBcgs9XrR2kYRAlHLAJ43QTXMk678Prr3+ldbGOvV3INeZgCeL8FQ
rONBBI5esDXFYTO3m5QOC4Q=
=aJpL
-----END PGP SIGNATURE-----