Thread: ODBC: Declare/Fetch & SELECT INTO
Hi, A recent problem with an installation of pgAdmin in which a SELECT INTO query failed has been caused by the 'Use Declare/Fetch' option in the ODBC driver being switched on (this has now been proven in 3 seperate cases). I'm assuming that declare/fetch should not cause this behaviour. The following is from the commlog - the table is not created, and no error is raised: conn=52758652, query='BEGIN' conn=52758652, query='declare SQL_CUR0325413C cursor for SELECT * INTO pgadmin_desc FROM pg_description WHERE objoid > 18655' conn=52758652, query='fetch 100 in SQL_CUR0325413C' [ fetched 13 rows ] conn=52758652, query='declare SQL_CUR03288FE8 cursor for select u.usename, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules from pg_user u, pg_class c, pg_attribute a, pg_type t where u.usesysid = c.relowner and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0) and c.relname like 'pg_description' order by attnum' conn=52758652, query='fetch 100 in SQL_CUR03288FE8' [ fetched 2 rows ] SQLColumns: table='pg_description',field_name='objoid',type=26,sqltype=268501632,name='o id' SQLColumns: table='pg_description',field_name='description',type=25,sqltype=268501632,na me='text' conn=52758652, query='close SQL_CUR03288FE8' conn=52758652, query='close SQL_CUR0325413C' conn=52758652, query='END' Regards, Dave. -- Disclaimer: the above is the author's personal opinion and is not the opinion or policy of his employer or of the little green men that have been following him all day. http://www.vale-housing.co.uk/ - http://www.pgadmin.freeserve.co.uk/
Dave Page <dpage@vale-housing.co.uk> writes:
> The following is from the commlog - the table is not created, and no error
> is raised:
> conn=52758652, query='declare SQL_CUR0325413C cursor for SELECT * INTO
> pgadmin_desc FROM pg_description WHERE objoid > 18655'
Hmm. I'd say that that should raise an error, since you are giving the
backend conflicting instructions about where to deliver the selected
tuples (through the cursor, or into a new table). I think right now
the code simply ignores the INTO clause when the context is a cursor
declaration, but giving an error if both are specified seems more
appropriate.
However, that might not help you very much :-(. I take it you are
just issuing a SELECT INTO command yourself, and ODBC is tacking on
the DECLARE CURSOR part? Seems ODBC needs to know not to do that
for a SELECT INTO.
regards, tom lane
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: 31 August 2000 21:47 > To: Dave Page > Cc: 'pgsql-interfaces@postgresql.org' > Subject: Re: [INTERFACES] ODBC: Declare/Fetch & SELECT INTO > > > Dave Page <dpage@vale-housing.co.uk> writes: > > The following is from the commlog - the table is not > created, and no error > > is raised: > > conn=52758652, query='declare SQL_CUR0325413C cursor for > SELECT * INTO > > pgadmin_desc FROM pg_description WHERE objoid > 18655' > > Hmm. I'd say that that should raise an error, since you are > giving the > backend conflicting instructions about where to deliver the selected > tuples (through the cursor, or into a new table). I think right now > the code simply ignores the INTO clause when the context is a cursor > declaration, but giving an error if both are specified seems more > appropriate. > > However, that might not help you very much :-(. I take it you are > just issuing a SELECT INTO command yourself, and ODBC is tacking on > the DECLARE CURSOR part? Seems ODBC needs to know not to do that > for a SELECT INTO. Yeah, that's exactly what I'm doing. My understanding was that that's what the Declare/Fetch option does - ie. forces the use of server side cursors for selects (but not 'select into' if all was well), presumably useful for handling particuarly large resultsets. One for someone familiar with the ODBC driver code methinks :-) Regards, Dave. -- Disclaimer: the above is the author's personal opinion and is not the opinion or policy of his employer or of the little green men that have been following him all day. http://www.vale-housing.co.uk/ - http://www.pgadmin.freeserve.co.uk/