Re: single row select in 4gl

On Sat, Jul 17, 1999 at 08:17:48AM +0200, Andras Simon wrote: > > How do I SELECT a single row using a user supplied search criterion > into a host variable in 4gl? > > It seems that I need to PREPARE the select statement (because of the > variable part) and I thought I could use EXECUTE .. INTO .. to fetch > the result. But the compiler disagrees :-( > > DATABASE mydbase > > main > define stmnt char(100) > define crit char(30) > define this like tbl.name > > let crit = "rowid=1" -- so the result will be unique I think with this you will get no rows (most likely). > > let stmnt = "select name from tbl where ", crit > prepare p from stmnt > execute p into this > |_____________^ > | > | A grammatical error has been found on line 12, character 15. > | The construct is not understandable in its context. > | See error number -4373. > > You have to declare a cursor on this statement and then make a foreach on it. i.e.: DECLARE q CURSOR FOR p FOREACH q INTO this END FOREACH or DECLARE q CURSOR FOR p OPEN q FETCH q INTO this CLOSE q Of course you should check the sqlca.sqlcode for NOTFOUND after FETCH, and you can wrap the fetch into a while... > I would appreciate any help! > > Andras > > ps. I can do this with a cursor, but that seems to be an overkill when > I know I'll get at most one row. No way without a cursor. Best Regards, Octav -- Octav Chiriac Phone: (373) 2 22 99 67 NetInfo S.R.L. Fax: (373) 2 21 36 59 Chisinau (373) 2 22 84 88 Moldova, Republic of mailto:com@netinfo-moldova.com

Octav Chiriac <com@netinfo-moldova.com> writes: > On Sat, Jul 17, 1999 at 08:17:48AM +0200, Andras Simon wrote: [...] >> let crit = "rowid=1" -- so the result will be unique >I think with this you will get no rows (most likely). Just wanted to present the problem as simply as possible. > You have to declare a cursor on this statement and then make a foreach on it. I will, if I have to... :-( What I find a little discomforting about this is that I see nothing in the docs about any restriction on EXECUTE .. INTO .. that would apply here (e.g. EXECUTE .. USING .. works). Am I missing something about EXECUTE? Thanks for your help! Andras

In article <vcdbtdbv6qe.fsf@russell.math.bme.hu>, Andras Simon <asimon@math.bme.hu> writes >Octav Chiriac <com@netinfo-moldova.com> writes: > >> On Sat, Jul 17, 1999 at 08:17:48AM +0200, Andras Simon wrote: >[...] >>> let crit = "rowid=1" -- so the result will be unique > >>I think with this you will get no rows (most likely). > >Just wanted to present the problem as simply as possible. > >> You have to declare a cursor on this statement and then make a foreach on it. > >I will, if I have to... :-( > >What I find a little discomforting about this is that I see nothing in >the docs about any restriction on EXECUTE .. INTO .. that would apply >here (e.g. EXECUTE .. USING .. works). Am I missing something about >EXECUTE? I had the same problem about a year ago. The manual said it was possible, but every attempt I tried ended up with a compilation error. We contacted Informix and got the response "You are correct, it doesn't work. The documentation is wrong, we'll get it changed" It would be nice if EXECUTE INTO worked, however DECLARE, OPEN, FETCH will just have to do :-) -- Steve Wright

Hi! How about SELECT ..... INTO ..... FROM Michael Andras Simon wrote: > Octav Chiriac <com@netinfo-moldova.com> writes: > > > On Sat, Jul 17, 1999 at 08:17:48AM +0200, Andras Simon wrote: > [...] > >> let crit = "rowid=1" -- so the result will be unique > > >I think with this you will get no rows (most likely). > > Just wanted to present the problem as simply as possible. > > > You have to declare a cursor on this statement and then make a foreach on it. > > I will, if I have to... :-( > > What I find a little discomforting about this is that I see nothing in > the docs about any restriction on EXECUTE .. INTO .. that would apply > here (e.g. EXECUTE .. USING .. works). Am I missing something about > EXECUTE? > > Thanks for your help! > > Andras

Michael Krzepkowski <michaelk@sqlcanada.com> writes: > Hi! > > How about SELECT ..... INTO ..... FROM That doesn't work because of the "variable" part (crit) of the select statement. :-( > > Michael Andras