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

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

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

On Mon, Apr 11, 2011 at 10:48:09PM -0400, Kevin Kenny wrote:
> On 04/11/2011 02:48 AM, to...@tu... wrote:
>> What happens is that PostgreSQL sets the type of the parameter to
>> "unspecified" [...]

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

Yes, sorry. Here it is:

    CREATE TABLE besitzer (
      id SERIAL   PRIMARY KEY,
      b_name      VARCHAR
    );

...just cut down to bare minimum (it's wht I used in preparing this
example, though)-

> 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.

Aha. So it seems it'd work if b_name above were TEXT instead of VARCHAR
[runs away for testing...]

HAH. That's it. Declaring the colum as TEXT instead of VARCHAR does the
trick. This one doesn't make trouble :-)

So a workaround would be to use TEXT instead of VARCHAR. They are the
same for PostgreSQL anyway.

> 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?

Hm. I don't know. What's interesting is that psql doesn't complain in
this case either:

 |  tomas@floh:~$ psql foo
 => psql (8.4.5)
 => Type "help" for help.
 |  
 |  foo=# prepare muh as select id, b_name from blub where b_name = $1;
 => PREPARE

(sorry for the name changes :-/

> 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.

I'd glad to help in any way, I'm a big fan of Tdbc. On the other hand,
it's strange VARCHAR and TEXT behave so differently, so the right thing
might be to take it to the PostgreSQL folks. I'll keep you in the loop.

Thanks for looking into it
- -- tomás
>
> -- 
> 73 de ke9tv/2, Kevin
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD4DBQFNo+u/Bcgs9XrR2kYRAg51AJ9/N/kqIKBuKAYV5qpPf6XkzErtMACUDIA+
p+yJptjlnCjktLho7CJ49Q==
=S3WK
-----END PGP SIGNATURE-----