INSERT INTO ... RETURNING id not behaving as expected with SQLNumResultCols

I'm using Postgres 8.4.1 and psqlodbc 08.04.0200. I'm trying to execute a statement INSERT INTO table/values RETURNING id to get the serial id. Problem is, SQLNumResultCols shows that there are zero columns. The statement I used works fine in pgadmin. This seems really basic and I am somewhat new to databases and odbc, but does anyone have a solution?

Thanks,
Ryan




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

Re: INSERT INTO ... RETURNING id not behaving as expectedwith SQLNumResultCols

This is a cryptographically signed message in MIME format.

--------------ms060100080307060704030300
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Ryan,

What language are you writing your app? What is the code you're using to =

capture the returning id?

Also, are you aware that you can fire a new SELECT statement after=20
you're done inserting? In scenarios where you wouldn't have much=20
concurrency that can be a valid approach.

And don't forget that the OID column is there for you to inspect. Ever=20
tried a SELECT OID FROM MyTable ORDER BY OID DESC LIMIT 1 ?

Gustavo

On 07/10/2010 01:04, Ryan Pfeiffer wrote:
> I'm using Postgres 8.4.1 and psqlodbc 08.04.0200. I'm trying to execute=
a statement INSERT INTO table/values RETURNING id to get the serial id. =
Problem is, SQLNumResultCols shows that there are zero columns. The state=
ment I used works fine in pgadmin. This seems really basic and I am somew=
hat new to databases and odbc, but does anyone have a solution?
>
> Thanks,
> Ryan
>
>
>
>


--------------ms060100080307060704030300
Content-Type: application/pkcs7-signature; name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="smime.p7s"
Content-Description: S/MIME Cryptographic Signature

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIIRWzCC
BN0wggPFoAMCAQICEHGS++YZX6xNEoV0cTSiGKcwDQYJKoZIhvcNAQEFBQAw ezELMAkGA1UE
BhMCR0IxGzAZBgNVBAgMEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UEBwwH U2FsZm9yZDEa
MBgGA1UECgwRQ29tb2RvIENBIExpbWl0ZWQxITAfBgNVBAMMGEFBQSBDZXJ0 aWZpY2F0ZSBT
ZXJ2aWNlczAeFw0wNDAxMDEwMDAwMDBaFw0yODEyMzEyMzU5NTlaMIGuMQsw CQYDVQQGEwJV
UzELMAkGA1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYD VQQKExVUaGUg
VVNFUlRSVVNUIE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRy dXN0LmNvbTE2
MDQGA1UEAxMtVVROLVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24g YW5kIEVtYWls
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAsjmFpPJ9q0E7YkY3 rs3BYHW8OWX5
ShpHornMSMxqmNVNNRm5pELlzkniii8efNIxB8dOtINknS4p1aJkxIW9hVE1 eaROaJB7HHqk
kqgX8pgV8pPMyaQylbsMTzC9mKALi+VuG6JG+ni8om+rWV6lL8/K2m2qL+us obNqqrcuZzWL
eeEeaYji5kbNoKXqvgvOdjp6Dpvq/NonWz1zHyLmSGHGTPNpsaguG7bUMSAs vIKKjqQOpdeJ
Q/wWWq8dcdcRWdq6hw2v+vPhwvCkxWeM1tZUOt4KpLoDd7NlyP0e03RiqhjK aJMeoYV+9Udl
y/hNVyh00jT/MLbu9mIwFIws6wIDAQABo4IBJzCCASMwHwYDVR0jBBgwFoAU oBEKIz6W8Qfs
4q8p74Klf9AwpLQwHQYDVR0OBBYEFImCZ33EnSZwAEu0UEh83j2uBG59MA4G A1UdDwEB/wQE
AwIBBjAPBgNVHRMBAf8EBTADAQH/MB0GA1UdJQQWMBQGCCsGAQUFBwMCBggr BgEFBQcDBDAR
BgNVHSAECjAIMAYGBFUdIAAwewYDVR0fBHQwcjA4oDagNIYyaHR0cDovL2Ny bC5jb21vZG9j
YS5jb20vQUFBQ2VydGlmaWNhdGVTZXJ2aWNlcy5jcmwwNqA0oDKGMGh0dHA6 Ly9jcmwuY29t
b2RvLm5ldC9BQUFDZXJ0aWZpY2F0ZVNlcnZpY2VzLmNybDARBglghkgBhvhC AQEEBAMCAQYw
DQYJKoZIhvcNAQEFBQADggEBAJ2Vyzy4fqUJxB6/C8LHdo45PJTGEKpPDMng q4RdiVTgZTvz
bRx8NywlVF+WIfw3hJGdFdwUT4HPVB1rbEVgxy35l1FM+WbKPKCCjKbI8OLp 1Er57D9Wyd12
jMOCAU9sAPMeGmF0BEcDqcZAV5G8ZSLFJ2dPV9tkWtmNH7qGL/QGrpxp7en0 zykX2OBKnxog
L5dMUbtGB8SKN04g4wkxaMeexIud6H4RvDJoEJYRmETYKlFgTYjrdDrfQwYy yDlWjDoRUtNB
pEMD9O3vMyfbOeAUTibJ2PU54om4k123KSZB6rObroP8d3XK6Mq1/uJlSmM+ RMTQw16Hc6mY
HK9/FX8wggY5MIIFIaADAgECAhB/1xcnPsIlgAYgb+FdNE7AMA0GCSqGSIb3 DQEBBQUAMIGu
MQswCQYDVQQGEwJVUzELMAkGA1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFr ZSBDaXR5MR4w
HAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6 Ly93d3cudXNl
cnRydXN0LmNvbTE2MDQGA1UEAxMtVVROLVVTRVJGaXJzdC1DbGllbnQgQXV0 aGVudGljYXRp
b24gYW5kIEVtYWlsMB4XDTEwMDExMjAwMDAwMFoXDTExMDExMjIzNTk1OVow geMxNTAzBgNV
BAsTLENvbW9kbyBUcnVzdCBOZXR3b3JrIC0gUEVSU09OQSBOT1QgVkFMSURB VEVEMUYwRAYD
VQQLEz1UZXJtcyBhbmQgQ29uZGl0aW9ucyBvZiB1c2U6IGh0dHA6Ly93d3cu Y29tb2RvLm5l
dC9yZXBvc2l0b3J5MR8wHQYDVQQLExYoYykyMDAzIENvbW9kbyBMaW1pdGVk MRgwFgYDVQQD
Ew9HdXN0YXZvIFBpbnNhcmQxJzAlBgkqhkiG9w0BCQEWGHBpbnNhcmRAcm9j a3NvbGlkLmNv
bS5icjCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAM0Xcozv7lrO imxtOszN2Vdt
Zy64Ck5wndx/w3q19vVZkSMc6Qs/NBEBRSPP7iohOdx+JR5n5JZosygjG/Pi pSKAwlRjbP5K
3t86BbsKuJmkW9lK3iw73dGEg0Sq/26Y567zinST+PtIrJVitxA4QTxxOfBL gr1EPrD2v7Ni
vzNxMIrfOFiO6uTDZh2RyXRJTjwzKsY0rMGyett67dYYRMVOTqc2IV+s2cIt b6Iy3v+J8xFn
AgjhyeIAlIpqBJgkt0NYiiTJIKFefK5rlBRoiaDG1ElrJOXAE+0LTGV+vY52 SqrccF5qYbq1
k1PSGlHXmDoeu604xSrSVtcJgcHns90CAwEAAaOCAhowggIWMB8GA1UdIwQY MBaAFImCZ33E
nSZwAEu0UEh83j2uBG59MB0GA1UdDgQWBBS4o8zX3QI6jO5IFRdE+U/OR2KZ gzAOBgNVHQ8B
Af8EBAMCBaAwDAYDVR0TAQH/BAIwADAgBgNVHSUEGTAXBggrBgEFBQcDBAYL KwYBBAGyMQED
BQIwEQYJYIZIAYb4QgEBBAQDAgUgMEYGA1UdIAQ/MD0wOwYMKwYBBAGyMQEC AQEBMCswKQYI
KwYBBQUHAgEWHWh0dHBzOi8vc2VjdXJlLmNvbW9kby5uZXQvQ1BTMIGlBgNV HR8EgZ0wgZow
TKBKoEiGRmh0dHA6Ly9jcmwuY29tb2RvY2EuY29tL1VUTi1VU0VSRmlyc3Qt Q2xpZW50QXV0
aGVudGljYXRpb25hbmRFbWFpbC5jcmwwSqBIoEaGRGh0dHA6Ly9jcmwuY29t b2RvLm5ldC9V
VE4tVVNFUkZpcnN0LUNsaWVudEF1dGhlbnRpY2F0aW9uYW5kRW1haWwuY3Js MGwGCCsGAQUF
BwEBBGAwXjA2BggrBgEFBQcwAoYqaHR0cDovL2NydC5jb21vZG9jYS5jb20v VVROQUFBQ2xp
ZW50Q0EuY3J0MCQGCCsGAQUFBzABhhhodHRwOi8vb2NzcC5jb21vZG9jYS5j b20wIwYDVR0R
BBwwGoEYcGluc2FyZEByb2Nrc29saWQuY29tLmJyMA0GCSqGSIb3DQEBBQUA A4IBAQCdCVSd
BcAjK7U99hJ5P+UZ6sPlRuYwN6LeKPDBG7JU9Frvi9ahCSM+VS9vV4ChJUzo 5OdRAi40Cybw
3zjJ6ORchE13rNev4mQ1T+vSK6DSbqLbWMW07ytHTDMtj4lYXzlUUAhtZh05 AlvtNwnTKZnL
YxH3OY6hPfCvPPxh2iaMncvDXjNkLOQJe+un9WuEw1vePnvvtFxzRPegL4FG PcWiLUhumbrh
zHAJcoN4nMKe5KJetOSPTp8WcxwMueD9Wd6WRjeH9Wb2ZPRokgSaZP1cPoYC gKLbxyZ+Z3pO
PSY0H841l0Da+F9yimcHJxTWR4K5t6Psw0TDHgAi7fuyBgWNMIIGOTCCBSGg AwIBAgIQf9cX
Jz7CJYAGIG/hXTROwDANBgkqhkiG9w0BAQUFADCBrjELMAkGA1UEBhMCVVMx CzAJBgNVBAgT
AlVUMRcwFQYDVQQHEw5TYWx0IExha2UgQ2l0eTEeMBwGA1UEChMVVGhlIFVT RVJUUlVTVCBO
ZXR3b3JrMSEwHwYDVQQLExhodHRwOi8vd3d3LnVzZXJ0cnVzdC5jb20xNjA0 BgNVBAMTLVVU
Ti1VU0VSRmlyc3QtQ2xpZW50IEF1dGhlbnRpY2F0aW9uIGFuZCBFbWFpbDAe Fw0xMDAxMTIw
MDAwMDBaFw0xMTAxMTIyMzU5NTlaMIHjMTUwMwYDVQQLEyxDb21vZG8gVHJ1 c3QgTmV0d29y
ayAtIFBFUlNPTkEgTk9UIFZBTElEQVRFRDFGMEQGA1UECxM9VGVybXMgYW5k IENvbmRpdGlv
bnMgb2YgdXNlOiBodHRwOi8vd3d3LmNvbW9kby5uZXQvcmVwb3NpdG9yeTEf MB0GA1UECxMW
KGMpMjAwMyBDb21vZG8gTGltaXRlZDEYMBYGA1UEAxMPR3VzdGF2byBQaW5z YXJkMScwJQYJ
KoZIhvcNAQkBFhhwaW5zYXJkQHJvY2tzb2xpZC5jb20uYnIwggEiMA0GCSqG SIb3DQEBAQUA
A4IBDwAwggEKAoIBAQDNF3KM7+5azopsbTrMzdlXbWcuuApOcJ3cf8N6tfb1 WZEjHOkLPzQR
AUUjz+4qITncfiUeZ+SWaLMoIxvz4qUigMJUY2z+St7fOgW7CriZpFvZSt4s O93RhINEqv9u
mOeu84p0k/j7SKyVYrcQOEE8cTnwS4K9RD6w9r+zYr8zcTCK3zhYjurkw2Yd kcl0SU48MyrG
NKzBsnrbeu3WGETFTk6nNiFfrNnCLW+iMt7/ifMRZwII4cniAJSKagSYJLdD WIokySChXnyu
a5QUaImgxtRJayTlwBPtC0xlfr2Odkqq3HBeamG6tZNT0hpR15g6HrutOMUq 0lbXCYHB57Pd
AgMBAAGjggIaMIICFjAfBgNVHSMEGDAWgBSJgmd9xJ0mcABLtFBIfN49rgRu fTAdBgNVHQ4E
FgQUuKPM190COozuSBUXRPlPzkdimYMwDgYDVR0PAQH/BAQDAgWgMAwGA1Ud EwEB/wQCMAAw
IAYDVR0lBBkwFwYIKwYBBQUHAwQGCysGAQQBsjEBAwUCMBEGCWCGSAGG+EIB AQQEAwIFIDBG
BgNVHSAEPzA9MDsGDCsGAQQBsjEBAgEBATArMCkGCCsGAQUFBwIBFh1odHRw czovL3NlY3Vy
ZS5jb21vZG8ubmV0L0NQUzCBpQYDVR0fBIGdMIGaMEygSqBIhkZodHRwOi8v Y3JsLmNvbW9k
b2NhLmNvbS9VVE4tVVNFUkZpcnN0LUNsaWVudEF1dGhlbnRpY2F0aW9uYW5k RW1haWwuY3Js
MEqgSKBGhkRodHRwOi8vY3JsLmNvbW9kby5uZXQvVVROLVVTRVJGaXJzdC1D bGllbnRBdXRo
ZW50aWNhdGlvbmFuZEVtYWlsLmNybDBsBggrBgEFBQcBAQRgMF4wNgYIKwYB BQUHMAKGKmh0
dHA6Ly9jcnQuY29tb2RvY2EuY29tL1VUTkFBQUNsaWVudENBLmNydDAkBggr BgEFBQcwAYYY
aHR0cDovL29jc3AuY29tb2RvY2EuY29tMCMGA1UdEQQcMBqBGHBpbnNhcmRA cm9ja3NvbGlk
LmNvbS5icjANBgkqhkiG9w0BAQUFAAOCAQEAnQlUnQXAIyu1PfYSeT/lGerD 5UbmMDei3ijw
wRuyVPRa74vWoQkjPlUvb1eAoSVM6OTnUQIuNAsm8N84yejkXIRNd6zXr+Jk NU/r0iug0m6i
21jFtO8rR0wzLY+JWF85VFAIbWYdOQJb7TcJ0ymZy2MR9zmOoT3wrzz8Ydom jJ3Lw14zZCzk
CXvrp/VrhMNb3j5777Rcc0T3oC+BRj3Foi1Ibpm64cxwCXKDeJzCnuSiXrTk j06fFnMcDLng
/VnelkY3h/Vm9mT0aJIEmmT9XD6GAoCi28cmfmd6Tj0mNB/ONZdA2vhfcopn BycU1keCubej
7MNEwx4AIu37sgYFjTGCBF0wggRZAgEBMIHDMIGuMQswCQYDVQQGEwJVUzEL MAkGA1UECBMC
VVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNF UlRSVVNUIE5l
dHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQG A1UEAxMtVVRO
LVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsAhB/ 1xcnPsIlgAYg
b+FdNE7AMAkGBSsOAwIaBQCgggJuMBgGCSqGSIb3DQEJAzELBgkqhkiG9w0B BwEwHAYJKoZI
hvcNAQkFMQ8XDTEwMTAwNzExMTE1N1owIwYJKoZIhvcNAQkEMRYEFN/gdUGH CswNWaGLcoqN
QnLzzGmKMF8GCSqGSIb3DQEJDzFSMFAwCwYJYIZIAWUDBAECMAoGCCqGSIb3 DQMHMA4GCCqG
SIb3DQMCAgIAgDANBggqhkiG9w0DAgIBQDAHBgUrDgMCBzANBggqhkiG9w0D AgIBKDCB1AYJ
KwYBBAGCNxAEMYHGMIHDMIGuMQswCQYDVQQGEwJVUzELMAkGA1UECBMCVVQx FzAVBgNVBAcT
DlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdv cmsxITAfBgNV
BAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQGA1UEAxMtVVROLVVT RVJGaXJzdC1D
bGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsAhB/1xcnPsIlgAYgb+Fd NE7AMIHWBgsq
hkiG9w0BCRACCzGBxqCBwzCBrjELMAkGA1UEBhMCVVMxCzAJBgNVBAgTAlVU MRcwFQYDVQQH
Ew5TYWx0IExha2UgQ2l0eTEeMBwGA1UEChMVVGhlIFVTRVJUUlVTVCBOZXR3 b3JrMSEwHwYD
VQQLExhodHRwOi8vd3d3LnVzZXJ0cnVzdC5jb20xNjA0BgNVBAMTLVVUTi1V U0VSRmlyc3Qt
Q2xpZW50IEF1dGhlbnRpY2F0aW9uIGFuZCBFbWFpbAIQf9cXJz7CJYAGIG/h XTROwDANBgkq
hkiG9w0BAQEFAASCAQB5IHHXOczuzbcUvNCRmXuf+8+ddgkWukVAhkq6jL3+ CZrcmySjo1Oh
lJOqQXC5hpJ98nWk/mXVwWDQBimHi64dcfi1/0FjWQl+GXq2duh7qHBxb99u kQCMKhO1WX/d
0Bhcyret/7mRP7HrAIUrAAL56XSaw3B2x5vx3dFs9fUCh7jNNsiyvoWH4xHu BZ17L/Lg5ns4
UUCYK64RcTlWr9JNQz9OJAa6AEHHW6AgdlAX9tw9NabxTfH7WHN2zwF12iYa tjNHngCyxo0i
n24qWemx0iUYOZMqCZjUhaDUPbrcZb/wBgrFOBbEXAyywN7SV1HO+FPPp6po /7NNo78Ce/6N
AAAAAAAA
--------------ms060100080307060704030300--

Re: INSERT INTO ... RETURNING id not behaving as expected with SQLNumResultCols

I'm writing it in visual c++. I've been using DataLayer to interact with OD=
BC, here: http://www.codeproject.com/KB/database/DataLayer.aspx

The code in question is:

ret =3D SQLPrepare(m_hstmt, (SQLCHAR*)selectStatement, SQL_NTS);
if ( ( ret == SQL_SUCCESS ) || ( ret == SQL_SUCCESS_WITH_INFO ) )
{
SQLSMALLINT numberColumns;

// Get number of columns in the result set
ret =3D SQLNumResultCols(m_hstmt, &numberColumns);

&numberColumns comes up as 0. Can SQLPrepare not be used the way I want for=
INSERT INTO ... RETURNING?

And yes, I'm aware I could use another select statement, and concurrency wo=
uld *probably* not be an issue down the road, but it bothers me that it is =
possible I could get the wrong ID. I suppose I can just SELECT id WHERE val=
1 =3D x, val2 =3D y, and so on as there should not be any exact duplicates =
in my tables that I can think of at this point. Still early on in developme=
nt though.

Thanks,
Ryan

--- On Thu, 10/7/10, Gustavo Pinsard wrote:

> From: Gustavo Pinsard
> Subject: Re: [ODBC] INSERT INTO ... RETURNING id not behaving as expected=
with SQLNumResultCols
> To: pgsql-odbc@postgresql.org
> Date: Thursday, October 7, 2010, 6:11 AM
> Ryan,
>=20
> What language are you writing your app? What is the code
> you're using to=20
> capture the returning id?
>=20
> Also, are you aware that you can fire a new SELECT
> statement after=20
> you're done inserting?=A0 In scenarios where you
> wouldn't have much=20
> concurrency that can be a valid approach.
>=20
> And don't forget that the OID column is there for you to
> inspect. Ever=20
> tried a SELECT OID FROM MyTable ORDER BY OID DESC LIMIT 1
> ?
>=20
> Gustavo
>=20
> On 07/10/2010 01:04, Ryan Pfeiffer wrote:
> > I'm using Postgres 8.4.1 and psqlodbc 08.04.0200. I'm
> trying to execute a statement INSERT INTO table/values
> RETURNING id to get the serial id. Problem is,
> SQLNumResultCols shows that there are zero columns. The
> statement I used works fine in pgadmin. This seems really
> basic and I am somewhat new to databases and odbc, but does
> anyone have a solution?
> >
> > Thanks,
> > Ryan
> >
> >
> >
> >
>=20
>=20




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

Re: INSERT INTO ... RETURNING id not behaving as expectedwith SQLNumResultCols

This is a cryptographically signed message in MIME format.

--------------ms010701050001070302010403
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Ryan,

You seem to have a buffer problem. As you know you have to declare=20
which columns you want returned from the INSERT statement. Thus, you=20
need a compatible buffer to receive each column declared.

As you didn't inform how you're mounting your statement, nor the struct=20
to hold the results of it, I can't say for sure.

But here is an example of how I deal with INSERT RETURNING, in my=20
language of choice: Clarion.

PROGRAM

MAP
END

szDBConn CSTRING( 512 )
tblDummy FILE, DRIVER( 'ODBC' ), NAME( 'dummy' ), OWNER( szDBConn )
RECORD RECORD
id ULONG
END
END


! Clarion has roots in Cobol, and the following statement
! declares the executable part of the program
CODE

! Lets prepare the connection string
szDBConn =3D 'Driver=3D{{PostgreSQL ANSI};database=3Dtest;uid=3Dtest;p=
wd=3Dtest'

! Lets open the "file", which is in reality just a buffer pointing
! to a table in a server, according to the "OWNER" modifier
OPEN( tblDummy, 42h )

! Here I send the command I want - " & | " instructs to continue
! on the next line. Clarion doesn't use ";" as an end of statement
! marker, but the \n\r sequence, and when we need, well, you figured.
tblDummy{ PROP:SQL } =3D 'INSERT INTO customers ' & |
'VALUES ( 'GUSTAVO' ) ' & |
'RETURNING id'

! HERE IT IS - read whatever the server returned
NEXT( tblDummy )

! This is a MessageBox() alright.
MESSAGE( tblDummy.id )

! Ends program
RETURN 0

The above code holds a complete program that will connect to a server=20
(given teh connection strings is valid, off course), and issue an INSERT =

command against an existing "customers" table.

Now, how do I SEND the SQL command to de server? Using the transport=20
buffer I declared: tblDummy. This buffer reflects an existing table (or =

view) in the database. In this case, a table called "dummy".

In Clarion, when I issue a PROP:SQL command attached to a buffer, the=20
server responds back the informed buffer. Then, a simple NEXT() will=20
read the contents of the buffer, which is a struct at the end of the=20
day, and I'm all set.

This tip of having a dummy table or view declared may help you to=20
collect different types of information from the server, not always=20
related to data tables. You can, for that matter, ask the server who is =

logged, for how long, its version etc.

It is just a matter of having a compatible buffer to receive whatever=20
the server sends back in response to a command.

HTH

Gustavo


--------------ms010701050001070302010403
Content-Type: application/pkcs7-signature; name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="smime.p7s"
Content-Description: S/MIME Cryptographic Signature

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIIRWzCC
BN0wggPFoAMCAQICEHGS++YZX6xNEoV0cTSiGKcwDQYJKoZIhvcNAQEFBQAw ezELMAkGA1UE
BhMCR0IxGzAZBgNVBAgMEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UEBwwH U2FsZm9yZDEa
MBgGA1UECgwRQ29tb2RvIENBIExpbWl0ZWQxITAfBgNVBAMMGEFBQSBDZXJ0 aWZpY2F0ZSBT
ZXJ2aWNlczAeFw0wNDAxMDEwMDAwMDBaFw0yODEyMzEyMzU5NTlaMIGuMQsw CQYDVQQGEwJV
UzELMAkGA1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYD VQQKExVUaGUg
VVNFUlRSVVNUIE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRy dXN0LmNvbTE2
MDQGA1UEAxMtVVROLVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24g YW5kIEVtYWls
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAsjmFpPJ9q0E7YkY3 rs3BYHW8OWX5
ShpHornMSMxqmNVNNRm5pELlzkniii8efNIxB8dOtINknS4p1aJkxIW9hVE1 eaROaJB7HHqk
kqgX8pgV8pPMyaQylbsMTzC9mKALi+VuG6JG+ni8om+rWV6lL8/K2m2qL+us obNqqrcuZzWL
eeEeaYji5kbNoKXqvgvOdjp6Dpvq/NonWz1zHyLmSGHGTPNpsaguG7bUMSAs vIKKjqQOpdeJ
Q/wWWq8dcdcRWdq6hw2v+vPhwvCkxWeM1tZUOt4KpLoDd7NlyP0e03RiqhjK aJMeoYV+9Udl
y/hNVyh00jT/MLbu9mIwFIws6wIDAQABo4IBJzCCASMwHwYDVR0jBBgwFoAU oBEKIz6W8Qfs
4q8p74Klf9AwpLQwHQYDVR0OBBYEFImCZ33EnSZwAEu0UEh83j2uBG59MA4G A1UdDwEB/wQE
AwIBBjAPBgNVHRMBAf8EBTADAQH/MB0GA1UdJQQWMBQGCCsGAQUFBwMCBggr BgEFBQcDBDAR
BgNVHSAECjAIMAYGBFUdIAAwewYDVR0fBHQwcjA4oDagNIYyaHR0cDovL2Ny bC5jb21vZG9j
YS5jb20vQUFBQ2VydGlmaWNhdGVTZXJ2aWNlcy5jcmwwNqA0oDKGMGh0dHA6 Ly9jcmwuY29t
b2RvLm5ldC9BQUFDZXJ0aWZpY2F0ZVNlcnZpY2VzLmNybDARBglghkgBhvhC AQEEBAMCAQYw
DQYJKoZIhvcNAQEFBQADggEBAJ2Vyzy4fqUJxB6/C8LHdo45PJTGEKpPDMng q4RdiVTgZTvz
bRx8NywlVF+WIfw3hJGdFdwUT4HPVB1rbEVgxy35l1FM+WbKPKCCjKbI8OLp 1Er57D9Wyd12
jMOCAU9sAPMeGmF0BEcDqcZAV5G8ZSLFJ2dPV9tkWtmNH7qGL/QGrpxp7en0 zykX2OBKnxog
L5dMUbtGB8SKN04g4wkxaMeexIud6H4RvDJoEJYRmETYKlFgTYjrdDrfQwYy yDlWjDoRUtNB
pEMD9O3vMyfbOeAUTibJ2PU54om4k123KSZB6rObroP8d3XK6Mq1/uJlSmM+ RMTQw16Hc6mY
HK9/FX8wggY5MIIFIaADAgECAhB/1xcnPsIlgAYgb+FdNE7AMA0GCSqGSIb3 DQEBBQUAMIGu
MQswCQYDVQQGEwJVUzELMAkGA1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFr ZSBDaXR5MR4w
HAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6 Ly93d3cudXNl
cnRydXN0LmNvbTE2MDQGA1UEAxMtVVROLVVTRVJGaXJzdC1DbGllbnQgQXV0 aGVudGljYXRp
b24gYW5kIEVtYWlsMB4XDTEwMDExMjAwMDAwMFoXDTExMDExMjIzNTk1OVow geMxNTAzBgNV
BAsTLENvbW9kbyBUcnVzdCBOZXR3b3JrIC0gUEVSU09OQSBOT1QgVkFMSURB VEVEMUYwRAYD
VQQLEz1UZXJtcyBhbmQgQ29uZGl0aW9ucyBvZiB1c2U6IGh0dHA6Ly93d3cu Y29tb2RvLm5l
dC9yZXBvc2l0b3J5MR8wHQYDVQQLExYoYykyMDAzIENvbW9kbyBMaW1pdGVk MRgwFgYDVQQD
Ew9HdXN0YXZvIFBpbnNhcmQxJzAlBgkqhkiG9w0BCQEWGHBpbnNhcmRAcm9j a3NvbGlkLmNv
bS5icjCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAM0Xcozv7lrO imxtOszN2Vdt
Zy64Ck5wndx/w3q19vVZkSMc6Qs/NBEBRSPP7iohOdx+JR5n5JZosygjG/Pi pSKAwlRjbP5K
3t86BbsKuJmkW9lK3iw73dGEg0Sq/26Y567zinST+PtIrJVitxA4QTxxOfBL gr1EPrD2v7Ni
vzNxMIrfOFiO6uTDZh2RyXRJTjwzKsY0rMGyett67dYYRMVOTqc2IV+s2cIt b6Iy3v+J8xFn
AgjhyeIAlIpqBJgkt0NYiiTJIKFefK5rlBRoiaDG1ElrJOXAE+0LTGV+vY52 SqrccF5qYbq1
k1PSGlHXmDoeu604xSrSVtcJgcHns90CAwEAAaOCAhowggIWMB8GA1UdIwQY MBaAFImCZ33E
nSZwAEu0UEh83j2uBG59MB0GA1UdDgQWBBS4o8zX3QI6jO5IFRdE+U/OR2KZ gzAOBgNVHQ8B
Af8EBAMCBaAwDAYDVR0TAQH/BAIwADAgBgNVHSUEGTAXBggrBgEFBQcDBAYL KwYBBAGyMQED
BQIwEQYJYIZIAYb4QgEBBAQDAgUgMEYGA1UdIAQ/MD0wOwYMKwYBBAGyMQEC AQEBMCswKQYI
KwYBBQUHAgEWHWh0dHBzOi8vc2VjdXJlLmNvbW9kby5uZXQvQ1BTMIGlBgNV HR8EgZ0wgZow
TKBKoEiGRmh0dHA6Ly9jcmwuY29tb2RvY2EuY29tL1VUTi1VU0VSRmlyc3Qt Q2xpZW50QXV0
aGVudGljYXRpb25hbmRFbWFpbC5jcmwwSqBIoEaGRGh0dHA6Ly9jcmwuY29t b2RvLm5ldC9V
VE4tVVNFUkZpcnN0LUNsaWVudEF1dGhlbnRpY2F0aW9uYW5kRW1haWwuY3Js MGwGCCsGAQUF
BwEBBGAwXjA2BggrBgEFBQcwAoYqaHR0cDovL2NydC5jb21vZG9jYS5jb20v VVROQUFBQ2xp
ZW50Q0EuY3J0MCQGCCsGAQUFBzABhhhodHRwOi8vb2NzcC5jb21vZG9jYS5j b20wIwYDVR0R
BBwwGoEYcGluc2FyZEByb2Nrc29saWQuY29tLmJyMA0GCSqGSIb3DQEBBQUA A4IBAQCdCVSd
BcAjK7U99hJ5P+UZ6sPlRuYwN6LeKPDBG7JU9Frvi9ahCSM+VS9vV4ChJUzo 5OdRAi40Cybw
3zjJ6ORchE13rNev4mQ1T+vSK6DSbqLbWMW07ytHTDMtj4lYXzlUUAhtZh05 AlvtNwnTKZnL
YxH3OY6hPfCvPPxh2iaMncvDXjNkLOQJe+un9WuEw1vePnvvtFxzRPegL4FG PcWiLUhumbrh
zHAJcoN4nMKe5KJetOSPTp8WcxwMueD9Wd6WRjeH9Wb2ZPRokgSaZP1cPoYC gKLbxyZ+Z3pO
PSY0H841l0Da+F9yimcHJxTWR4K5t6Psw0TDHgAi7fuyBgWNMIIGOTCCBSGg AwIBAgIQf9cX
Jz7CJYAGIG/hXTROwDANBgkqhkiG9w0BAQUFADCBrjELMAkGA1UEBhMCVVMx CzAJBgNVBAgT
AlVUMRcwFQYDVQQHEw5TYWx0IExha2UgQ2l0eTEeMBwGA1UEChMVVGhlIFVT RVJUUlVTVCBO
ZXR3b3JrMSEwHwYDVQQLExhodHRwOi8vd3d3LnVzZXJ0cnVzdC5jb20xNjA0 BgNVBAMTLVVU
Ti1VU0VSRmlyc3QtQ2xpZW50IEF1dGhlbnRpY2F0aW9uIGFuZCBFbWFpbDAe Fw0xMDAxMTIw
MDAwMDBaFw0xMTAxMTIyMzU5NTlaMIHjMTUwMwYDVQQLEyxDb21vZG8gVHJ1 c3QgTmV0d29y
ayAtIFBFUlNPTkEgTk9UIFZBTElEQVRFRDFGMEQGA1UECxM9VGVybXMgYW5k IENvbmRpdGlv
bnMgb2YgdXNlOiBodHRwOi8vd3d3LmNvbW9kby5uZXQvcmVwb3NpdG9yeTEf MB0GA1UECxMW
KGMpMjAwMyBDb21vZG8gTGltaXRlZDEYMBYGA1UEAxMPR3VzdGF2byBQaW5z YXJkMScwJQYJ
KoZIhvcNAQkBFhhwaW5zYXJkQHJvY2tzb2xpZC5jb20uYnIwggEiMA0GCSqG SIb3DQEBAQUA
A4IBDwAwggEKAoIBAQDNF3KM7+5azopsbTrMzdlXbWcuuApOcJ3cf8N6tfb1 WZEjHOkLPzQR
AUUjz+4qITncfiUeZ+SWaLMoIxvz4qUigMJUY2z+St7fOgW7CriZpFvZSt4s O93RhINEqv9u
mOeu84p0k/j7SKyVYrcQOEE8cTnwS4K9RD6w9r+zYr8zcTCK3zhYjurkw2Yd kcl0SU48MyrG
NKzBsnrbeu3WGETFTk6nNiFfrNnCLW+iMt7/ifMRZwII4cniAJSKagSYJLdD WIokySChXnyu
a5QUaImgxtRJayTlwBPtC0xlfr2Odkqq3HBeamG6tZNT0hpR15g6HrutOMUq 0lbXCYHB57Pd
AgMBAAGjggIaMIICFjAfBgNVHSMEGDAWgBSJgmd9xJ0mcABLtFBIfN49rgRu fTAdBgNVHQ4E
FgQUuKPM190COozuSBUXRPlPzkdimYMwDgYDVR0PAQH/BAQDAgWgMAwGA1Ud EwEB/wQCMAAw
IAYDVR0lBBkwFwYIKwYBBQUHAwQGCysGAQQBsjEBAwUCMBEGCWCGSAGG+EIB AQQEAwIFIDBG
BgNVHSAEPzA9MDsGDCsGAQQBsjEBAgEBATArMCkGCCsGAQUFBwIBFh1odHRw czovL3NlY3Vy
ZS5jb21vZG8ubmV0L0NQUzCBpQYDVR0fBIGdMIGaMEygSqBIhkZodHRwOi8v Y3JsLmNvbW9k
b2NhLmNvbS9VVE4tVVNFUkZpcnN0LUNsaWVudEF1dGhlbnRpY2F0aW9uYW5k RW1haWwuY3Js
MEqgSKBGhkRodHRwOi8vY3JsLmNvbW9kby5uZXQvVVROLVVTRVJGaXJzdC1D bGllbnRBdXRo
ZW50aWNhdGlvbmFuZEVtYWlsLmNybDBsBggrBgEFBQcBAQRgMF4wNgYIKwYB BQUHMAKGKmh0
dHA6Ly9jcnQuY29tb2RvY2EuY29tL1VUTkFBQUNsaWVudENBLmNydDAkBggr BgEFBQcwAYYY
aHR0cDovL29jc3AuY29tb2RvY2EuY29tMCMGA1UdEQQcMBqBGHBpbnNhcmRA cm9ja3NvbGlk
LmNvbS5icjANBgkqhkiG9w0BAQUFAAOCAQEAnQlUnQXAIyu1PfYSeT/lGerD 5UbmMDei3ijw
wRuyVPRa74vWoQkjPlUvb1eAoSVM6OTnUQIuNAsm8N84yejkXIRNd6zXr+Jk NU/r0iug0m6i
21jFtO8rR0wzLY+JWF85VFAIbWYdOQJb7TcJ0ymZy2MR9zmOoT3wrzz8Ydom jJ3Lw14zZCzk
CXvrp/VrhMNb3j5777Rcc0T3oC+BRj3Foi1Ibpm64cxwCXKDeJzCnuSiXrTk j06fFnMcDLng
/VnelkY3h/Vm9mT0aJIEmmT9XD6GAoCi28cmfmd6Tj0mNB/ONZdA2vhfcopn BycU1keCubej
7MNEwx4AIu37sgYFjTGCBF0wggRZAgEBMIHDMIGuMQswCQYDVQQGEwJVUzEL MAkGA1UECBMC
VVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNF UlRSVVNUIE5l
dHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQG A1UEAxMtVVRO
LVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsAhB/ 1xcnPsIlgAYg
b+FdNE7AMAkGBSsOAwIaBQCgggJuMBgGCSqGSIb3DQEJAzELBgkqhkiG9w0B BwEwHAYJKoZI
hvcNAQkFMQ8XDTEwMTAwNzE4MzExM1owIwYJKoZIhvcNAQkEMRYEFFeGV9hl yeFa7SKuP+Mz
WRlydznTMF8GCSqGSIb3DQEJDzFSMFAwCwYJYIZIAWUDBAECMAoGCCqGSIb3 DQMHMA4GCCqG
SIb3DQMCAgIAgDANBggqhkiG9w0DAgIBQDAHBgUrDgMCBzANBggqhkiG9w0D AgIBKDCB1AYJ
KwYBBAGCNxAEMYHGMIHDMIGuMQswCQYDVQQGEwJVUzELMAkGA1UECBMCVVQx FzAVBgNVBAcT
DlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdv cmsxITAfBgNV
BAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQGA1UEAxMtVVROLVVT RVJGaXJzdC1D
bGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsAhB/1xcnPsIlgAYgb+Fd NE7AMIHWBgsq
hkiG9w0BCRACCzGBxqCBwzCBrjELMAkGA1UEBhMCVVMxCzAJBgNVBAgTAlVU MRcwFQYDVQQH
Ew5TYWx0IExha2UgQ2l0eTEeMBwGA1UEChMVVGhlIFVTRVJUUlVTVCBOZXR3 b3JrMSEwHwYD
VQQLExhodHRwOi8vd3d3LnVzZXJ0cnVzdC5jb20xNjA0BgNVBAMTLVVUTi1V U0VSRmlyc3Qt
Q2xpZW50IEF1dGhlbnRpY2F0aW9uIGFuZCBFbWFpbAIQf9cXJz7CJYAGIG/h XTROwDANBgkq
hkiG9w0BAQEFAASCAQB0Gry8IHd4Gfqych0OzYOixIVijtiw8INMjAIXt5dC T4VtoqyT/BIY
z4QukBedGqfHEWlhYjLpeEv/B6lySjO8ITdVTzTxAMIDWm/6TCp8+DQB2wTC 7Hlr1aIjsQCI
fPv5fisR+vu/GnGWk2WbLlni5PmFsF1H7JCJS4npfljOKW4XNb6UIquGX1MF Axg8n3LgZ5ee
F121jH+L8dKh5RtG+bqTJvpM6h/MRIaZ3knEtrOlezaGwRFzAmQOA6iJHJOm CJBb5/K47i9x
Mvy6uXSh5IUARKVjYzH7EJQtfFnJ5KG20S/bBn5hHIeIShLyYu4cOvrWXTnY fs4H+lzsOkAn
AAAAAAAA
--------------ms010701050001070302010403--

Re: INSERT INTO ... RETURNING id not behaving as expected with SQLNumResultCols

Ok, I believe I have found the problem.

http://msdn.microsoft.com/en-us/library/ms711684(v=3DVS.85). aspx

"The application can call SQLNumResultCols at any time after the statement =
is prepared or executed. However, because some data sources cannot easily d=
escribe the result sets that will be created by prepared statements, perfor=
mance will suffer if SQLNumResultCols is called after a statement is prepar=
ed but before it is executed."

I suppose that's what I get for slapping an existing library into my softwa=
re without completely understanding it. If I understand what I've read corr=
ectly, SQLPrepare should only be used if I plan on repeating the same state=
ment over and over with different variables.

Since this is a simple wrapper, it seems that an error may have been made. =
It calls SQLPrepare each time the wrapper classes are used, so even if it w=
as repeated many times, SQLPrepare is still being called each time which I =
think is not useful. Not sure on that though. At this point I moved up SQLE=
xecute and I got the results I expected.

--- On Thu, 10/7/10, Ryan Pfeiffer wrote:

> From: Ryan Pfeiffer
> Subject: Re: [ODBC] INSERT INTO ... RETURNING id not behaving as expected=
with SQLNumResultCols
> To: pgsql-odbc@postgresql.org
> Date: Thursday, October 7, 2010, 12:51 PM
> I'm writing it in visual c++. I've
> been using DataLayer to interact with ODBC, here: http://www.codeproject.=
com/KB/database/DataLayer.aspx
>=20
> The code in question is:
>=20
> ret =3D SQLPrepare(m_hstmt, (SQLCHAR*)selectStatement,
> SQL_NTS);
> if ( ( ret == SQL_SUCCESS ) || ( ret ==
> SQL_SUCCESS_WITH_INFO ) )
> {
>   =A0SQLSMALLINT numberColumns;
>=20
>   =A0// Get number of columns in the result
> set
>   =A0ret =3D SQLNumResultCols(m_hstmt,
> &numberColumns);
>=20
> &numberColumns comes up as 0. Can SQLPrepare not be
> used the way I want for INSERT INTO ... RETURNING?
>=20
> And yes, I'm aware I could use another select statement,
> and concurrency would *probably* not be an issue down the
> road, but it bothers me that it is possible I could get the
> wrong ID. I suppose I can just SELECT id WHERE val1 =3D x,
> val2 =3D y, and so on as there should not be any exact
> duplicates in my tables that I can think of at this point.
> Still early on in development though.
>=20
> Thanks,
> Ryan
>=20
> --- On Thu, 10/7/10, Gustavo Pinsard
> wrote:
>=20
> > From: Gustavo Pinsard
> > Subject: Re: [ODBC] INSERT INTO ... RETURNING id not
> behaving as expected with SQLNumResultCols
> > To: pgsql-odbc@postgresql.org
> > Date: Thursday, October 7, 2010, 6:11 AM
> > Ryan,
> >=20
> > What language are you writing your app? What is the
> code
> > you're using to=20
> > capture the returning id?
> >=20
> > Also, are you aware that you can fire a new SELECT
> > statement after=20
> > you're done inserting?=A0 In scenarios where you
> > wouldn't have much=20
> > concurrency that can be a valid approach.
> >=20
> > And don't forget that the OID column is there for you
> to
> > inspect. Ever=20
> > tried a SELECT OID FROM MyTable ORDER BY OID DESC
> LIMIT 1
> > ?
> >=20
> > Gustavo
> >=20
> > On 07/10/2010 01:04, Ryan Pfeiffer wrote:
> > > I'm using Postgres 8.4.1 and psqlodbc 08.04.0200.
> I'm
> > trying to execute a statement INSERT INTO
> table/values
> > RETURNING id to get the serial id. Problem is,
> > SQLNumResultCols shows that there are zero columns.
> The
> > statement I used works fine in pgadmin. This seems
> really
> > basic and I am somewhat new to databases and odbc, but
> does
> > anyone have a solution?
> > >
> > > Thanks,
> > > Ryan
> > >
> > >
> > >
> > >
> >=20
> >=20
>=20
>=20
>=20
>=20
> --=20
> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-odbc
>=20




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