Thread: Problem with using snprintf in C-function
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);
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.
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);
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.
2009/10/17 Ilya Urikh <ilya.urikh@gmail.com>: > 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. >
Pavel,
Could you provide some example? I don't really understand which placeholder I can use.
--
Best regards,
Ilya Urikh.
Could you provide some example? I don't really understand which placeholder I can use.
On Sun, Oct 18, 2009 at 1:20 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2009/10/17 Ilya Urikh <ilya.urikh@gmail.com>:Hello> 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);
>
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.
>
--
Best regards,
Ilya Urikh.
2009/10/17 Ilya Urikh <ilya.urikh@gmail.com>: > Pavel, > > Could you provide some example? I don't really understand which placeholder > I can use. > here is one fragment from orafce: it's for delete statement, but SELECT statement is similar ItemPointer tid; Oid argtypes[1] = {TIDOID}; char nulls[1] = {' '}; Datum values[1]; void *plan; tid = &rettuple->t_data->t_ctid; if (!(plan = SPI_prepare("DELETE FROM ora_alerts WHERE ctid = $1", 1, argtypes))) ereport(ERROR, (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), errmsg("SPI_prepare failed"))); values[0] = ItemPointerGetDatum(tid); if (SPI_OK_DELETE != SPI_execute_plan(plan, values, nulls, false, 1)) ereport(ERROR, (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), errmsg("can't execute sql"))); SPI_finish(); return PointerGetDatum(rettuple); I don't found better samples, but both functions are well documented. your code should look like: Datum params[3]; char nulls[3] = {' ',' ',' '} ; Oid types[3] = {INT8OID, DATEOID, DATEOID}; void *plan; params[0] = PG_GETARG_DATUM(0); params[1] = PG_GETARG_DATUM(1); params[2] = PG_GETARG_DATUM(2); nulls[0] = PG_ARGISNULL(0) ? 'n' : ' '; ... plan = SPI_prepare("SELECT ...",3, types); if (plan == NULL) ... raise exception result = SPI_execute_plan(plan, params, nulls, true, -1); ... Regards Pavel Stehule. > On Sun, Oct 18, 2009 at 1:20 AM, Pavel Stehule <pavel.stehule@gmail.com> > wrote: >> >> 2009/10/17 Ilya Urikh <ilya.urikh@gmail.com>: >> > 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. >> > > > > > -- > Best regards, > Ilya Urikh. >
Ilya Urikh <ilya.urikh@gmail.com> writes: > 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. It looks to me like you're assuming that %ld is the appropriate format string for an int64 value. This might be true on a 64-bit machine but it would not be on 32-bit. It's not obvious why that would lead to exactly the results you show, but it could certainly account for snprintf fetching the wrong bits for some of its arguments. The portable way (within Postgres code) is to use INT64_FORMAT, which will be set up correctly by configure. So instead of > WHERE accountId = %ld AND\ you need WHERE accountId = " INT64_FORMAT " AND\ (relying on concatenation of literals in C). BTW, if your compiler didn't warn you about this, you need to get a new compiler, or at least start using the switches that would make it warn you. If you did get a warning, and ignored it, you need to learn better habits ;-) regards, tom lane