Getting TEXT data in char.

Hi, Environment: IDS 11.50 on HP-UX 11. How can I get part of the TEXT datatype in char variable for string manipulation? I need to get the date out of "fragment expression on a table" from sysfragments table. But since the column exprtext which stores the fragment expression is TEXT type, I can not use SUBSTR or any functions on TEXT datatype. How can I extract date out of the fragment expression stored in TEXT datatype column? Thanks, Nandkishor

Try this link: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/co m.ibm.jdbc_pg.doc/jdbc114.htm part of this page says: When you select a TEXT column, you can choose to receive all or part of it. To retrieve it all, use the regular syntax for selecting a column. You can also select any part of a TEXT column by using subscripts, as this example shows: SELECT cat_descr [1,75] FROM catalog WHERE catalog_num = 10001 This statement reads the first 75 bytes of the cat_descr column associated with the catalog_num value 10001. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of NANDKISHOR SINGARE Sent: Tuesday, September 08, 2009 12:19 AM To: ids@iiug.org Subject: Getting TEXT data in char. [16888] Hi, Environment: IDS 11.50 on HP-UX 11. How can I get part of the TEXT datatype in char variable for string manipulation? I need to get the date out of "fragment expression on a table" from sysfragments table. But since the column exprtext which stores the fragment expression is TEXT type, I can not use SUBSTR or any functions on TEXT datatype. How can I extract date out of the fragment expression stored in TEXT datatype column? Thanks, Nandkishor ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

Nandkishor, You have to fetch the exprtext column into a locator (typedef loc_t) structure. You can do that several ways most simply by using a locator structure as the host 'variable' into which to fetch the column or by using a DESCRIPTOR or an sqlda structure with DESCRIBE. You then modify the locator structure to have the FETCH place the column's data where you want it. Here's a sample snippet from the myschema code. I've removed all of the error checking for clarity sake: -- I like to use the LOCUSER method and supply my own open, close, and write functions -- (see below) but you can use LOCMEMORY which is only a bit slower. typedef struct SYSFRAGMENTS_S { string fragtype[2]; long tabid; string indexname[IDBYTES]; string strategy[2]; long evalpos; loc_t exprtext; string dbspace[IDBYTES]; string partition[IDBYTES]; } sysfragments_t; ... snprintf( statement2, sizeof statement2, "SELECT fragtype, tabid, indexname, strategy, evalpos, " " exprtext, dbspace, %s " "FROM \\\\"informix\\\\".sysfragments " "WHERE tabid = ? AND fragtype = 'T' " "ORDER BY evalpos ", major_vers >= 10 ? "partition" : "' '" ); EXEC SQL PREPARE s_frags FROM :statement2; fragments.exprtext.loc_loctype = LOCUSER; fragments.exprtext.loc_buffer = (char *)malloc( 4096 ); fragments.exprtext.loc_bufsize = 4096; fragments.exprtext.loc_write = writeit; fragments.exprtext.loc_open = openit; fragments.exprtext.loc_close = closeit; tblobbuff.bbsize = 2048; tblobbuff.buff = (char *)malloc( tblobbuff.bbsize ); tblobbuff.global_length = 0; ... static off_t pos = 0; static int inblob = 0; int openit( loc_t *loc, int flags, int bsize ) { if (inblob == 1) { fprintf( stderr, "Open called for new blob without close.\\ " "length = %d.!\\ ", loc->loc_xfercount ); } loc->loc_status = 0; loc->loc_xfercount = 0L; inblob = 0; blobbuff->global_length = 0 ; pos = 0; /* Make sure blob buffer is large enough to hold BLOB. */ if (bsize > blobbuff->bbsize) { blobbuff->bbsize = bsize + (1024 - (bsize % 1024)); blobbuff->buff = (char *)realloc( blobbuff->buff, blobbuff->bbsize ); } memset( blobbuff->buff, 0, blobbuff->bbsize ); return 0; } int writeit( loc_t *loc, char *buf, int nbytes ) { if ((pos + nbytes) > blobbuff->bbsize) { blobbuff->bbsize += (2 * (nbytes < 1024 ? 1024 : nbytes)); blobbuff->buff = (char *)realloc( blobbuff->buff, blobbuff->bbsize ); } if (!inblob) inblob = 1; memcpy( &(blobbuff->buff[pos]), buf, nbytes ); pos += nbytes; loc->loc_xfercount += nbytes; blobbuff->global_length += nbytes; return 0; } int closeit( loc_t *loc ) { inblob = 0; loc->loc_status = 0; if (loc->loc_oflags & LOC_WONLY) { /* Fetching, cleanup. */ loc->loc_indicator = 0; loc->loc_size = loc->loc_xfercount; } if( blobbuff->global_length != loc->loc_xfercount) fprintf( stderr, "GLOBAL_LENGTH != loc->loc_xfercount <%d, %d> \\ ", blobbuff->global_length, loc->loc_xfercount ); pos = 0; return 0; } See the CSDK manual for details on using the locator structure. Since they use a couple of static variables to communicate between them, you would have to modify the openit, writeit, and closeit functions in order to fetch from multiple tables containing blob columns or to fetch from a table containing multiple blob columns, but for this application the code will work fine. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Sep 8, 2009 at 1:18 AM, NANDKISHOR SINGARE <ns_singare@yahoo.com>wrote: > Hi, > Environment: IDS 11.50 on HP-UX 11. > > How can I get part of the TEXT datatype in char variable for string > manipulation? I need to get the date out of "fragment expression on a > table" > from sysfragments table. But since the column exprtext which stores the > fragment expression is TEXT type, I can not use SUBSTR or any functions on > TEXT datatype. How can I extract date out of the fragment expression stored > in > TEXT datatype column? > > Thanks, > Nandkishor > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015174757a470d223047311f773

OH! forgot to mention a few things: - The structure tblobbuf in the main code and the pointer blobbuff in the openit, writeit, & closeit functions are defined as: typedef struct S_BLOBBUFF { char *buff; int bbsize, global_length; } t_blobbuff; And I copy a pointer to tblobbuff to the global variable blobbuff for the functions to use later in the code. - Once the row has been fetched, you would find the text of the exprtext column in the tblobbuff element buff. - The variable fragments is defined as a sysfragments structure if that wasn't clear. The full code is contained in the code for my dbschema replacement utility, myschema, which is included in the package utils2_ak. You can download the package from the Oninit web site (www.oninit.com/utils) or from the IIUG Software Repository (www.iiug.org/software). See the three files: myschema.eh, myschema.ec, & blobfuncs.ec There are also more generic blob fetching examples in several files in the demo/esqlc subdirectory of $INFORMIXDIR in your installation od the CSDK. Example: getcd_of.ec (Fetching blob data to a file using ...loc_loctype = LOCFILE Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Sep 8, 2009 at 10:41 AM, Art Kagel <art.kagel@gmail.com> wrote: > Nandkishor, > > You have to fetch the exprtext column into a locator (typedef loc_t) > structure. You can do that several ways most simply by using a locator > structure as the host 'variable' into which to fetch the column or by using > a DESCRIPTOR or an sqlda structure with DESCRIBE. You then modify the > locator structure to have the FETCH place the column's data where you want > it. Here's a sample snippet from the myschema code. I've removed all of > the error checking for clarity sake: > > -- I like to use the LOCUSER method and supply my own open, close, and > write functions > -- (see below) but you can use LOCMEMORY which is only a bit slower. > > typedef struct SYSFRAGMENTS_S { > string fragtype[2]; > long tabid; > string indexname[IDBYTES]; > string strategy[2]; > long evalpos; > loc_t exprtext; > string dbspace[IDBYTES]; > string partition[IDBYTES]; > } sysfragments_t; > > ... > snprintf( statement2, > sizeof statement2, > "SELECT fragtype, tabid, indexname, strategy, evalpos, " > " exprtext, dbspace, %s " > "FROM \\\\"informix\\\\".sysfragments " > "WHERE tabid = ? AND fragtype = 'T' " > "ORDER BY evalpos ", > major_vers >= 10 ? "partition" : "' '" ); > EXEC SQL PREPARE s_frags FROM :statement2; > > fragments.exprtext.loc_loctype = LOCUSER; > fragments.exprtext.loc_buffer = (char *)malloc( 4096 ); > fragments.exprtext.loc_bufsize = 4096; > fragments.exprtext.loc_write = writeit; > fragments.exprtext.loc_open = openit; > fragments.exprtext.loc_close = closeit; > tblobbuff.bbsize = 2048; > tblobbuff.buff = (char *)malloc( tblobbuff.bbsize ); > tblobbuff.global_length = 0; > ... > > static off_t pos = 0; > static int inblob = 0; > > int openit( loc_t *loc, int flags, int bsize ) > { > if (inblob == 1) { > fprintf( stderr, "Open called for new blob without close.\\ " > "length = %d.!\\ ", > loc->loc_xfercount ); > } > loc->loc_status = 0; > loc->loc_xfercount = 0L; > inblob = 0; > blobbuff->global_length = 0 ; > pos = 0; > /* Make sure blob buffer is large enough to hold BLOB. */ > if (bsize > blobbuff->bbsize) { > blobbuff->bbsize = bsize + (1024 - (bsize % 1024)); > blobbuff->buff = (char *)realloc( blobbuff->buff, blobbuff->bbsize ); > } > memset( blobbuff->buff, 0, blobbuff->bbsize ); > > return 0; > } > > int writeit( loc_t *loc, char *buf, int nbytes ) > { > if ((pos + nbytes) > blobbuff->bbsize) { > blobbuff->bbsize += (2 * (nbytes < 1024 ? 1024 : nbytes)); > blobbuff->buff = (char *)realloc( blobbuff->buff, blobbuff->bbsize > ); > } > if (!inblob) > inblob = 1; > memcpy( &(blobbuff->buff[pos]), buf, nbytes ); > pos += nbytes; > > loc->loc_xfercount += nbytes; > blobbuff->global_length += nbytes; > > return 0; > } > > int closeit( loc_t *loc ) > { > inblob = 0; > loc->loc_status = 0; > if (loc->loc_oflags & LOC_WONLY) { > /* Fetching, cleanup. */ > loc->loc_indicator = 0; > loc->loc_size = loc->loc_xfercount; > } > if( blobbuff->global_length != loc->loc_xfercount) > fprintf( stderr, > "GLOBAL_LENGTH != loc->loc_xfercount <%d, %d> \\ ", > blobbuff->global_length, > loc->loc_xfercount ); > pos = 0; > > return 0; > } > > See the CSDK manual for details on using the locator structure. > > Since they use a couple of static variables to communicate between them, > you would have to modify the openit, writeit, and closeit functions in order > to fetch from multiple tables containing blob columns or to fetch from a > table containing multiple blob columns, but for this application the code > will work fine. > > Art > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Oninit, the IIUG, nor any other > organization with which I am associated either explicitly or implicitly. > Neither do those opinions reflect those of other individuals affiliated > with any entity with which I am affiliated nor those of the entities > themselves. > > > > > On Tue, Sep 8, 2009 at 1:18 AM, NANDKISHOR SINGARE <ns_singare@yahoo.com>wrote: > >> Hi, >> Environment: IDS 11.50 on HP-UX 11. >> >> How can I get part of the TEXT datatype in char variable for string >> manipulation? I need to get the date out of "fragment expression on a >> table" >> from sysfragments table. But since the column exprtext which stores the >> fragment expression is TEXT type, I can not use SUBSTR or any functions on >> TEXT datatype. How can I extract date out of the fragment expression >> stored in >> TEXT datatype column? >> >> Thanks, >> Nandkishor >> >> >> >> ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --001517475742049edb0473122fce