Search Postgresql Archives

Re: Problem with using snprintf in C-function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



2009/10/17 Ilya Urikh <ilya.urikh@xxxxxxxxx>:
> Hi,
>
> I have a problem with snprintf function which insert the variables to string
> with error.
> This code I wrote for PostgreSQL 8.3.7 on Fedora 10 and it worked fine. But
> now I use CentOS 5.3 and PostgreSQL 8.3.8.
>
> Function:
> Datum calculateAccount(PG_FUNCTION_ARGS) {
>     int64   accountId = PG_GETARG_INT64(0);
>     DateADT startDate = PG_GETARG_DATEADT(1);
>     DateADT endDate = PG_GETARG_DATEADT(2);
>
>     char    command[QUERY_MAX_SIZE];
>     char    startDateStr[MAXDATELEN + 3];
>     char    endDateStr[MAXDATELEN + 3];
>
>     snprintf(startDateStr, sizeof (startDateStr), "'%s'",
> DatumGetCString(DirectFunctionCall1(date_out, startDate)));
>     snprintf(endDateStr, sizeof (endDateStr), "'%s'",
> DatumGetCString(DirectFunctionCall1(date_out, endDate)));
>     elog(INFO, startDateStr);
>     elog(INFO, endDateStr);
>     snprintf(command, sizeof (command),
>             "SELECT serviceId, periodStartDate, periodEndDate\
>              FROM   accountServiceBaseView\
>              WHERE  accountId = %ld AND\
>                     periodStartDate <= %s AND\
>                     periodEndDate >= %s;",
>             accountId, startDateStr, endDateStr);
>     elog(INFO, command);
>

Hello

my reply is little bit offtopic. Why you convert date values to
string? You can use placeholders and execute query with parameters in
native format. It's more simply and safe.

Regards
Pavel Stehule




>     PG_RETURN_BOOL(true);
> }
>
> Result:
> select calculateaccount(123, '01-01-2009', '01-02-2009');
> INFO:  '2009-01-01'
> INFO:  '2009-02-01'
> INFO:  SELECT serviceId, periodStartDate, periodEndDate FROM
> accountServiceBaseView WHERE  accountId = 123 AND periodStartDate <= (null)
> AND periodEndDate >= '2009-01-01';
>
>
> If I change the order of parameters to "periodStartDate <= %s AND
> periodEndDate >= %s AND accountId = %ld", all works fine. Unfortunately this
> method can not be applied to other functions of my module.
>
>
> --
> Best regards,
> Ilya Urikh.
>

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux