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

[Kevin K. <kk...@ny...>]

On 04/11/2011 02:48 AM, to...@tu... wrote:
> 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).

Can you give me the schema for the 'besitzer' table?

I'm not seeing the same phenomenon that you are.  I *do* get it
for a statement like:

prepare foo as select $1

where there is actually no information about $1's data type.  (Please
log a bug at tdbc.tcl.tk about the cast syntax :foo::VARCHAR -- that's
an easy change.)

But I don't get the error for statements like

select id from people where name = $1

In that case, it correctly infers the 'text' type for 'name' and
transfers it successfully.

For what it's worth, my PostgreSQL server reports 8.4.7 and not 8.4.5,
so maybe there was a bug fixed along the way?

Are you set up to build tdbc? If I give you source patches, can you
test them? I have a few ideas for potential fixes.

-- 
73 de ke9tv/2, Kevin