Обсуждение: ODBC int2 parameters to pg function

I have discovered a problem with passing parameters to a pg function
using the ODBC driver.

I have delcared a pg function as:

function GENERATE_TIMESHEETS(timestamp,timestamp,smallint,smallint)
....

I have a .NET program that uses the following:
cmd.CommandText = "SELECT GENERATE_TIMESHEETS(?,?,?,?)";
cmd.Parameters.Add("DATE_FROM",OdbcType.DateTime,8);
cmd.Parameters.Add("DATE_TO",OdbcType.DateTime,8);
cmd.Parameters.Add("TIME_FROM",OdbcType.SmallInt,2);
cmd.Parameters.Add("TIME_TO",OdbcType.SmallInt,2);

I then assign the parameter values and execute the statement.

pg returns an error stating
GENERATE_TIMESHEETS(timestamp,timestamp,int4,int4) cannot be found

It appears that the ODBC driver is not typecasting the smallint (int2)
parameters correctly for the function call.

Can anyone shed any light on this?

Thanks
Gary.

Gary Doades <gpd@cwcom.net> writes:
> I have delcared a pg function as:
> function GENERATE_TIMESHEETS(timestamp,timestamp,smallint,smallint)
> ....

You're probably best off to declare the inputs as integer not smallint.
Integer literals are assumed to be int4 by default, and since there's
no implicit downcast to int2 (anymore), writing a function this way will
require you to cast to smallint explicitly a lot.  It's seldom worth
that notational hassle.  You can cast inside the function if you really
need the values to be int2 inside it.

            regards, tom lane

Tom,
Thanks for the reply.

I agree with your solution in principle, but I have nearly 100 databases
so far across several servers and many at customer sites. I am trying to
migrate some from and maintain compatibility with MS SQLServer.
These quantities have been smallint for quite some time. Changing the
underlying database types now would be a problem.

I can cast inside the function, this is my current solution. Given that
there is no implicit downcast wouldn't the best solution be to get the
ODBC driver do the cast on the function call?

Thanks,
Gary.

On 13 Mar 2004 at 19:40, Tom Lane wrote:

> Gary Doades <gpd@cwcom.net> writes:
> > I have delcared a pg function as:
> > function GENERATE_TIMESHEETS(timestamp,timestamp,smallint,smallint)
> > ....
>
> You're probably best off to declare the inputs as integer not smallint.
> Integer literals are assumed to be int4 by default, and since there's
> no implicit downcast to int2 (anymore), writing a function this way will
> require you to cast to smallint explicitly a lot.  It's seldom worth
> that notational hassle.  You can cast inside the function if you really
> need the values to be int2 inside it.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

"Gary Doades" <gpd@gpdnet.co.uk> writes:
> I can cast inside the function, this is my current solution. Given that
> there is no implicit downcast wouldn't the best solution be to get the
> ODBC driver do the cast on the function call?

I think that would likely fix this example at the cost of breaking many
others.  Exactly what conditions would you have the ODBC driver cast to
int2 under?

            regards, tom lane

I don't see how it would break anything. I the case where the driver is substituting
ODBC parameters it ought to substitute (bind) the datatype that you have asked for.

In the case of "Select a_func(?,?)". If the two parameters are timestamp and smallint,
then the ODBC bind function calls specify these datatypes. Currently the ODBC
driver calls the function with a timestamp and an int4 parameter, where it should call
it with a timestamp and an int2 parameter.

I have checked a few other datatypes and looked at the "mylog" output. It seems that
dates, timestamp, numeric, floats are all explicitly typecast by the ODBC driver to
make sure the pg function gets called correctly, but not int2.

I'm not suggesting that general SQL strings are scanned and typecasts inserted.
Where functions/statements are called with parameters and those parameters then
substitued (bound) at run-time then it should get the types right where it matters.
AFAICS it is only int2 types that are not typecast. All other datatypes (except string
and int4 which don't need it) are typecast explicitly by the driver before being passed
to the server.

Regards,
Gary.

On 14 Mar 2004 at 12:30, Tom Lane wrote:

> "Gary Doades" <gpd@gpdnet.co.uk> writes:
> > I can cast inside the function, this is my current solution. Given that
> > there is no implicit downcast wouldn't the best solution be to get the
> > ODBC driver do the cast on the function call?
>
> I think that would likely fix this example at the cost of breaking many
> others.  Exactly what conditions would you have the ODBC driver cast to
> int2 under?
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

"Gary Doades" <gpd@gpdnet.co.uk> writes:
> AFAICS it is only int2 types that are not typecast. All other
> datatypes (except string and int4 which don't need it) are typecast
> explicitly by the driver before being passed to the server.

I see.  AFAIR you hadn't mentioned that you were declaring the parameter
as smallint to ODBC.  I tend to agree that a cast should be added in
that case.  It's not my code though ...

            regards, tom lane

Thanks Tom,

I did actually put the parameter binding examples in my original post,
but as they are in .NET speak it may not have been obvious.

I guess I should try to get this on the "things to do with the ODBC"
driver list. I should be able to look at the code myself, but I will have to
remember how to speak C.

Thanks again,
Gary.


On 14 Mar 2004 at 13:24, Tom Lane wrote:

> "Gary Doades" <gpd@gpdnet.co.uk> writes:
> > AFAICS it is only int2 types that are not typecast. All other
> > datatypes (except string and int4 which don't need it) are typecast
> > explicitly by the driver before being passed to the server.
>
> I see.  AFAIR you hadn't mentioned that you were declaring the parameter
> as smallint to ODBC.  I tend to agree that a cast should be added in
> that case.  It's not my code though ...
>
>             regards, tom lane