Struct like complex data type for SQL results set

Author: james.reynolds@tribalgroup.com (jamesruk21)

Hi Uniface, We would like a complex data type that we can post SQL/print or SSP result sets into. This complex data type would allow us to jump to rows by index number and columns by name or column number in the result set. So to visualise a bit Select * from tblGeoff Col1 Col2 a aa b bb This would go into "sqltable" type Uniface dev would go variables string columncontent numeric rownum sqlresulttable sqltable endvariables ;do the sql get into sqltable variable sqltable = sql/print "Select * from tblGeoff" ;assume /print might change to /resulttable or whatever rownum = 0 while (rownum < sqltable.count()) ;here "Col1" columnname is equivalent to the column index "1" columncontent = sqltable[rownum].[Col1] or columncontent = sqltable[rownum].[1] rownum = rownum + 1 endwhile So reasons we would like to do this. 1. We like the normal drivers/methods that Uniface provides, but we want to put logic in queries/procs (SSPs) to do work outside of Uniface to improve performance and maybe use functions that aren't available in uniface 2. We currently have to do a lot of work in processing results from SQL/print and this would make it consitent across databases, and also faster as yous could write it into the framework 3. It really makes using direct/adhoc SQL quite accessible from Uniface 4. Other customers may have logic that they do not want to rewrite out of stored procs Hope this makes the cut. Thanks Jim

Hi Jim, one option I use is to change the select statement so all fields are concatenated together isung a tab as a separator. Then I use scan to access "%%^" which separates the records (skip the first 2 lines which hold fieldnames and underlines). This string is turned into an indexed list by replacing the TAB with ; if I want to have a random access option, these lists are collected into a list of records. And if you have a fixed select statement, the positions in each line are stable. So you can just use string extraction mystring[start:length] to get your field. Just a matter of pointer juggling. Your example above is not real Uniface $result: Assuming you have a C1 and a C2 field (watch the crippled filednames in line 1) this will look like: C Co = == a aa b bb If you want to be completely flexible, you can use the underlines (second line of $result) with their "="s as indicators of fieldpositions. Because I have plenty of time now, I may implement a quick demo what I'm up to in the next couple of days.

I have used the same method as Uli. Works fine. But I do not fully understand why activating stored procedures via signatures would not work in your case?

Hi Both (above), Thanks for the feedback. @Ulrich, we do something similar with the result string formatting as a list but we find that for large datasets, Uniface is poor at handling the strings/lists, it gets very slow and starts consuming memory. Also, that looks like at MS-SQL result set you've put there, we don't get that on our Oracle implementations, we get loads of extra white space on the end of each column which just adds massively to the resultset size (I've a separate call raised for this, not sure if it's the way we implement or not). Here's a couple of extra practical reasons why we would like a datatype like this. 1. It gives a consistent datatype for developers to code at across databases (which is a Uniface thing anyway?) 2. Hoping if it's written into Uniface, that it would be much faster and scale better than bashing lists/strings about in 4GL. 3. We have a component that lets our users define their own SQL queries, we be able to manage processing the results more effectively if the starting point was a fast, formatted, parseable dataset. @Theo this means we cannot use the Uniface suggested way of returning data through an SSP into a painted entity from what I understand. Ta Jim

Hi Jim, at first: Think I got it from SOLID, but I saw a similar pattern from ORACLE as well, please note that I am talking about a C1 and a C2 field in my example. Because it was introduced for on-the-fly reporting sql/print padds the field up to what uniface (or teh database) sees as maximum report width. I got similar formatting using the SQL*plus etc. if I see it from a uniface point of view (and I am not an employee of CPWR in any case), the answer would be: to put the entity in the data model and may use the where clause to transport native select code (with some limitations). Then you have stepped hitlist etc. A possibility is a mix of techniques to make the result set accessable with standard uniface means: - use temporary tables in the database (which are properly defined in the Uniface Model). - use a whatsoever SQL statement to populate this temporary table - retrieve data from the temporary table with a standard uniface retrieve - clear the table after use

BTW. Handling large strings in Uniface is a bit complicated, but if you slice the large string in smaller working buffers, you can achieve a very nice performance compared to always start the monster string from the beginning. And there are 3GL DLLs like USEQREAD which allows to read a flat file sequentially. So use SQL/PRINT, dump $result to a file and process this record for record with USEQREAD. Very fast, pretty easy, next to no memory consumption.

Hi Jim Do you know the fields which you want to read from database? If yes, you can do something like this *) v_SQL="SELECT RTRIM(FIELD_1)+CHAR(21)+CHAR(27)+RTRIM(FIELD_2)+CHAR(21)+CHAR(27)+RTRIM(FIELD_3) FROM A_TABLE" sql/print v_SQL,"DEF" v_RESULT_SET=$replace($result,1,"%%^","·;",-1) Now you do have an UnifAce-item-list(UIL) of rows, each of this rows are UIL of the fields. *) Due to an bug in the driver (MSS only?), somtimes the result is not as expected. UnifAce will fix this in one of the next patches. In meantime, one can use e.g "\!\;" as separator for the fields and then replace by GOLD-!+GOLD+; Ingo

ulrich-merkel said BTW. Handling large strings in Uniface is a bit complicated, but if you slice the large string in smaller working buffers, you can achieve a very nice performance compared to always start the monster string from the beginning. And there are 3GL DLLs like USEQREAD which allows to read a flat file sequentially. So use SQL/PRINT, dump $result to a file and process this record for record with USEQREAD. Very fast, pretty easy, next to no memory consumption.

Unfortunately, the underline seems to have no breaks any more. Since version 8, as I found, we get C Co ==== a aa b bb. So it is a little bit harder to find the cut positions: we have to check the header line and cut when spaces change to non-spaces.

Ok, here we go with an example. I'm not that C++ crack, so my code may have some smells, but it works at least. The uniface code reads as: ************************** cut here ************************************* entry do_gen_SQL params string p_DMLTEXT : IN ; SQL Statement string p_SQLPATH : IN ; Uniface Path string p_DUMPPATH: IN ; Path for dumpfile endparams sql/print p_DMLTEXT, p_sqlpath filedump $result,p_dumppath return(0) end ; do_gen_SQL entry do_split_SQL params string p_DUMPPATH: IN ; Path for dumpfile string p_Splitted: OUT ; Splitted result endparams variables string v_rec_header, v_rec_ruler, v_rec_data numeric v_loop endvariables p_splitted = "" ; open file, read the header and "ruler" line $30 = p_DUMPPATH $31 = "" perform "M4LS_OPEN" $30 = "" perform "M4LS_NEXT" v_rec_header = $30 $30 = "" perform "M4LS_NEXT" v_rec_ruler = $30 $30 = v_rec_header $31 = v_rec_header $32 = " " perform "M4LS_HEADER2LIST" p_splitted = $concat(p_splitted,v_rec_header,"%%^",$30,"%%^********%%^") ; read a couple of data lines; convert them to list according to ruler v_loop = 5 while (v_loop > 0) v_loop -= 1 $30 = "" perform "M4LS_NEXT" if ($status < 0) break endif v_rec_data = $30 $30 = v_rec_data $31 = v_rec_header $32 = " " perform "M4LS_HEADER2LIST" p_splitted = $concat(p_splitted,v_rec_data,"%%^",$30,"%%^********%%^") endwhile return(0) end ; do_split_SQL ************************** cut here ************************************* and the C code runs as ************************** cut here ************************************* /* ************** Special Global Variables */ char listsep; char nam_fpi[_MAX_PATH]; char workbuffer[8001]; long rec_cnt; FILE *fpi; /* ***************************** Exported functions */ XEXPORT(long) GETTICKS(void) { return(GetTickCount()); } XEXPORT(long) M4LS_INFO(void)/* returns name of file and record count */ { UPUTREGS(30,nam_fpi); return(rec_cnt); } XEXPORT(long) M4LS_OPEN(void) /* opens the file for reading and sets parameters*/ { UGETREGS(30,nam_fpi,sizeof(nam_fpi)-1); /* Input File */ UGETREGS(31,workbuffer,sizeof(workbuffer)-1); /* Separator */ listsep = workbuffer[0]; if (nam_fpi[0] == 0) { UPUTREGS(30,"M4LS_E0001 Input file has to be specified"); return(-11); }; if ((fpi = fopen(nam_fpi,"rt")) == NULL) { UPUTREGS(30,"M4LS_E0002 can't open input file"); rec_cnt = -11; return(-2); } rec_cnt = 0; return(rec_cnt); } XEXPORT(long) M4LS_CLOSE(void)/* closes the file and frees the buffers */ { if (fclose(fpi) == EOF) { UPUTREGS(30,"M4LS_E0003 can't close input file"); return(-5); }; rec_cnt = -2; return(0); } XEXPORT(long) M4LS_NEXT(void) /* copies next record to $30*/ { char *p; if (fgets(workbuffer,sizeof(workbuffer)-1,fpi) == NULL) /* read a record */ return(-3); for(p = workbuffer;*p != '';p++) if (*p=='\n') *p = ''; if (listsep != '') for(p = workbuffer;*p != '';p++) if (*p==listsep) *p = '\33'; UPUTREGS(30,workbuffer); return(++rec_cnt); } XEXPORT(long) M4LS_RULER2LIST(void) /* sets SEPARATORS in data where a LISTSEP character is encountered in RULER string */ { char L_DATA[8001]; char L_RULER[8001]; char L_LISTSEP[2]; char *p_data; char *p_ruler; UGETREGS(30,L_DATA,sizeof(L_DATA)-1); /* Data String */ UGETREGS(31,L_RULER,sizeof(L_RULER)-1); /* Ruler String */ UGETREGS(32,L_LISTSEP,sizeof(L_LISTSEP)-1); /* Separator Character in Ruler string */ if (L_LISTSEP[0] == 0) L_LISTSEP[0] = ' '; for(p_data = L_DATA, p_ruler = L_RULER;*p_data != '';p_data++, p_ruler++) if (*p_ruler==L_LISTSEP[0]) *p_data = '\33'; UPUTREGS(30,L_DATA); /* Data String */ return(0); } XEXPORT(long) M4LS_HEADER2LIST(void) /* sets SEPARATORS in data where SPACE changes to non-space in HEADER string */ { char L_DATA[8001]; char L_HEADER[8001]; char *p_data; char *p_header; char *p_header_prev; UGETREGS(30,L_DATA,sizeof(L_DATA)-1); /* Data String */ UGETREGS(31,L_HEADER,sizeof(L_HEADER)-1); /* Header String */ for(p_data = L_DATA, p_header_prev = L_HEADER, p_header = L_HEADER, p_header++;*p_header != '' && *p_data != '';p_data++, p_header_prev++, p_header++) if (*p_header_prev==' ' && *p_header != ' ') *p_data = '\33'; UPUTREGS(30,L_DATA); /* Data String */ return(0); } ************************** cut here ************************************* Don't forget to declare the DLL in your ASN File with ************************** cut here ************************************* [USER_3GL] ..\3GL\M4LSEQREAD_U8.dll /preload ************************** cut here ************************************* Because it's still the old dITo (do IT ourself) spirit, feel free to use it, copy it, modify it (even in commercial projects). Note: I just saw that the layout is lost in display, perhaps its worth to make it a downloadable text. (Unfortunately, I can not add a download file here, currently).

Because downloads are not possible here, you can use my homepage www.uli-merkel.de to download "dITo project SEQREAD plus - how2test.zip" which holds a DLL and a description.