[comp.databases.informix] retrieving Stored procedure with SQL IN INFORMIX

On Fri, 27 Jun 2003 09:54:13 -0400, Lionel Girard wrote: Myschema does the same thing in ESQL/C and there are not spaces compressed out. The procedure text is EXACTLY as it was entered! Must be something that Delphi or your ODBC driver is doing. Art S. Kagel > 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

On Fri, 27 Jun 2003 12:54:16 -0400, Art S. Kagel wrote: Oh, wait, I know exactly what's happening. Delphi is stripping the trailing spaces from the sysprocbody.data column so that when it is pasted back together on output it's trashing the code. Got it, had the same problem in early myschema development when I used a host variable type of 'string' for the data field. An ESQL/C string type causes the library to strip trailing spaces. Had to change the host type for that field to type fixchar to retain the trailing spaces. Then you just have to strip the trailing spaces from after the final semi-colon (OH! BTW, the last sysprocbody.data record does not neccessarily contain the final semi-colon under certain conditions you may have to add one yourself to make a create script that will run later!) Don't know the Delphi equivalent of fixchar, but this is the problem, Delphi is stripping the trailing spaces for sure. Art S. Kagel > On Fri, 27 Jun 2003 09:54:13 -0400, Lionel Girard wrote: > > Myschema does the same thing in ESQL/C and there are not spaces > compressed out. The procedure text is EXACTLY as it was entered! Must > be something that Delphi or your ODBC driver is doing. > > Art S. Kagel > >> 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