• Welcome, Guest. Please login.
 
August 22, 2019, 09:55:25 pm

News:

Welcome to the SQLitening support forums!


Results in Row or Column Order (also formatted to disk)

Started by cj, March 29, 2017, 04:44:37 pm

Previous topic - Next topic

cj



Formatted Row and Column Arrays Optionally To Disk

Solid State Drives and disk cache make writing to disk very fast.
Other programs may require TAB or other delimited formats to read in data.
If another program needs the same data it may already be in memory.

Data passed using a file uses little memory and has advantages.
Receiving programs can process the data lines at a time or read all in at once.
It also allows users to view the output on any station at any time.
Results may be produced in ROW order and COLUMN order without processing twice.
4 new functions are highlighted (no more too much data for a MSGBOX while testing.)

FUNCTION PBMAIN AS LONG

h& = freefile           
OPEN "output.txt" FOR APPEND AS #h&                  'open output file
slopen "sample.db3"                                  'open database
slselary "select * from parts",sArray()              'select data
WriteElementsRowOrder h&,sColRowArray(),$Delimiter   'format to disk in row order
PRINT #h&                                            'blank line

ReverseDimensions sArray(),sColArray()               'create array in column order
WriteElementsColumnOrder h&,sColArray(),$Delimiter   'format to disk in column order
CLOSE #h&                                            'close output file to allow display
DisplayTextFile "output.txt"                         'display using program for txt files

END FUNCTION

output.txt

NO|MANUF|REDREF|PRICE
1|3COM|00100283|3365
2|3COM|00100284|160420
3|3COM|00100285|49218
4|3COM|00100286|51861
5|3COM|00100287|2857
6|3COM|00100289|239358
7|3COM|00100290|779
8|3COM|00100295|68922
9|3COM|00100296|16941
10|3COM|00100298|6746
11|3COM|00100299|8376

NO|1|2|3|4|5|6|7|8|9|10|11
MANUF|3COM|3COM|3COM|3COM|3COM|3COM|3COM|3COM|3COM|3COM|3COM
REDREF|00100283|00100284|00100285|00100286|00100287|00100289|00100290|00100295|00100296|00100298|00100299
PRICE|3365|160420|49218|51861|2857|239358|779|68922|16941|6746|8376




#DIM ALL
#INCLUDE "win32api.inc"
#INCLUDE "sqlitening.inc"
$Delimiter = "|"

FUNCTION PBMAIN () AS LONG

LOCAL sColRowArray() AS STRING
LOCAL sRowColArray() AS STRING
LOCAL sOutputFile    AS STRING
LOCAL h              AS LONG

sOutputFile = "output.txt"
KILL sOutputFile:ERRCLEAR
h = FREEFILE
OPEN sOutputFile FOR APPEND AS #h
PRINT #h, "Formatted Row and Column Arrays Optionally To Disk";TAB(62)DATE$ + " " + TIME$
PRINT #h
PRINT #h, "Solid State Drives and disk cache make writing to disk very fast."
PRINT #h, "Other programs may require TAB or other delimited formats to read in data."
PRINT #h, "if another program uses the data on the same machine it may be in memory."
PRINT #h, "Receiving programs can process the data lines at a time or read all in at once."
PRINT #h, "It also allows users to view the output on any station at any time."
PRINT #h, "Results may be produced in ROW order and COLUMN order.
PRINT #h, "This program shells to the default program associated to read .TXT files."
PRINT #h,

REM slConnect "123.123.123.123"  'optional, get data far,far away

slopen "sample.db3"
slselary "select rowid as NO,MANUF,Redref,price from parts limit 11",sColRowArray()

WriteElementsRowOrder h,sColRowArray(),$Delimiter
PRINT #h, STRING$(80,"-")

ReverseDimensions sColRowArray(),sRowColArray() 'create array in column order
WriteElementsColumnOrder h,sRowColArray(),$Delimiter

CLOSE #h
DisplayTextFile sOutputFile

slDisconnect

END FUNCTION

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

'NOTE: Do not REDIM sDestArray() before or after calling to prevent corrupting SourceArray()

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

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

'----------------------------------------------------------------------------------
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

FUNCTION DisplayTextFile(sFileName AS STRING) AS LONG
LOCAL zFileName AS ASCIIZ * 257
zFileName = sFileName
ShellExecute (0, "OPEN", zFileName, BYVAL 0, CURDIR$, %SW_SHOWNORMAL)
END FUNCTION


FUNCTION WriteElementsColumnOrder(hFile AS LONG, sArray() AS STRING,sDelimiter AS STRING) AS LONG

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

lCol = LBOUND(sArray,2)  'row/column order values
UCol = UBOUND(sArray,2)
LRow = LBOUND(sArray,1)
URow = UBOUND(sArray,1)

rows = UROW-LRow '0 based number of rows
cols = UCOL-lCol '0 based number of columns
'array elements could be negative, 0, or positive so simple IF used
FOR COL = lCol TO UCol
  FOR ROW  = lRow TO Urow
   IF ROW < uRow THEN
    PRINT #hFile,sArray(ROW,COL);sDelimiter;
   ELSE
    PRINT #hFile,sArray(ROW,COL)
   END IF
  NEXT
NEXT COL

END FUNCTION

FUNCTION WriteElementsRowOrder(hFile AS LONG, sArray() AS STRING,sDelimiter AS STRING) AS LONG

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

lCol = LBOUND(sArray,1)  'column/row order values
UCol = UBOUND(sArray,1)
LRow = LBOUND(sArray,2)
URow = UBOUND(sArray,2)

rows = UROW-LRow
cols = UCOL-lCol

'array elements could be negative, 0, or positive so simple IF used
FOR ROW  = lRow TO Urow
  FOR COL = lCol TO UCol
   IF COL < UCol THEN             'not last column use delimiter
     PRINT #hFile,sArray(COL,ROW);sDelimiter;
   ELSE
     PRINT #hfile,sArray(COL,ROW) 'last column no delimiter
   END IF
  NEXT COL
NEXT ROW

END FUNCTION