• Welcome, Guest. Please login.
 
September 17, 2019, 09:55:52 am

News:

Welcome to the SQLitening support forums!


About slSelArray

Started by Jean-Pierre LEROY, March 20, 2017, 02:41:22 pm

Previous topic - Next topic

Jean-Pierre LEROY

slSelArray is very powerful function to retrieve many records and later display them using a virtual ListView or a grid.

By example, for a specific application, I'm able to retrieve and display thousands of records (30 000) in less than 10 seconds.

Currently slSelArray fills the Array by column then row: Array(Column, Row).

In order to use Array Scan or Array Sort I need the Array in the reverse order, i.e. Array(Row, Column).

Your help will be appreciated.

Thanks.
Jean-Pierre


Jean-Pierre LEROY

or why not a function two reverse a two dimensional string Array ?

Jean-Pierre LEROY

Many thanks. I will try to use your example.

Regards. JPL

Jean-Pierre LEROY

Thanks a lot for the corrected version  :)

I will do more tests.

Regards,
Jean-Pierre

Bern Ertl

March 22, 2017, 11:53:39 am #4 Last Edit: March 22, 2017, 12:05:00 pm by Bern Ertl
I have a solution of sorts:#COMPILE EXE
#DIM ALL

%Columns = 5
%Rows = 30

FUNCTION PBMAIN () AS LONG

    DIM sSourceArray( %Columns, %Rows) AS LOCAL STRING
    DIM sDestArray( %Rows, %Columns) AS LOCAL STRING

    LOCAL lpSource AS LONG PTR
    LOCAL lpDest AS LONG PTR

    LOCAL lRow AS LONG, lCol AS LONG

    'Fill arrays with some data
    FOR lCol = 0 TO %Columns
        FOR lRow = 0 TO %Rows
            sSourceArray( lCol, lRow) = "sSourceArray(" + FORMAT$( lCol) + ", " + FORMAT$( lRow) + ")"
        NEXT
    NEXT

    'Assign sting pointers to sDestArray
    lpSource = VARPTR( sSourceArray( 0, 0))
    RESET sDestArray()   'Prevent memory leak in case sDestArray() contained any data prior to overwriting string pointers
    lpDest = VARPTR( sDestArray( 0, 0))

    FOR lRow = 0 TO %Rows
        FOR lCol = 0 TO %Columns
            @lpDest[ lRow OF %Rows, lCol OF %Columns] = @lpSource[ lCol OF %Columns, lRow OF %Rows]
        NEXT
    NEXT

    'Check our work...
    MSGBOX "sDestArray( " + FORMAT$( %Rows) + ", " + FORMAT$( %Columns) + ")=" + sDestArray( %Rows, %Columns)

    'Test for potential issue...
    sSourceArray( %Columns, %Rows) = "Some new data"

    'Check what happens to sDestArray - potential GPF
    MSGBOX "sDestArray( " + FORMAT$( %Rows) + ", " + FORMAT$( %Columns) + ")=" + sDestArray( %Rows, %Columns)


END FUNCTION


The only potential problem is that the data in sDestArray is only valid as long as the data in sSourceArray is not modified/deleted.  If you try to access the string in either array after modifying/deleting the corresponding string data in the other array, you could get a GPF for accessing memory that is no longer valid.

This code is only transposing the string pointers from the source array into the destination array.  It's not copying or moving any of the actual string data, so it's pretty fast.

Bern Ertl

Quote from: cj on March 26, 2017, 01:42:41 am
The pointer method only works twice when switching Col to Row then Row to Col.
For this reason (until figuring out why)...


It has to do with the way PowerBASIC handles strings.  When you use the routine I posted, you are copying pointers to the string data into your array.  There are now two variables pointing to any given string (an element each from the source and transposed arrays).  As soon as one of the variables pointing at a string gets reassigned (or reset), the actual string data gets moved or erased and the pointer to the old string data is no longer valid.

I was wondering if (ie. I had not tested for) simply REDIMensioning the transposition array after the string data had been erased in the source array would cause PB to try and erase the string data for the array elements (ie. referencing the now invalid memory).  I guess my routine should be rewritten to use an array of string pointers for the transposition array instead of an array of strings (to eliminate the GPF on REDIMensioning issue), but then the transposition array wouldn't be useful for populating the grid control.

I guess there is no free lunch.  You are probably best off copying all the string data from array to the other.

Bern Ertl

Actually, I just had an idea that might work.  What we need for the pointer transposition code is our own RESET function for the transposition array.  We need to either clear or reset the pointers before REDIMensioning.  Something like:

...
FOR ROW = 0 TO rows
  FOR COL = 0 TO cols
   @lpDest[ROW OF rows , COL OF cols] = 0
  NEXT COL
NEXT ROW

'Now safe to redim sDestArray ...

cj

A simple work-around  is to use a fresh source array before any call to reverse dimensions.

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
LOCAL COL,ROW,cols,rows,x,ReverseThisManyTimes AS LONG
slOpen "sample.db3"
DIM sOutput() AS STRING
  FOR x = 5 TO 1 STEP -1  'reverse so we see if any residue in results
  REDIM sInput(0) AS STRING
  slSelAry "select rowid,manuf,price from parts limit "+STR$(x),sInput()
  ReverseDimensions sInput(),sOutPut()
  ? JOIN$(sInput(),$CR) + $CR + $CR + JOIN$(sOutput(),$CR),,USING$("LIMIT #",x)
NEXT
END FUNCTION

FUNCTION ReverseDimensions(sSourceArray() AS STRING,sDestArray() AS STRING) AS LONG

LOCAL lpSource AS LONG PTR
LOCAL lpDest   AS LONG PTR
LOCAL COL,ROW,lCol,lRow,uCol,uRow,Cols,Rows AS LONG

lCol = LBOUND(sSourceArray,1)
UCol = UBOUND(sSourceArray,1)
LRow = LBOUND(sSourceArray,2)
URow = UBOUND(sSourceArray,2)

rows = UROW-LRow '0 based number of rows
cols = UCOL-lCol '0 based number of columns

REDIM sDestArray(LRow TO URow,LCol TO UCol) AS STRING
lpSource = VARPTR(sSourceArray(lCol,lRow)) 'first element of source array
lpDest   = VARPTR(sDestArray  (lRow,lCol)) 'first element of destinstion array
'added init routine for destination array didn't fix so remarking
'FOR ROW = 0 TO rows
' FOR COL = 0 TO cols
'  @lpDest[ROW OF rows , COL OF cols] = 0
' NEXT COL
' NEXT ROW

FOR ROW = 0 TO rows
  FOR COL = 0 TO cols
   @lpDest[ROW OF rows , COL OF cols] = @lpSource[COL OF cols, ROW OF rows]
  NEXT COL
NEXT ROW

END FUNCTION

cj

March 27, 2017, 09:47:52 pm #8 Last Edit: March 27, 2017, 09:52:40 pm by cj
'slSelAry 84 seconds to process 10-million records
'slSelect 10 seconds to process 10-million records

'slSelAry uses REDIM PRESERVE every 2,500 records
'This can be controlled if you create the array

'Another advantage of creating the array locally is that you can
'display the first page immediately and do other things like processing any record.

'slSelAry is extremely valuable.  Just bringing up another option.


#DIM ALL
#INCLUDE "sqlitening.inc" 'slSelectTest.bas
%DropTable = 0
%AddMoreRecords = 1000000
%ReDimPreserveWhen = 1000000 'slSelAry REDIM's every 2,500 records

FUNCTION PBMAIN () AS LONG
LOCAL s,sStartTime,sEndTime AS STRING

IF %AddMoreRecords THEN AddMoreRecords(%AddMoreRecords)
? "Begin",%MB_SYSTEMMODAL
slOpen "sample.db3"

sStartTime = TIME$                  'slSelect instead of slSelAry
REDIM sColRow(0,0) AS STRING
REDIM sRowCol(0,0) AS STRING
slSelect("select rowid,* from t1",sColRow(),1)  '1 = add column names
sEndTime = TIME$
s+=  sStartTime + "  " + sEndTime + "  " + USING$("Rows #,", UBOUND(sColRow,2)) + $CR
? s
END

sStartTime = TIME$                  '
REDIM sColRow(0,0)  AS STRING
REDIM sRowCol(0,0) AS STRING
slSelAry "select rowid,* from t1",sColRow()
sEndTime = TIME$
s+= sStartTime + "  " + sEndTime + "  " + USING$("Rows #,",UBOUND(sColRow,2))   + $CR
BEEP

? s,%MB_SYSTEMMODAL,"slSelect"

END FUNCTION

FUNCTION ReverseDimensions(sSourceArray() AS STRING,sDestArray() AS STRING) AS LONG

LOCAL lpSource AS LONG PTR
LOCAL lpDest   AS LONG PTR
LOCAL COL,ROW,lCol,lRow,uCol,uRow,Cols,Rows AS LONG

lCol = LBOUND(sSourceArray,1)
UCol = UBOUND(sSourceArray,1)
LRow = LBOUND(sSourceArray,2)
URow = UBOUND(sSourceArray,2)

rows = UROW-LRow '0 based number of rows
cols = UCOL-lCol '0 based number of columns

REDIM sDestArray(LRow TO URow,LCol TO UCol) AS STRING
lpSource = VARPTR(sSourceArray(lCol,lRow)) 'first element of source array
lpDest   = VARPTR(sDestArray  (lRow,lCol)) 'first element of destinstion array

FOR ROW = 0 TO rows
  FOR COL = 0 TO cols
   @lpDest[ROW OF rows , COL OF cols] = @lpSource[COL OF cols, ROW OF rows]
  NEXT COL
NEXT ROW

END FUNCTION


SUB slSelect(sql AS STRING,sArr() AS STRING,OPTIONAL AddColumnNames AS LONG)

  ERASE sArr()
  LOCAL c         AS LONG
  LOCAL rownumber AS LONG
  LOCAL cols      AS LONG
  LOCAL skipcolumnnames AS LONG
  LOCAL upper     AS LONG
  LOCAL FirstRow  AS LONG

  IF slSel(sql,0,"D E0") THEN BEEP:EXIT SUB  'allow [D]uplicate columns, exit on [E]rror


  cols = slGetColumnCount
  IF cols THEN
    upper = %RedimPreserveWhen
    IF ISFALSE(ISMISSING(AddColumnNames)) AND AddColumnNames THEN
      FirstRow = 0  'add column names
      DIM sArr(1 TO cols,FirstRow TO upper) AS STRING
      FOR c = 1 TO cols   'column names in column,row=0
        sArr(c,0) = slGetColumnName(c)
      NEXT
    ELSE ' "Skipping column names
      FirstRow = 1
      DIM sArr(1 TO cols,FirstRow TO upper) AS STRING
    END IF

    DO WHILE slGetRow
      INCR rownumber
      IF rownumber > upper THEN
        upper+= %RedimPreserveWhen
        REDIM PRESERVE sArr(1 TO cols,FirstRow TO upper)
      END IF
      FOR c = 1 TO cols
        sArr(c,rownumber)=slf(c)
      NEXT
    LOOP

    IF rownumber THEN
       REDIM PRESERVE sArr(1 TO cols,FirstRow TO rownumber)
    ELSE
      ERASE sArr
    END IF
  END IF
END SUB

SUB AddMoreRecords(ItemsToAdd AS LONG)
LOCAL x,OldMax AS LONG
slOpen "sample.db3"                                 'open database
IF %DropTable THEN slexe "drop table if exists t1"
slexe "create table if not exists t1(c1)"           'create new table
slexe "begin"
slSel "select count(*) from t1"
slGetRow
OldMax = VAL(slf(1))+1
FOR x=1 TO ItemsToAdd
  slexe USING$("insert into t1 values('item#')",OldMax)
  IF x MOD 1000000 = 0 THEN SLEEP 10
  INCR oldMax
NEXT
slexe "end"
END SUB

Bern Ertl

March 28, 2017, 10:20:46 am #9 Last Edit: March 28, 2017, 10:24:10 am by Bern Ertl
Quote from: cj on March 27, 2017, 03:04:25 pm...
Tried adding your suggestion without success, but may have done it incorrectly
...


#COMPILE EXE
#DIM ALL

%Columns = 5
%Rows = 30
%NumLoops = 5

FUNCTION PBMAIN () AS LONG

    DIM sSourceArray( %Columns, %Rows) AS LOCAL STRING
    DIM sDestArray( %Rows, %Columns) AS LOCAL STRING

    LOCAL lpSource AS LONG PTR
    LOCAL lpDest AS LONG PTR

    LOCAL lRow AS LONG, lCol AS LONG

    LOCAL x AS LONG

    'Fill arrays with some data
    FOR lCol = 0 TO %Columns
        FOR lRow = 0 TO %Rows
            sSourceArray( lCol, lRow) = "sSourceArray(" + FORMAT$( lCol) + ", " + FORMAT$( lRow) + ")"
        NEXT
    NEXT

    'Assign sting pointers to sDestArray
    lpSource = VARPTR( sSourceArray( 0, 0))
    lpDest = VARPTR( sDestArray( 0, 0))

FOR x = 1 TO %NumLoops

    FOR lRow = 0 TO %Rows
        FOR lCol = 0 TO %Columns
            @lpDest[ lRow OF %Rows, lCol OF %Columns] = 0
        NEXT
    NEXT

'***
'Either of the following works...
    RESET sDestArray() 
'or
    REDIM sDestArray( %Rows, %Columns) AS LOCAL STRING
    lpDest = VARPTR( sDestArray( 0, 0))
'/***

    FOR lRow = 0 TO %Rows
        FOR lCol = 0 TO %Columns
            @lpDest[ lRow OF %Rows, lCol OF %Columns] = @lpSource[ lCol OF %Columns, lRow OF %Rows]
        NEXT
    NEXT

NEXT

    'Check our work...
    MSGBOX "sDestArray( " + FORMAT$( %Rows) + ", " + FORMAT$( %Columns) + ")=" + sDestArray( %Rows, %Columns)


END FUNCTION

cj

March 28, 2017, 10:33:46 am #10 Last Edit: March 29, 2017, 08:11:18 am by cj
I'm sure it works without a function within PBMAIN, but I'm using a function.

FUNCTION ReverseDimensions(sSourceArray() AS STRING,sDestArray() AS STRING) AS LONG

LOCAL lpSource AS LONG PTR
LOCAL lpDest   AS LONG PTR
LOCAL COL,ROW,lCol,lRow,uCol,uRow,Cols,Rows AS LONG

lCol = LBOUND(sSourceArray,1)
UCol = UBOUND(sSourceArray,1)
LRow = LBOUND(sSourceArray,2)
URow = UBOUND(sSourceArray,2)

rows = UROW-LRow '0 based number of rows
cols = UCOL-lCol '0 based number of columns

REDIM sDestArray(LRow TO URow,LCol TO UCol) AS STRING
lpSource = VARPTR(sSourceArray(lCol,lRow)) 'first element of source array
lpDest   = VARPTR(sDestArray  (lRow,lCol)) 'first element of destinstion array

FOR ROW = 0 TO rows
  FOR COL = 0 TO cols
   @lpDest[ROW OF rows , COL OF cols] = @lpSource[COL OF cols, ROW OF rows]
  NEXT COL
NEXT ROW

END FUNCTION


Bern Ertl

March 28, 2017, 11:28:12 am #11 Last Edit: March 28, 2017, 11:35:46 am by Bern Ertl
Quote from: cj on March 28, 2017, 10:33:46 am
I'm sure it works without a function within PBMAIN, but I'm using a function.
...


Not tested, but I think it's correct...

FUNCTION ReverseDimensions(sSourceArray() AS STRING,sDestArray() AS STRING) AS LONG

LOCAL lpSource AS LONG PTR
LOCAL lpDest   AS LONG PTR
LOCAL COL,ROW,lCol,lRow,uCol,uRow,Cols,Rows AS LONG

'*** Added to prevent GPF on re-use of sDestArray ***
lCol = LBOUND(sDestArray,2)
UCol = UBOUND(sDestArray,2)
LRow = LBOUND(sDestArray,1)
URow = UBOUND(sDestArray,1)

rows = UROW-LRow '0 based number of rows
cols = UCOL-lCol '0 based number of columns

lpDest   = VARPTR(sDestArray  (lRow,lCol)) 'first element of destinstion array
FOR ROW = 0 TO rows
  FOR COL = 0 TO cols
   @lpDest[ROW OF rows , COL OF cols] = 0
  NEXT COL
NEXT ROW
'*** /Added ***

lCol = LBOUND(sSourceArray,1)
UCol = UBOUND(sSourceArray,1)
LRow = LBOUND(sSourceArray,2)
URow = UBOUND(sSourceArray,2)

rows = UROW-LRow '0 based number of rows
cols = UCOL-lCol '0 based number of columns

REDIM sDestArray(LRow TO URow,LCol TO UCol) AS STRING

lpSource = VARPTR(sSourceArray(lCol,lRow)) 'first element of source array
lpDest   = VARPTR(sDestArray  (lRow,lCol)) 'first element of destinstion array

FOR ROW = 0 TO rows
  FOR COL = 0 TO cols
   @lpDest[ROW OF rows , COL OF cols] = @lpSource[COL OF cols, ROW OF rows]
  NEXT COL
NEXT ROW

END FUNCTION

cj

The source array gets trashed on the 3rd call even with the new code.
Recreating the source each time works, but there is something wrong.

Got to work by no REDIM of destination array, but still needs work.

#COMPILE EXE
#DIM ALL
%ReLoadSource = 0 '0=fails on 3rd call,  1=never fails

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG

LOCAL x, ReloadSource AS LONG, sMsg AS STRING

ReloadSource = %ReloadSource  '0=use original array else create new source
IF ReloadSource THEN  sMsg = " Source reloaded"  ELSE sMsg = " Source NOT reloaded"

slopen "sample.db3"

'create original array in row order
REDIM sColRow(0) AS STRING
slselary "select rowid,manuf,price from parts limit 2",sColRow()

FOR x = 1 TO 10

  IF ReloadSource THEN 'recreate source array
    REDIM sColRow(0) AS STRING
    slselary "select rowid,manuf,price from parts limit 2",sColRow()
  END IF

  REDIM sRowCol(0) AS STRING
  ReverseDimensions sColRow(),sRowCol() 'create array in column order

  ? JOIN$(sColRow(),$CR) + $CR + $CR +_   'source array
    JOIN$(sRowCol(),$CR),,STR$(x) + sMsg         'destination array
NEXT

END FUNCTION

FUNCTION ReverseDimensions(sSourceArray() AS STRING,sDestArray() AS STRING) AS LONG

LOCAL lpSource AS LONG PTR
LOCAL lpDest   AS LONG PTR
LOCAL COL,ROW,lCol,lRow,uCol,uRow,Cols,Rows AS LONG

'*** Added to prevent GPF on re-use of sDestArray ***
lCol = LBOUND(sDestArray,2)
UCol = UBOUND(sDestArray,2)
LRow = LBOUND(sDestArray,1)
URow = UBOUND(sDestArray,1)

rows = UROW-LRow '0 based number of rows
cols = UCOL-lCol '0 based number of columns

lpDest   = VARPTR(sDestArray  (lRow,lCol)) 'first element of destinstion array
FOR ROW = 0 TO rows
  FOR COL = 0 TO cols
   @lpDest[ROW OF rows , COL OF cols] = 0
  NEXT COL
NEXT ROW
'*** /Added ***

lCol = LBOUND(sSourceArray,1)
UCol = UBOUND(sSourceArray,1)
LRow = LBOUND(sSourceArray,2)
URow = UBOUND(sSourceArray,2)

rows = UROW-LRow '0 based number of rows
cols = UCOL-lCol '0 based number of columns

REDIM sDestArray(LRow TO URow,LCol TO UCol) AS STRING

lpSource = VARPTR(sSourceArray(lCol,lRow)) 'first element of source array
lpDest   = VARPTR(sDestArray  (lRow,lCol)) 'first element of destinstion array

FOR ROW = 0 TO rows
  FOR COL = 0 TO cols
   @lpDest[ROW OF rows , COL OF cols] = @lpSource[COL OF cols, ROW OF rows]
  NEXT COL
NEXT ROW

END FUNCTION

cj

NO REDIM of destination array before calling prevents source array from being scrambled on the 3rd call (see code above.)
I hope it leads to a better solution (if it isn't the solution.)

REM REDIM sRowCol(0,0) AS STRING 'no error if this line is remarked
ReverseDimensions sColRow(),sRowCol() 'create array in column order

Of course a one-time DIM  of the destination row/col array needs to be performed.
Need to investigate why REDIM destination array shuffles or corrupts memory of source. 
Looking at  removing extra code.                                                 

Bern Ertl

Quote from: cj on March 28, 2017, 02:33:57 pm
The source array gets trashed on the 3rd call even with the new code.
Recreating the source each time works, but there is something wrong.


You don't need to REDIM the destination array twice.  The code I added to the function call manually resets the string pointers to make that array safe to REDIM.  You added a REDIM to the array outside of the function before the array was made safe to REDIM and thus erasing all the string data (which the source array is still pointing at).  That will cause a GPF the next time a string in the source array is referenced.

The function, as written in my last post, will already redimension the destination array as needed.  The destination array should be considered "read-only" outside of the function (ie. safe to pass data to the grid control, but not for new assignments, clearing or redimensioning).