Re: retrieving Stored procedure with SQL IN INFORMIX

Informix client knows the way text of the procedure is broken and stored and that is why dbaccess/dbexport are able to generate full procbody by using the information in sysprocbody. It can be done via any client application by just adding some logic for buffering one row at a time and then use those intermediate buffers to generate proceduer body by adding spaces or "\\n" as required. You will need to write a lot of code for checking various conditions if you are going to support GLS. Otherwise, it should be straight forward logic. Or you can use dbschema with "-f procname" option to generate this for you. Thanks and Regards, Gaurav lionel.girard@stv a.com (Lionel To: informix-list@iiug.org Girard) cc: Sent by: Subject: retrieving Stored procedure with SQL IN INFORMIX owner-informix-li st@iiug.org 06/27/03 08:54 AM Please respond to lionel.girard Hi, i'm currently working on a Procedure extractor from delphi/BDE plateform , and i do it with this request passed to INFORMIX : strQuery:='SELECT sysprocedures.procname, sysprocedures.procid, ' + 'sysprocbody.datakey, sysprocbody.data, sysprocbody.seqno ' + 'FROM sysprocedures, sysprocbody ' + 'WHERE sysprocedures.procname=''' + txtProcStoc.Text + ''' ' + 'AND sysprocedures.procid=sysprocbody.procid ' + 'AND sysprocbody.datakey=''T'' ' + 'ORDER BY sysprocbody.seqno ASC;'; This give me the entire code of procedures, but there is a bug sometimes. When sysprocbody.data ends or begin with a space, it forget it in tables, so i can't get it back when i extract the procedure. For example, a procedure as a line like this : DEFINE myvar CHAR(2); and it is segmented like this in table sysprocbody ..........DEFINE|myvar................ The space is lost in text but INFORMIX seems to keep it somewhere because it can restore it with DBAccess. How can i find it and where? Do you have an idea? Really thanks for your help sending to informix-list

HI, thanks for the response. What i want to do is extracting procedure without using UNIX Client, directly from my application. Is there a way or tool to do it without UNIX executables? Where can i find the way to correctly extract those procedures? I mean, the rules that are used to store and unstore them by db tools without missing any space between segments? Thanks again for your help Fnu Gaurav <fnug@us.ibm.com> wrote in message news:<bdhtut$lb3$1@terabinaries.xmission.com>... > Informix client knows the way text of the procedure is broken and stored > and that is why dbaccess/dbexport are able to generate full procbody by > using the information in sysprocbody. > > It can be done via any client application by just adding some logic for > buffering one row at a time and then use those intermediate buffers to > generate proceduer body by adding spaces or "\\n" as required. You will need > to write a lot of code for checking various conditions if you are going to > support GLS. Otherwise, it should be straight forward logic. > > Or you can use dbschema with "-f procname" option to generate this for you. > > Thanks and Regards, > Gaurav > > > > lionel.girard@stv > a.com (Lionel To: informix-list@iiug.org > Girard) cc: > Sent by: Subject: retrieving Stored procedure with SQL IN INFORMIX > owner-informix-li > st@iiug.org > > > 06/27/03 08:54 AM > Please respond to > lionel.girard > > > > > > > Hi, i'm currently working on a Procedure extractor from delphi/BDE > plateform , and i do it with this request passed to INFORMIX : > > strQuery:='SELECT sysprocedures.procname, sysprocedures.procid, ' + > 'sysprocbody.datakey, sysprocbody.data, sysprocbody.seqno ' + > 'FROM sysprocedures, sysprocbody ' + 'WHERE sysprocedures.procname=''' > + txtProcStoc.Text + ''' ' + 'AND > sysprocedures.procid=sysprocbody.procid ' + > 'AND sysprocbody.datakey=''T'' ' + 'ORDER BY sysprocbody.seqno ASC;'; > > This give me the entire code of procedures, but there is a bug > sometimes. When sysprocbody.data ends or begin with a space, it forget > it in tables, so i can't get it back when i extract the procedure. > > For example, a procedure as a line like this : > DEFINE myvar CHAR(2); > and it is segmented like this in table sysprocbody > ..........DEFINE|myvar................ > The space is lost in text but INFORMIX seems to keep it somewhere > because it can restore it with DBAccess. How can i find it and where? > Do you have an idea? > > Really thanks for your help > > > > sending to informix-list