Re: EXECUTE ... SELECT COUNT(*) ... INTO ?

Bryan Tonnet wrote: > > In <57vgr3$lvj@cybernet.cse.fau.edu> barton@aura.irm.fau.edu (system PRIVILEGED account) writes: > > >I've been having all kinds of fun today with trying to get the > >proper syntax for a PREPARE statement. > > >This is being coded in 4GL. > > >I'm creating a WHERE clause from parameters given in an HTML > >form. The resulting SELECT statement is a simple COUNT(*) of > >records. Problem is, how do I get the returned value for COUNT(*)? > > >The following is my best try: > > LET p_sql_stmt = "SELECT COUNT(*) FROM v3 WHERE ", p_where_clause > > PREPARE pre_count FROM p_sql_stmt > > EXECUTE pre_count INTO p_row_cnt > >|_________________________^ > >| > >| A grammatical error has been found on line 169, character 27. > >| The construct is not understandable in its context. > >| See error number -4373. > > >p_row_cnt has been declared as both INTEGER and CHAR(10) with the same > >results. > > It's good isn't it. I've had the same problem and ended up just declaring > a cursor and being done with it. > > LET p_sql_stmt = "SELECT COUNT(*) FROM v3 WHERE ", p_where_clause > PREPARE pre_count FROM p_sql_stmt > DECLARE a_curs CURSOR FOR pre_count > OPEN a_curs > FETCH a_curs INTO p_row_cnt > CLOSE a_curs > > Ugly, but functional. What do you mean ugly? That is exactly what DECLAREing a CURSOR was designed to do; return values from either a hard-coded SELECT or a PREPAREd one. The EXECUTE command has never returned values. The variable p_row_cnt should be defined as a numeric type because you are returning a numeric result. So INTEGER sounds good to me. Cheers, -- Mark. +-------------------------------------------------------------------------+ |Mark D. Stock - The West Solutions Group http://www.west.co.za | | The Informix FAQ is at http://www.iiug.org | |mailto:marks@west.co.za +------------------------------------------------+ |Tel: +27 11 803 2151 |If it doesn't work... force it! | |Fax: +27 11 803 2189 |If it breaks... it needed replacing anyway! | |Cell: +27 83 250 2325 |Well, that's how I code anyway! | +------------------------+------------------------------------------------+