Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1W1HUV-0005Az-SK for pgsql-interfaces@arkaria.postgresql.org; Thu, 09 Jan 2014 15:25:52 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1W1HUV-0004u6-Ao for pgsql-interfaces@arkaria.postgresql.org; Thu, 09 Jan 2014 15:25:51 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1W1HUT-0004sJ-AZ for pgsql-interfaces@postgresql.org; Thu, 09 Jan 2014 15:25:49 +0000 Received: from mail-yh0-x236.google.com ([2607:f8b0:4002:c01::236]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1W1HUQ-0002yF-Ef for pgsql-interfaces@postgresql.org; Thu, 09 Jan 2014 15:25:49 +0000 Received: by mail-yh0-f54.google.com with SMTP id b12so477152yha.13 for ; Thu, 09 Jan 2014 07:25:45 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:subject :content-type; bh=YTNnxSSHsORwvKkM/TmaKH+x56DBSy+kt9O01ecre9Q=; b=VHzU6lk+TuifQrP3a1in9k8ARK+WCc84vizvfL69Q1GLCDeNBa4IkGmFIOp7fKdbmg ksM6En9n8HFq7u6CcIGGFyMogAMIRYb9bAusRbScTtoxI69pa98+R06unaBAJLgr6YlA cuqtANpxOiqYSw8FmCf5OJaIUP1NRYcGj8dvZAiTDOxzPy5JJoZ/cPPtJBUSBm8n+SVA 8WWn9pxdZ+kw15NjShJlF2YpOkJ/OrPCvtQPpqZV408uP4ZOka1WYo2mHxXmFkYy88n8 ETBhTh88Fb6cIUByOoc4uVvFDBremaM4o3B220YKAZrLl2DpaBS7d3nK5l6+6MJsU2vu Ou3A== X-Received: by 10.236.44.102 with SMTP id m66mr5412520yhb.89.1389281145109; Thu, 09 Jan 2014 07:25:45 -0800 (PST) Received: from ?IPv6:2601:4:1080:11f:16da:e9ff:fe21:2aa4? ([2601:4:1080:11f:16da:e9ff:fe21:2aa4]) by mx.google.com with ESMTPSA id 9sm6817532yhe.21.2014.01.09.07.25.44 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Thu, 09 Jan 2014 07:25:44 -0800 (PST) Message-ID: <52CEBF77.5030503@gmail.com> Date: Thu, 09 Jan 2014 10:25:43 -0500 From: "Raymond C. Rodgers" User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.2.0 MIME-Version: 1.0 To: pgsql-interfaces@postgresql.org Subject: "unsupported format code: 36" in prepared statement with libpq-fe 9.2.5 Content-Type: multipart/alternative; boundary="------------080709010805000906060903" X-Pg-Spam-Score: -1.7 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-interfaces Precedence: bulk Sender: pgsql-interfaces-owner@postgresql.org This is a multi-part message in MIME format. --------------080709010805000906060903 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hello, I haven't been working with libpq-fe for long (only the last month or so) but I've had the good fortune of being able to figure out and solve most problems I've encountered within a few minutes to an hour, but this one is different. I have a prepared statement that is inserting data into 12 of the 13 columns my table has (1 UUID, 4 numerics, 1 bigint, 2 ints, 1 boolean, 2 varchars, and 1 of 2 timestamps are being inserted). This is the first time I've attempted to insert more than 8 columns at once, but I'm pretty sure the number of columns isn't the problem I'm hitting. At least, I don't think it is. Some of the vital details: Fedora 18 (64-bit), PostgreSQL and libpq-fe version 9.2.5, code written in C++ compiled with gcc version 4.7.2 20121109. The errors are being pulled from the pg_logs directory for the appropriate day. (I'm using "tail -f" to watch as they occur.) I've prepared the statement with code similar to the following (lines broken up for slight readability improvement); DB is an PGconn pointer that has already been connected to the database by this point: Oid dozenFieldTypes[12] = {0,0,0,0,0,0,0,0,0,0,0,0}; PQprepare(DB,"insertData","insert into mytable (data1, data2, data3, data4, data5, data6, data7, data8 ,data9, data10, data11, data12) values ($1, $2::double precision, $3::double precision, $4::double precision, $5::double precision,$6,$7,$8, $9::int,$10::int, $11::boolean,$12::timestamp with time zone)",12,dozenFieldType); The above is how the query currently exists after hours of banging my head on this "unsupported format code: 36" error; I've tried casting each for the inputs as the appropriate type in addition to trying to let PostgreSQL/libpq-fe figure it out completely. As you can see, I'm somewhere in the middle of the two at the moment. Nonetheless, I don't get the error on the creation of the prepared statement, though I am curious if the numbering above 9 for the parameters should be in decimal or hexadecimal. I've only tried decimal at this point because it seems logical that it would continue in decimal and I've found no indications otherwise in the documentation. Of course, I also haven't found anything in the documentation that states that a dozen parameters can be used. I'm hitting the error when I try to execute the statement using code similar to the following; only my variable and custom function names have been changed: const char *value[12]; char dt12[30]; memset(dt12,0,30); // convert timestamp from time_t to string struct tm time_s; memset(&time_s,0,sizeof(time_s)); gmtime_r(&data12,&time_s); strftime(dt12,29,"%Y-%m-%d %H:%M:%S GMT",&time_s); int32_t dt9 = bswap_32(data9),dt10 = bswap_32(data10); int64_t dt6 = bswap_64(atol(data6)); // prepDouble is a custom function to swap bytes appropriately // for doubles; proven to work properly elsewhere in code double dt2 = prepDouble(data2),dt3 = prepDouble(data3), dt4 = prepDouble(data4), dt5 = prepDouble(data5); value[0] = data1; // UUID stored as string (char *) value[1] = (char*)&dt2; value[2] = (char*)&dt3; value[3] = (char*)&dt4; value[4] = (char*)&dt5; value[5] = (char*)&dt6; value[6] = data7; //string (char*) taken as is value[7] = data8; //string (char*) taken as is value[8] = (char*)&dt9; value[9] = (char*)&dt10; const char *dt11 = (char*)&data11; // boolean tried as int, string, and in original bool format, made no difference in error value[10] = dt11; value[11] = dt12; int32_t format[12] = {0,0,0,0,0,0,0,0,0,0,0,0}; int32_t length[12] = {(int32_t) strlen(data1), sizeof(dt2), sizeof(dt3), sizeof(dt4), sizeof(dt5), sizeof(dt6), (int32_t) strlen(data7), (int32_t) strlen(data8), sizeof(dt9), sizeof(dt10), sizeof(data11), (int32_t) strlen(dt12)}; PQexecPrepared(DB,"insertData",12,value,format,length,1); Once the PQexecPrepared() has executed, I'm getting the following in the PostgreSQL log: ERROR: unsupported format code: 36 STATEMENT: insert into mytable (data1,data2,data3,data4,data5,data6,data7,data8,data9,data10,data11,data12) values ($1,$2::double precision,$3::double precision,$4::double precision,$5::double precision,$6,$7,$8,$9::int,$10::int,$11::boolean,$12::timestamp with time zone) So far, I haven't been able to determine precisely what "unsupported format code: 36" refers to. I've tried playing with the resultFormat arameter in the PQexecPrepared() call, changing the casting types of just about every data item, and in the case of the boolean, using an intermediate variable of a different type and making the conversion, to rule it out. There *are* two data types that I haven't tried using in libpq-fe before now, though I'm sure one is fine: UUID (data1) and int64_t/bigint (data6). I'm pretty sure that the UUID is fine since it's being stored in the program as a simple string (char *) and I got an error when I adjusted the prepared statement to cast the value as varchar(36). (The error basically said that it was a UUID value and that I need to change the casting.) Data6, on the other hand, is an unknown factor. While I've been inserting 64-bit data types (doubles) into the database easily enough, this is the first time I've tried working with a long/int64_t/bigint and the database. I presume that since the bswap_32 from byteswap.h works properly, that it's sibling bswap_64 is also working properly. As you might notice, data6 is being converted from a string to int64_t via atol(), and I've checked to make sure that's working properly. So, that pretty much explains where I am getting stuck. I haven't been able to find out what "unsupported format code: 36" is referring to, there doesn't seem to be any documentation covering that error, and I think I've done all I can to try to resolve it without having good information about what is causing the error. I'd greatly appreciate any help I can get to resolve this error, and document it for posterity. :-) Best regards and thank you, Raymond --------------080709010805000906060903 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hello,

I haven't been working with libpq-fe for long (only the last month or so) but I've had the good fortune of being able to figure out and solve most problems I've encountered within a few minutes to an hour, but this one is different. I have a prepared statement that is inserting data into 12 of the 13 columns my table has (1 UUID, 4 numerics, 1 bigint, 2 ints, 1 boolean, 2 varchars, and 1 of 2 timestamps are being inserted). This is the first time I've attempted to insert more than 8 columns at once, but I'm pretty sure the number of columns isn't the problem I'm hitting. At least, I don't think it is. Some of the vital details: Fedora 18 (64-bit), PostgreSQL and libpq-fe version 9.2.5, code written in C++ compiled with gcc version 4.7.2 20121109. The errors are being pulled from the pg_logs directory for the appropriate day. (I'm using "tail -f" to watch as they occur.)

I've prepared the statement with code similar to the following (lines broken up for slight readability improvement); DB is an PGconn pointer that has already been connected to the database by this point:

Oid dozenFieldTypes[12] = {0,0,0,0,0,0,0,0,0,0,0,0};
PQprepare(DB,"insertData","insert into mytable (data1, data2, data3, data4, data5,
                 data6, data7, data8 ,data9, data10, data11, data12) values ($1, $2::double precision,
                $3::double precision, $4::double precision, $5::double precision,$6,$7,$8,
                $9::int,$10::int, $11::boolean,$12::timestamp with time zone)",12,dozenFieldType);

The above is how the query currently exists after hours of banging my head on this "unsupported format code: 36" error; I've tried casting each for the inputs as the appropriate type in addition to trying to let PostgreSQL/libpq-fe figure it out completely. As you can see, I'm somewhere in the middle of the two at the moment. Nonetheless, I don't get the error on the creation of the prepared statement, though I am curious if the numbering above 9 for the parameters should be in decimal or hexadecimal. I've only tried decimal at this point because it seems logical that it would continue in decimal and I've found no indications otherwise in the documentation. Of course, I also haven't found anything in the documentation that states that a dozen parameters can be used.

I'm hitting the error when I try to execute the statement using code similar to the following; only my variable and custom function names have been changed:

        const char *value[12];
        char dt12[30];
        memset(dt12,0,30);
   // convert timestamp from time_t to string
        struct tm time_s;
        memset(&time_s,0,sizeof(time_s));
        gmtime_r(&data12,&time_s);
        strftime(dt12,29,"%Y-%m-%d %H:%M:%S GMT",&time_s);

        int32_t dt9 = bswap_32(data9),dt10 = bswap_32(data10);
        int64_t dt6 = bswap_64(atol(data6));
// prepDouble is a custom function to swap bytes appropriately
// for doubles; proven to work properly elsewhere in code
        double dt2 = prepDouble(data2),dt3 = prepDouble(data3),
                 dt4 = prepDouble(data4), dt5 = prepDouble(data5);
        value[0] = data1; // UUID stored as string (char *)
        value[1] = (char*)&dt2;
        value[2] = (char*)&dt3;
        value[3] = (char*)&dt4;
        value[4] = (char*)&dt5;
        value[5] = (char*)&dt6;
        value[6] = data7; //string (char*) taken as is
        value[7] = data8; //string (char*) taken as is
        value[8] = (char*)&dt9;
        value[9] = (char*)&dt10;
        const char  *dt11 = (char*)&data11; // boolean tried as int, string, and in original bool format, made no difference in error
        value[10] = dt11;
        value[11] = dt12;
         int32_t format[12] = {0,0,0,0,0,0,0,0,0,0,0,0};
        int32_t length[12] = {(int32_t) strlen(data1), sizeof(dt2), sizeof(dt3), sizeof(dt4), sizeof(dt5),
                sizeof(dt6), (int32_t) strlen(data7), (int32_t) strlen(data8), sizeof(dt9), sizeof(dt10),
                sizeof(data11), (int32_t) strlen(dt12)};
        PQexecPrepared(DB,"insertData",12,value,format,length,1);

Once the PQexecPrepared() has executed, I'm getting the following in the PostgreSQL log:

ERROR:  unsupported format code: 36
STATEMENT:  insert into mytable (data1,data2,data3,data4,data5,data6,data7,data8,data9,data10,data11,data12) values ($1,$2::double precision,$3::double precision,$4::double precision,$5::double precision,$6,$7,$8,$9::int,$10::int,$11::boolean,$12::timestamp with time zone)

So far, I haven't been able to determine precisely what "unsupported format code: 36" refers to. I've tried playing with the resultFormat arameter in the PQexecPrepared() call, changing the casting types of just about every data item, and in the case of the boolean, using an intermediate variable of a different type and making the conversion, to rule it out. There *are* two data types that I haven't tried using in libpq-fe before now, though I'm sure one is fine: UUID (data1) and int64_t/bigint (data6). I'm pretty sure that the UUID is fine since it's being stored in the program as a simple string (char *) and I got an error when I adjusted the prepared statement to cast the value as varchar(36). (The error basically said that it was a UUID value and that I need to change the casting.) Data6, on the other hand, is an unknown factor. While I've been inserting 64-bit data types (doubles) into the database easily enough, this is the first time I've tried working with a long/int64_t/bigint and the database. I presume that since the bswap_32 from byteswap.h works properly, that it's sibling bswap_64 is also working properly. As you might notice, data6 is being converted from a string to int64_t via atol(), and I've checked to make sure that's working properly.

So, that pretty much explains where I am getting stuck. I haven't been able to find out what "unsupported format code: 36" is referring to, there doesn't seem to be any documentation covering that error, and I think I've done all I can to try to resolve it without having good information about what is causing the error.

I'd greatly appreciate any help I can get to resolve this error, and document it for posterity. :-)

Best regards and thank you,
Raymond
--------------080709010805000906060903--