X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BEC0B534E3 for ; Wed, 4 May 2005 14:09:23 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 54114-10 for ; Wed, 4 May 2005 17:09:17 +0000 (GMT) Received: from zeus.w2k.nrlmry.navy.mil (zeus.w2k.nrlmry.navy.mil [199.9.2.132]) by svr1.postgresql.org (Postfix) with ESMTP id 01A8B52A8B for ; Wed, 4 May 2005 14:09:16 -0300 (ADT) content-class: urn:content-classes:message Subject: Re: PQescapeBytea & PQunescapeBytea MIME-Version: 1.0 Date: Wed, 4 May 2005 10:09:15 -0700 Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=SHA1; boundary="----=_NextPart_000_002A_01C55091.52C77310" X-MimeOLE: Produced By Microsoft Exchange V6.0.6556.0 Message-ID: <5188F15076BC7A4DBB705747602058C11AAB96@mailhost> X-MS-Has-Attach: yes X-MS-TNEF-Correlator: Thread-Topic: [INTERFACES] PQescapeBytea & PQunescapeBytea Thread-Index: AcVQtdOgWiDmzLO7RB64GFdBLPVLDwAFU4dA From: "Frost, Mr. Michael (contractor)" To: "Tom Lane" , X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.305 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE X-Spam-Level: X-Archive-Number: 200505/15 X-Sequence-Number: 5518 This is a multi-part message in MIME format. ------=_NextPart_000_002A_01C55091.52C77310 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Thanks for the quick response Tom. Here is some sample code that illustrates what I am trying to do. Basically, I have an array of floating point values that I want to store in a bytea field. To do this, I cast the float * to unsigned char *, run the unsigned char *'s through PQescapeBytea, and then insert the record. To extract it back out, I retrieve the record, run it through PQunescapeBytea, and then cast it back to float. The array of floats is just an example. In practice, it would just be a pointer some arbitrary data. Thanks, Michael Frost Computer Sciences Corporation Phone: 831-656-4723 Fax: 831-656-4769 Email: frost@nrlmry.navy.mil //////////////////////////////////////////////////////////////////////// // #include "postgres.h" #include exec sql include sqlca; int main ( int argc, char **argv ) { void insertData ( ); void getData ( ); printf ( "Calling insertData\n" ); insertData ( ); printf ( "Calling getData\n" ); getData ( ); } void getData ( ) { int newSize = 0; float *prsData = 0; exec sql begin declare section; char myData[2000]; int nGridID = 0; int id = 1; char *pUnescapedData = 0; exec sql end declare section; exec sql connect to mike; memset ( myData, 0, 2000 ); exec sql select gridid, thedata into :nGridID, :myData from grids where gridid = :id; pUnescapedData = PQunescapeBytea ( myData, &newSize ); prsData = ( float * ) pUnescapedData; exec sql disconnect; } void insertData ( ) { float *pData = 0; int i = 0; unsigned char *pTemp = 0; int newSize = 0; int size = 40; exec sql begin declare section; unsigned char *pEncodeData = 0, *myData = 0; int nGridID = 1; exec sql end declare section; exec sql connect to mike; pData = ( float * ) calloc ( 1, 10 * sizeof ( float ) ); for ( i = 0; i < 10; i++ ) pData[i] = ( float ) i; pTemp = ( unsigned char * ) calloc ( 1, 10 * sizeof ( float ) + 4 ); memcpy ( &pTemp[1], pData, 10 * sizeof ( float ) ); memcpy ( pTemp, &size, 4 ); pEncodeData = PQescapeBytea ( pTemp, 10 * sizeof ( float ) + 4, &newSize ); myData = ( unsigned char * ) calloc ( 1, newSize ); memcpy ( myData, pEncodeData, newSize ); exec sql delete from grids; exec sql insert into grids ( gridid, thedata ) values ( :nGridID, :myData ); exec sql disconnect; } /* Table "public.grids" Column | Type | Modifiers ---------+---------+----------- gridid | integer | thedata | bytea | */ /////////////////////////////////////////////////////////////////////// -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Wednesday, May 04, 2005 7:30 AM To: Frost, Mr. Michael (contractor) Cc: pgsql-interfaces@postgresql.org Subject: Re: [INTERFACES] PQescapeBytea & PQunescapeBytea "Frost, Mr. Michael (contractor)" writes: > [ PQescapeBytea & PQunescapeBytea are not inverses ] They're not supposed to be. PQescapeBytea creates something that can be inserted into a SQL command as a string literal. PQunescapeBytea deconstructs something that has been returned as a SELECT result. There are two different levels of backslashing involved. This doesn't directly answer your original problem, but I think we'll need to see more of your code to figure out what you were doing wrong. regards, tom lane ------=_NextPart_000_002A_01C55091.52C77310 Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIM8jCCBBsw ggOEoAMCAQICARMwDQYJKoZIhvcNAQEFBQAwYTELMAkGA1UEBhMCVVMxGDAWBgNVBAoTD1UuUy4g R292ZXJubWVudDEMMAoGA1UECxMDRG9EMQwwCgYDVQQLEwNQS0kxHDAaBgNVBAMTE0RvRCBDTEFT UyAzIFJvb3QgQ0EwHhcNMDAwODExMTc0NTI5WhcNMDYwODEwMTc0NTI5WjBkMQswCQYDVQQGEwJV UzEYMBYGA1UEChMPVS5TLiBHb3Zlcm5tZW50MQwwCgYDVQQLEwNEb0QxDDAKBgNVBAsTA1BLSTEf MB0GA1UEAxMWRE9EIENMQVNTIDMgRU1BSUwgQ0EtMzCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkC gYEA73y9yGvr8S43hXDaHWi/OIgHM6aXzwsq2I1DEwU3TslXTgkVHaRm247GUJy82cvZ9e/2VEbi 16LCoGV23fp6RVj/UsORbrEN5y8edLgG4mFA+eYx2e4cXjtPArs79vG968Aqkp5LX+zDXC7Iok30 GLuN7MffqBj4J1P1XB8/hAkCAwEAAaOCAd4wggHaMB0GA1UdDgQWBBTsE1u8IYxmmwqLfwdfJbAU +RD1mzAOBgNVHQ8BAf8EBAMCAYYwDwYDVR0TAQH/BAUwAwEB/zAMBgNVHSQEBTADgAEAMB8GA1Ud IwQYMBaAFGycpfBcj21BjcQXO5BXwg+jzW3+MDAGA1UdIAQpMCcwCwYJYIZIAWUCAQsFMAsGCWCG SAFlAgELCTALBglghkgBZQIBCwowgYMGA1UdEgR8MHqGeGxkYXA6Ly9kcy0zLmMzcGtpLmNoYW1i LmRpc2EubWlsL2NuJTNkRG9EJTIwQ0xBU1MlMjAzJTIwUm9vdCUyMENBJTJjb3UlM2RQS0klMmNv dSUzZERvRCUyY28lM2RVLlMuJTIwR292ZXJubWVudCUyY2MlM2RVUzCBsAYDVR0fBIGoMIGlMIGi oIGfoIGchoGZbGRhcDovL2RzLTMuYzNwa2kuY2hhbWIuZGlzYS5taWwvY24lM2REb0QlMjBDTEFT UyUyMDMlMjBSb290JTIwQ0ElMmNvdSUzZFBLSSUyY291JTNkRG9EJTJjbyUzZFUuUy4lMjBHb3Zl cm5tZW50JTJjYyUzZFVTP2NlcnRpZmljYXRlcmV2b2NhdGlvbmxpc3Q7YmluYXJ5MA0GCSqGSIb3 DQEBBQUAA4GBACaljTU4dmv+9EEmyDEZRft2IawIavNgzULR+GsmScfgvsubrU23VtJnj+hseOVD EFDIPH2i8hIECL/9NVR3VQpt2o1bsieyjZ6eYr1t4b14rkzRl8R2PdLRTGYkCBFdKBWycTvHM/e5 nX+YnH76BLByl37808FUk2MUk9/xG8vgMIIEQDCCA6mgAwIBAgIDFWV6MA0GCSqGSIb3DQEBBQUA MGQxCzAJBgNVBAYTAlVTMRgwFgYDVQQKEw9VLlMuIEdvdmVybm1lbnQxDDAKBgNVBAsTA0RvRDEM MAoGA1UECxMDUEtJMR8wHQYDVQQDExZET0QgQ0xBU1MgMyBFTUFJTCBDQS0zMB4XDTAzMDYwOTAw MDAwMFoXDTA2MDYwODAwMDAwMFowgYExCzAJBgNVBAYTAlVTMRgwFgYDVQQKEw9VLlMuIEdvdmVy bm1lbnQxDDAKBgNVBAsTA0RvRDEMMAoGA1UECxMDUEtJMRMwEQYDVQQLEwpDT05UUkFDVE9SMScw JQYDVQQDEx5GUk9TVC5NSUNIQUVMLkRBVklELjExOTgzODQ5NDgwgZ8wDQYJKoZIhvcNAQEBBQAD gY0AMIGJAoGBAMyjbYxyiCnVfeNQ/u6cAxJLqMXQbr8nFjSp+xF0VT6bb9hKu5em4QosLQkpEXsO j//lQqzsZpzmiaos88jV1rb2obJCV2Syhqd/oPuE4YL8uatn2gp2PVXwfTVfGHMfeABuD/QMS1cq VGay0RB7d31m5XHugMy2X8mClO1l6RnTAgMBAAGjggHgMIIB3DAOBgNVHQ8BAf8EBAMCBSAwJAYD VR0RBB0wG4EZZnJvc3QuY3NjQG5ybG1yeS5uYXZ5Lm1pbDAfBgNVHSMEGDAWgBTsE1u8IYxmmwqL fwdfJbAU+RD1mzAdBgNVHQ4EFgQU+xrDudyhjyztOeEPzLvFgfAOMWYwFgYDVR0gBA8wDTALBglg hkgBZQIBCwkwgY8GA1UdEgSBhzCBhIaBgWxkYXA6Ly9lbWFpbC1kcy0zLmMzcGtpLmNoYW1iLmRp c2EubWlsL2NuJTNkRE9EJTIwQ0xBU1MlMjAzJTIwRU1BSUwlMjBDQS0zJTJjb3UlM2RQS0klMmNv dSUzZERvRCUyY28lM2RVLlMuJTIwR292ZXJubWVudCUyY2MlM2RVUzCBuQYDVR0fBIGxMIGuMIGr oIGooIGlhoGibGRhcDovL2VtYWlsLWRzLTMuYzNwa2kuY2hhbWIuZGlzYS5taWwvY24lM2RET0Ql MjBDTEFTUyUyMDMlMjBFTUFJTCUyMENBLTMlMmNvdSUzZFBLSSUyY291JTNkRG9EJTJjbyUzZFUu Uy4lMjBHb3Zlcm5tZW50JTJjYyUzZFVTP2NlcnRpZmljYXRlcmV2b2NhdGlvbmxpc3Q7YmluYXJ5 MA0GCSqGSIb3DQEBBQUAA4GBAJlQGMGXSR7RJ8nmXZj25TbtCh+YYToNI/6V8rZDJ2W3MICnny28 I0TwvxFiEY1G7TcProIaTTOSdXqgalTcE/iOImszHyvUFA1nw4zLOaCYS9yqF5TR72/sVv+4pIPb 1pnCsujyS6/hoHUtuFNBZ/x08gMxX631taKraOWA1JG7MIIEizCCA/SgAwIBAgIDFWV1MA0GCSqG SIb3DQEBBQUAMGQxCzAJBgNVBAYTAlVTMRgwFgYDVQQKEw9VLlMuIEdvdmVybm1lbnQxDDAKBgNV BAsTA0RvRDEMMAoGA1UECxMDUEtJMR8wHQYDVQQDExZET0QgQ0xBU1MgMyBFTUFJTCBDQS0zMB4X DTAzMDYwOTAwMDAwMFoXDTA2MDYwODAwMDAwMFowgYExCzAJBgNVBAYTAlVTMRgwFgYDVQQKEw9V LlMuIEdvdmVybm1lbnQxDDAKBgNVBAsTA0RvRDEMMAoGA1UECxMDUEtJMRMwEQYDVQQLEwpDT05U UkFDVE9SMScwJQYDVQQDEx5GUk9TVC5NSUNIQUVMLkRBVklELjExOTgzODQ5NDgwgZ8wDQYJKoZI hvcNAQEBBQADgY0AMIGJAoGBAJDhyeM/vz4OKgaU2A7GQXTRGIv0biyHBsyuC9YeW0o0MEIfT/oZ DJFEJRRogcnVF/GmYudix0NHuLS/XyQnVKaP4lmkbuXWRsMEbfCMSEHOiCnmIw4v9M49PjdD1x5I Z0j48U6ialENVTOnKuBfaLAomRliupQqajSmGnSba5OTAgMBAAGjggIrMIICJzAOBgNVHQ8BAf8E BAMCBsAwHwYDVR0jBBgwFoAU7BNbvCGMZpsKi38HXyWwFPkQ9ZswHQYDVR0OBBYEFJZ496wcsHpk u7ZHulSCACsOu3xvMBYGA1UdIAQPMA0wCwYJYIZIAWUCAQsJMIGPBgNVHRIEgYcwgYSGgYFsZGFw Oi8vZW1haWwtZHMtMy5jM3BraS5jaGFtYi5kaXNhLm1pbC9jbiUzZERPRCUyMENMQVNTJTIwMyUy MEVNQUlMJTIwQ0EtMyUyY291JTNkUEtJJTJjb3UlM2REb0QlMmNvJTNkVS5TLiUyMEdvdmVybm1l bnQlMmNjJTNkVVMwgbkGA1UdHwSBsTCBrjCBq6CBqKCBpYaBomxkYXA6Ly9lbWFpbC1kcy0zLmMz cGtpLmNoYW1iLmRpc2EubWlsL2NuJTNkRE9EJTIwQ0xBU1MlMjAzJTIwRU1BSUwlMjBDQS0zJTJj b3UlM2RQS0klMmNvdSUzZERvRCUyY28lM2RVLlMuJTIwR292ZXJubWVudCUyY2MlM2RVUz9jZXJ0 aWZpY2F0ZXJldm9jYXRpb25saXN0O2JpbmFyeTApBgNVHSUEIjAgBgorBgEEAYI3FAICBggrBgEF BQcDBAYIKwYBBQUHAwIwRAYDVR0RBD0wO4EZZnJvc3QuY3NjQG5ybG1yeS5uYXZ5Lm1pbKAeBgor BgEEAYI3FAIDoBAMDjExOTgzODQ5NDhAbWlsMA0GCSqGSIb3DQEBBQUAA4GBAA0TfZt/LsgUohxw v8DuXOcayOJ6gzjyRG01eqkUqLe0TKZYxjwpY11Y9Xn4/hrw+m5R9uznuiJQZy8YU5RKoxYYKeEc 4gCefqKOIyQePTJFVsD+Bti2yJFNp8EXvHg0MKXMvOBlV/QoJnx8t2gpIi14090xMZixlBMTD5cc JXbpMYICIzCCAh8CAQEwazBkMQswCQYDVQQGEwJVUzEYMBYGA1UEChMPVS5TLiBHb3Zlcm5tZW50 MQwwCgYDVQQLEwNEb0QxDDAKBgNVBAsTA1BLSTEfMB0GA1UEAxMWRE9EIENMQVNTIDMgRU1BSUwg Q0EtMwIDFWV1MAkGBSsOAwIaBQCgggEOMBgGCSqGSIb3DQEJAzELBgkqhkiG9w0BBwEwHAYJKoZI hvcNAQkFMQ8XDTA1MDUwNDE3MDkxM1owIwYJKoZIhvcNAQkEMRYEFF5XZBzWp/Vv5L5A/9sl3COy CkCeMDMGCSqGSIb3DQEJDzEmMCQwDQYIKoZIhvcNAwICASgwBwYFKw4DAhowCgYIKoZIhvcNAgUw egYJKwYBBAGCNxAEMW0wazBkMQswCQYDVQQGEwJVUzEYMBYGA1UEChMPVS5TLiBHb3Zlcm5tZW50 MQwwCgYDVQQLEwNEb0QxDDAKBgNVBAsTA1BLSTEfMB0GA1UEAxMWRE9EIENMQVNTIDMgRU1BSUwg Q0EtMwIDFWV6MA0GCSqGSIb3DQEBAQUABIGAiFpDfp7ZkjS6R2VVIvdHzbCTezdnW8La4labG9P4 WrLC4W7Ofs5Phfq/xpjUpA2LU3nMmetwqcSb4qlxYL0mAffyXRFwGGFTIYiRPxvhBJkQDMhviB/V mrb+bR8sDngaJodvsBJCxBcRkxdfKzK0WWVK278LmAyO9l8DUvKbN1wAAAAAAAA= ------=_NextPart_000_002A_01C55091.52C77310--