We use row producing procedures from a web front end and we have a work round for the missing column names.
We make the first column returned from the procedure always contain a list of the real column names. We then use this in our front end programs to rename the columns returned. e.g. the returned query may have 3 data columns that are firstname lastname postcode these are returned by ingres as result column 0 result column 1 result column 2 we will cause the dbproc to also return a fixed column 1 which contains "firstname,lastname,postcode" as a fixed value So now we get 4 columns result column 0 result column 1 result column 2 result column 3 we know that result column 0 will contain a list of the names of the other columns. We have a standard function which renames the columns so that we have almost forgotten about the problem! It is a pain though and the sooner it is fixed the better! Michael Traher -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Nanda, Sambit K Sent: 31 March 2005 10:46 To: Javed Khan Dalwai Cc: [email protected] Subject: RE: [Users] Stored Procedure - Row Producing Hello Javed, To best of my knowledge for using session temp table in RPP you need to do some thing like this execute procedure proc (parm1 = session.t1); You need to pass the session temp table as parameter to a RPP for using it within the RPP. However there is are few limitation to this like the session temp table should be the only parameter when called and returned. The Ingres SQL reference guide could give you more info on this. In case this does not work to you requirements you could most certainly use normal table to inserting and sorting within a procedure. This should not be a show-stopper for you. For your other question related to the column names in return row clause. Your suggestion is certainly a good way of doing things. However it is planned to add support for dynamic row returning procedures which could allow you go for a describe on the row returning procedures which in turn will give you column names and datatype information of the rows returned from the procedure. But it will be a while before that project is completed. As an alternative to this you could Describe the tables used in the procedure into sqlda structure and read the sqlvar->sqlname element to find the column name information which could be displayed to client applications. Certainly this is not the most elegant way of doing things but this could get you the required results. Hope this helps!!!!! Sambit Kumar Nanda Computer Associates Software Engineer, Development tel:+91-40 5567 0409 fax:+91-40 55670001 [EMAIL PROTECTED] -----Original Message----- From: Javed Khan Dalwai [mailto:[EMAIL PROTECTED] Sent: Thursday, March 31, 2005 1:26 PM To: Nanda, Sambit K Subject: RE: [Users] Stored Procedure - Row Producing Importance: High Hello Sambit, Thank you for your email. I was able to port most of my MSSQL database to Ingres without any real issues. When I started testing SP's from VFP-ODBC client, I reached a dead end and unfortunately I had to stop the migration. As at today, it is unfortunate that Ingres row producing SP doesn't return fields names, I think if we have to use Ingres, we should not use row producing SP at all!!! How about adding feature like: Create procedure rowproc ... as ... result row (char(10) as name, varchar(50) as surname , char(3) as age) ... begin ... Does it make sense? Also, I faced problems with creating temporary tables in a SP. sample MSSQL SP: CREATE PROCEDURE procname ... AS ... SELECT * INTO #t1 FROM myTable WHERE .... SELECT #t1....., otherTable..... FROM otherTable INNER JOIN #t1 ON .... Basically, I couldn't do something like this in Ingres! The temporary table #t1 is needed for intermediate processing. Any thoughts? Thank you again. Best Regards, Javed > -----Original Message----- > From: Nanda, Sambit K [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 29, 2005 10:54 AM > To: [email protected] > Cc: Javed Khan Dalwai > Subject: RE: [Users] Stored Procedure - Row Producing > > > > Hello Javed > > If I understand your question correctly then you are looking > for method > to get back the > column names after execution of a row returning procedure. > > To best of my knowledge a "using names" clause in the execute > procedure > could get you the column names however I believe "using > names" clause is > still not supported > for row returning procedure. > > Ideally in the case of normal dynamic sql statements you > could describe > your select statement and during the same you could mention "using > names" which you get you back the column names. > Some thing like this > Exec sql describe :sql_statement into sqlda using names. > > But since Ingres does not support a describe on row returning > procedure. > You cannot get back the column names as a part of the return row > statement. > > Hope this helps!! > > Sambit Kumar Nanda > Computer Associates > Software Engineer, Development > tel:+91-40 5567 0409 > fax:+91-40 55670001 > [EMAIL PROTECTED] > > > > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Javed Khan Dalwai > Sent: Monday, March 28, 2005 6:01 PM > To: [email protected] > Subject: [Users] Stored Procedure - Row Producing > Importance: High > > Hi All, > > I have following questions about stored procedures in Ingres. > 1. How can I get field names for the return row from row producing > stored procedure? > For example, lets say you have a stored procedure which > returns multiple > rows with 'result row(...)' and 'return row(...)' statements. > Sample: > Create procedure rowproc ... as > ... result row (char(10), varchar(50), char(3)) ... > begin > ... > for > select userid, username, dept > into :a, :b, :c > from personnel > ... > return row (:a, :b, :c); > endfor; > ... > end; > > My client is Visual Foxpro 8.0 (VFP) and I Ingres ODBC on Windows XP > machine. When I call this procedure from VFP-ODBC-Ingres, the > resulting > table fields (in VFP) I get are as follows: > result_element0, result_element1, result_element2 > Since, I refer to the result columns with their names, I > would like the > name of the return fields to be userid, username, dept. > > 2. Is stored procedure parameter value case-sensitive? Can we change > this behavior through environment vars? > > > Thanks in advance. > > Best Regards, > Javed Dalwai, > > _______________________________________________ > Users mailing list > [email protected] > http://ingres.ca.com/mailman/listinfo/users > > _______________________________________________ Users mailing list [email protected] http://ingres.ca.com/mailman/listinfo/users _______________________________________________ Users mailing list [email protected] http://ingres.ca.com/mailman/listinfo/users
