Announcement

Collapse
No announcement yet.

MLG Questions

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • #21
    Thanks Mike,

    Very interesting.
    Fredrick Ughimi
    www.meganetsoft.com

    Comment


    • #22
      Hello,

      Is there a way to read SQLitening Table Field values into MLG Column formatted as Combo?

      Psedo:

      Code:
      slSelAry "SELECT Field1 FROM Table LIMIT 10", a(), "Q9"
      MLG_FormatColCombo hJornalEntryGrid,2, a()
      King regards.
      Last edited by Fredrick Ughimi; 21 Oct 2015, 05:57 PM.
      Fredrick Ughimi
      www.meganetsoft.com

      Comment


      • #23
        Column names and much more

        SQLitening keeps the column names in element 0 of the returned recordset if a 1-dimensional array is requested with the "Q" option.
        slSelAry "select * from part",sArray(),"Q9" '9 is the ASCII column delimiter.

        To make it easier to work with the column names wrote this:
        columns = GetColumnNames(sRecordset$(),sColNames$(),Delimit$) AS LONG

        MLG makes it very easy to handle data in the form sData(row,col)
        SQlitening makes it very easy to handle data in the form sData(col,row)

        Since a 1-dimensional array was requested from SQLitening a bit more is
        involved and a second array created to pass a 2-dimensional array (row,col).
        Somebody may have a better way!!

        The main point, the column names are easy to get with GetColumnNames function that places them into an array from element 0 of the recordset
        when using the "Q" + delimiter option which specified a 1-dim array.
        Code:
        #INCLUDE "win32api.inc"     'mlg uses arrays (rows,cols)
        #INCLUDE "mlg.inc"          'sqlitening uses (cols,rows)
        #INCLUDE "sqlitening.inc"
        %GRID101 = 101
        %GRID102 = 102
         
        FUNCTION PBMAIN () AS LONG  'sl2mlg.bas  10/22/15
          OneDimensionToMLG 'modeless no callback
          TwoDimensionToMLG 'modal    no callback
        END FUNCTION
         
        FUNCTION GetColumnNames(OneDimRecordSet() AS STRING,sColumnNames() AS STRING,sDelimiter AS STRING) AS LONG
          '1-dimensional recordset returned using slSelAry with Q option
          LOCAL cols AS LONG
          IF UBOUND(OneDimRecordSet)> -1 THEN
            cols = PARSECOUNT(OneDimRecordSet(0),sDelimiter)
            IF cols THEN
              REDIM sColumnNames(1 TO cols)
              PARSE OneDimRecordSet(0),sColumnNames(),sDelimiter
              FUNCTION = cols
            END IF
          END IF
        END FUNCTION
         
        SUB OneDimensionToMLG
          LOCAL hDlg1,hGrid1 AS DWORD, ROW,COL,rows,cols,x AS LONG
          LOCAL a() AS STRING, colNames() AS STRING
          LOCAL sDelimiter AS STRING
           DIALOG NEW 0, "1-dimension array to 2-dimension array to MLG",200,0,440, 370, %WS_SYSMENU OR %WS_THICKFRAME TO hDlg1
          CONTROL ADD "MYLITTLEGRID",hDlg1,%GRID101,"",0,0,820,370,%MLG_STYLE OR %WS_TABSTOP
          CONTROL HANDLE hDlg1, %GRID101 TO hGrid1
           'column names are in element 0 if Q specified, delimited by Q+? when 1-dim array is used
          sDelimiter = $TAB
          slOpen "sample.db3"
          slSelAry "select rowid,manuf,redref,product from parts",a(),"Q"+FORMAT$(ASC(sDelimiter))
          cols = GetcolumnNames(a(),ColNames(),sDelimiter)
          IF cols < 1 THEN EXIT SUB
           '? JOIN$(ColNames(),$CR),,"Cols" + STR$(cols)
          rows = UBOUND(a)
          DIM rowsCols(rows, cols) AS STRING
          FOR ROW = 1 TO rows
            FOR COL = 1 TO cols
              rowscols(ROW,COL) = PARSE$(a(ROW),sDelimiter,COL)
            NEXT
          NEXT
           IF rows > -1 THEN
            LOCAL zs AS ASCIIZ * 256
            Mlg_PutEx hGrid1,RowsCols(),-4,1  '-4=do not write over row or column headers,1=refresh grid
            'get column names
            FOR x = 1 TO cols
              zs = ColNames(x)
              sendMessage hGrid1,%MLG_SetCell,MAKLNG(0,x),VARPTR(zs)
            NEXT
           DIALOG SHOW MODELESS hDlg1
          END IF
        END SUB
         
        SUB TwoDimensionToMLG
          LOCAL hDlg2,hGrid2 AS DWORD, rows,cols,x AS LONG
          LOCAL RowsCols() AS STRING
          LOCAL ColNames() AS ASCIIZ * 256
          DIALOG NEW 0, "2-dimension array to MLG",646,0, 440, 370, %WS_SYSMENU OR %WS_THICKFRAME TO hDlg2
          CONTROL ADD "MYLITTLEGRID",hDlg2,%GRID102,"",0,0,820,370,%MLG_STYLE OR %WS_TABSTOP
          CONTROL HANDLE hDlg2, %GRID102 TO hGrid2
          slMLGArray "select rowid, manuf, redref,product from parts",RowsCols(),ColNames()
          rows = UBOUND(RowsCols)
          cols = UBOUND(RowsCols,2)
          IF rows > -1 THEN
            Mlg_PutEx hGrid2,RowsCols(),-4,1  '-4=do not write over row or column headers,1=refresh grid
            FOR x = 1 TO cols:sendMessage hGrid2,%MLG_SetCell,MAKLNG(0,x),VARPTR(ColNames(x)):NEXT 'get column names
            DIALOG SHOW MODAL hDlg2
          END IF
        END SUB
         
        SUB slMLGArray(sql AS STRING,RowsCols() AS STRING,ColumnNameArray() AS ASCIIZ*256)
         'create  recordset into ColRow() array, return RowCol() array for MLG
          LOCAL ROW,COL,rows,cols AS LONG, ColsRows() AS STRING
          slOpen "sample.db3","C"
          slSel sql
          cols = slGetColumnCount
          IF cols > 0 THEN
            rows = 100000
            DIM ColsRows(cols,rows) AS STRING
             REDIM ColumnNameArray(1 TO cols) 'column names
            FOR COL = 1 TO cols
              ColumnNameArray(COL) = slGetColumnName(COL)
            NEXT
             DO WHILE slGetRow
              INCR ROW 'actual count
              IF ROW MOD 100000 = 0 THEN
                 REDIM PRESERVE ColsRows(cols,ROW+100000)
              END IF
              FOR COL = 1 TO cols
                ColsRows(COL,ROW) = slf(COL)
              NEXT
            LOOP
            rows = ROW 'redim to actual size if need ColsRowsArray
            REDIM RowsCols(rows,cols) 'create array for My Little Grid (cols,rows)
            FOR ROW = 1 TO rows
              FOR COL = 1 TO cols
                RowsCols(ROW,COL) = ColsRows(COL,ROW)
             NEXT
            NEXT
          END IF
        END SUB
        Attached Files
        https://duckduckgo.com instead of google

        Comment


        • #24
          Hello Mike,

          Thank you for your sample code. Maybe I did not explain what I meant well enough.

          I want to fill a MLG Combobox column with values from an SQLitening table.

          For instance:

          Code:
          MLG_FormatColCombo hGrid1,2,"1st Choice Ed,2nd Choice Ed ,3rd Choice Ed"
          Can "1st Choice Ed,2nd Choice Ed ,3rd Choice Ed" be filled from SQLitening table? Just like I used to do with the normal Combobox with the following code:

          Code:
          Method ReadVanuesFromTable( ByVal nCbHndl As Long ) As Long
                  
                  slSEL "SELECT Description FROM tblDefineAllowances"
                      
                  ' Process records
                  Do While slGetRow
                  
                      ' Set first item. 
                      SendDlgItemMessage(nCbHndl, %ID_FRMALLOWANCESTABLERPTDLG_CBOALLOWANCE, %CB_SETCURSEL, 0, 0 )    
                                             
                      VD_ListBox_AddItem(nCbHndl, %ID_FRMALLOWANCESTABLERPTDLG_CBOALLOWANCE, slFN("Description"), 1)
                                         
                  Loop
                  
               End Method
          Last edited by Fredrick Ughimi; 22 Oct 2015, 11:45 AM.
          Fredrick Ughimi
          www.meganetsoft.com

          Comment


          • #25
            This is only an example creating a ChoiceTable and later using
            JOIN$ to combine all the selected items that will be presented in the combo boxes.
            No idea how to make the combox bigger and perhaps present it without having to click.

            I will be working on a single function to eliminate all this code and just call a single function
            to present data in a grid from SQLitening since this seems to complicated. All the pieces are here.


            Code:
            #INCLUDE "win32api.inc"
            #INCLUDE "sqlitening.inc"
            #INCLUDE "mlg.inc"
            %Grid =101
             
            FUNCTION PBMAIN () AS LONG
              LOCAL hDlg,hGrid AS DWORD, sChoice AS STRING, Cols,x AS LONG
              LOCAL RowColArray () AS STRING,ChoiceArray() AS STRING,ColNameArray() AS ASCIIZ * 256
              '--------------------------------------------------------------------------
              DIALOG NEW 0, "",0,0, 1000, 300, %WS_SYSMENU OR %WS_THICKFRAME TO hDlg
              CONTROL ADD "MYLITTLEGRID",hDlg,%GRID,"",0,0,990,290,%MLG_STYLE OR %WS_TABSTOP
              CONTROL HANDLE hDlg, %GRID TO hGrid
               slOpen "sample.db3"  'open database
              '--------------------------------------------------------------------------
              'Create ChoiceTable and add some choices
              slexe "drop table if exists ChoiceTable"
              slexe "Create Table if not exists ChoiceTable(Choices)"
             
              FOR x = 1 TO 4
                slexe "Insert or replace into ChoiceTable values('Choice" + STR$(x) + "')"
              NEXT
             
              slSelAry "select * from ChoiceTable",ChoiceArray(),"Q9c"
              sChoice = JOIN$(ChoiceArray(),",")
              '--------------------------------------------------------------------------
              cols = slMlgArray("select rowid,* from parts",RowColArray(),ColNameArray())
              '--------------------------------------------------------------------------
              IF cols THEN  'fill column names and combo boxes
                LOCAL zs AS ASCIIZ * 256
                mlg_putex hgrid,RowColArray(),-4,1     'put data into MLG
             
                FOR x = 1 TO cols                      'Give combo choice to every column
                  mlg_FormatColCombo hGrid,x,sChoice
                  zs = ColNameArray(x)
                  sendMessage hGrid,%MLG_SetCell,MAKLNG(0,x),VARPTR(zs)
                NEXT
             
              END IF
              DIALOG SHOW MODAL hdlg
            END FUNCTION
            
            FUNCTION slMLGArray(sql AS STRING,RowsCols() AS STRING,ColumnNameArray() AS ASCIIZ*256) AS LONG
             'create  recordset into ColRow() array, return RowCol() array for MLG
              LOCAL ROW,COL,rows,cols AS LONG, ColsRows() AS STRING
              rem slOpen "sample.db3","C"  'not restricted to this database  1023/15  12:16 PM CST
              slSel sql
              cols = slGetColumnCount
             
              IF cols > 0 THEN
                rows = 100000
                DIM ColsRows(cols,rows) AS STRING
                 REDIM ColumnNameArray(1 TO cols) 'column names
                FOR COL = 1 TO cols
                  ColumnNameArray(COL) = slGetColumnName(COL)
                NEXT
             
                 DO WHILE slGetRow
                  INCR ROW 'actual count
                  IF ROW MOD 100000 = 0 THEN
                     REDIM PRESERVE ColsRows(cols,ROW+100000)
                  END IF
             
                  FOR COL = 1 TO cols
                    ColsRows(COL,ROW) = slf(COL)
                  NEXT
             
                LOOP
             
                rows = ROW 'redim to actual size if need ColsRowsArray
                REDIM RowsCols(rows,cols) 'create array for My Little Grid (rows,cols)
             
                FOR ROW = 1 TO rows
                  FOR COL = 1 TO cols
                    RowsCols(ROW,COL) = ColsRows(COL,ROW)
                  NEXT
                NEXT
                FUNCTION = cols
             
              END IF
             
            END FUNCTION
            Last edited by Mike Doty; 23 Oct 2015, 12:16 PM.
            https://duckduckgo.com instead of google

            Comment


            • #26
              Thanks Mike.

              Phew! A lot of codes just to read a table into an MLG Combobox Column!

              Regards.
              Fredrick Ughimi
              www.meganetsoft.com

              Comment


              • #27
                Fredrick only 3 lines for that:

                slSelAry "select * from ChoiceTable",ChoiceArray(),"Q9c"
                sChoice = JOIN$(ChoiceArray(),",")
                mlg_FormatColCombo hGrid,x,sChoice

                The rest of the program:

                Created the choice table
                Inserted data into choice table
                Read parts table
                Reversed array into (row,col) for MLG
                Placed parts table into an MLG grid
                Placed column names into the grid.
                Displayed the dialog with the grid
                Placed the combo box as an option in all columns
                https://duckduckgo.com instead of google

                Comment


                • #28
                  Hello Mike,

                  Fredrick only 3 lines for that:

                  slSelAry "select * from ChoiceTable",ChoiceArray(),"Q9c"
                  sChoice = JOIN$(ChoiceArray(),",")
                  mlg_FormatColCombo hGrid,x,sChoice
                  I understand. I was actually referring to the slMLGArray function.

                  Thanks.
                  Last edited by Fredrick Ughimi; 23 Oct 2015, 05:47 AM.
                  Fredrick Ughimi
                  www.meganetsoft.com

                  Comment


                  • #29
                    Thank you Mike Doty

                    Hello Mike,

                    This snippet really worked for placing items in the combobox. Thanks.

                    Code:
                    slSelAry "select Status from tblUsers",sUsersArray(),"Q9c"
                    sUsers = JOIN$(sUsersArray(),",")
                    mlg_FormatColCombo hGrid,3,sUsers ' Column 3 only
                    I noticed that you did not use the MLG_GETEX to retrieve the multiple records for the multiple records save. Why?
                    Is it more easier using your own custom routines?

                    Best regards.
                    Fredrick Ughimi
                    www.meganetsoft.com

                    Comment


                    • #30
                      MLG_GetEx hGrid, s() reads from a grid. Please post code you are referring to. I only see reading from SQLitening.
                      https://duckduckgo.com instead of google

                      Comment


                      • #31
                        Hello Mike,

                        I was referring to my question in post #18.

                        Regards.
                        Fredrick Ughimi
                        www.meganetsoft.com

                        Comment


                        • #32
                          Hello Mike,

                          Here, I am trying to get multiple rows from MLG and saving it into SQLitening:

                          Code:
                          Dim sUsersArray() As String
                          MLG_GetEx hUsersGrid, sUsersArray()   
                          Local cols,rows,refresh,x,ROW,COL As Long
                           Dim sUsersArray2(rows,cols) As String     'move data to another array
                            For ROW = 0 To rows
                              For COL = 1 To cols
                               sUsersArray2(ROW,COL) = sUsersArray(COL,ROW)
                              Next
                            Next   
                          slExeBind "Insert into tblUsers values(?" + Repeat$(2, ",?" ) + ")", Join$( sUsersArray2( ), "" ), "V3"
                          I get error -19 invalid string or request. Wonder what I have done wrong here.
                          Fredrick Ughimi
                          www.meganetsoft.com

                          Comment


                          • #33
                            Insert 2-dimensional array into SQLitening

                            Fredrick,
                            slBuildBindDat is needed with each element when using slExeBind.
                            Code:
                            #DIM ALL
                            #INCLUDE "sqlitening.inc"
                            %DropTable = 0
                             
                            FUNCTION PBMAIN () AS LONG 'TwoDimension.bas
                              DIM sRecordSet() AS STRING
                              LOCAL ROW,COL,rows,cols AS LONG, s AS STRING
                            '--------------------------------------------------------------------
                            'Fill 2-dimensional array and bind each element because we are going to write array
                              cols = 3
                              rows = 5
                              DIM TwoDimArray(1 TO cols,1 TO rows) AS STRING
                              FOR ROW = 1 TO rows
                                FOR COL = 1 TO cols
                                  TwoDimArray(COL,ROW) = slBuildBindDat(USING$("r#c#",ROW,COL))
                                NEXT
                              NEXT
                            '--------------------------------------------------------------------
                            'Open database and dynamically create table
                              slOpen "sample.db3","C"
                              IF %DropTable THEN slexe  "drop table if exists t1" 'optionally drop table
                              'Dynamically create the table
                              s= "create table if not exists t1("
                              FOR COL=1 TO cols
                                s+="c"+FORMAT$(COL)+","
                              NEXT
                              ASC(s,LEN(s))=41
                              slexe s
                            '--------------------------------------------------------------------
                            'Dynamically create insert statement and insert 2-dimensional array
                              slexe "begin exclusive"
                              s="Insert into t1 values(?" + REPEAT$(cols-1, ",?" ) + ")"
                              slExeBind s, JOIN$(TwoDimArray(),""), "V" + FORMAT$(cols)
                              slexe "end"
                            '--------------------------------------------------------------------
                            'Display everything with columns delimited using 9/Tab
                              slSelAry "select rowid,* from t1",sRecordSet(),"Q9"
                              ? JOIN$(sRecordSet(),$CR),,"After inserting TwoDimArray()"
                            END FUNCTION
                            Last edited by Mike Doty; 31 Oct 2015, 10:13 PM. Reason: Added comments
                            https://duckduckgo.com instead of google

                            Comment


                            • #34
                              Hello Mike,

                              Thank you. Saves! Again no use of MLG_GetEx. But have you seen the content of the inserted records? Unrecognized characters. Something to do with unicode?
                              Fredrick Ughimi
                              www.meganetsoft.com

                              Comment


                              • #35
                                Code:
                                'Create MyLittleGrid and insert selected rows into Sqlitening table
                                '
                                '1 Create data array RowsCols$(rows,cols)
                                '2 Fill grid using Mlg_Put RowsCols()
                                '3 Dialog Show modeless display grid in dialog
                                '4 Select rows 6 to 10 with Mlg_GetEx and create sBind$(cols,rows)
                                '5 Create/open SQLite/SQLitening database and create table named T1
                                '6 Insert into table T1 using slExeBind rows 6 to 10 from step 4
                                '7 SQLite select all reusing array sBind$()
                                '8 join sBind(),$CR 'show results in table
                                #INCLUDE "win32api.inc"
                                #INCLUDE "mlg.inc"
                                #INCLUDE "sqlitening.inc"
                                %DropTable=1
                                '--------------------------------------------------------------
                                FUNCTION PBMAIN () AS LONG  'mlg.bas
                                  LOCAL hDlg AS DWORD,r,c,cols,rows,GridID,hGrid AS LONG
                                  LOCAL RowsCols(),sBind() AS STRING, QuestionMarks AS STRING
                                  LOCAL RowStart,RowEnd,ColStart,ColEnd AS LONG
                                  GridID = 101
                                  DIALOG NEW 0, "My Little Grid - MLG is extremely fast",,, 520, 370, %WS_SYSMENU OR %WS_THICKFRAME TO hDlg
                                  CONTROL ADD "MYLITTLEGRID",hDlg,GridID,"",0,0,520,370,%MLG_STYLE OR %WS_TABSTOP
                                  CONTROL HANDLE hDlg, GridID TO hGrid
                                1 rows=20:cols=5:CreateRowColArray Rows,cols,RowsCols()
                                2 Mlg_PutEx hGrid,RowsCols(),-4,1  'fill grid
                                3 DIALOG SHOW MODELESS hDlg        'show grid
                                '--------------------------------------------------------------
                                  RowStart=6  'first row to insert
                                  RowEnd=10   'last  row to insert
                                  ColStart=1  'first col to insert
                                  ColEnd=cols 'last  col to insert
                                4 Mlg_GetEx_Bind(hGrid,RowStart,RowEnd,ColStart,ColEnd,sBind())
                                '--------------------------------------------------------------
                                5 slOpen "sample.db3","C"            'open database
                                  IF %DropTable THEN slexe  "drop table if exists t1"
                                  slexe  "create table if not exists t1(c1,c2,c3,c4,c5)"
                                  QuestionMarks = REPEAT$(cols-1,"?,")+"?"
                                  slexe "begin exclusive"
                                6 slexebind "insert into t1 values("+QuestionMarks + ")",JOIN$(sBind(),""),"V" + FORMAT$(cols)
                                  slexe "end"
                                7 slSelAry "select rowid,* from t1 order by rowid",sBind(),"Q9"
                                8 ? JOIN$(sBind(),$CR),,USING$("Inserted rows # to #",RowStart,RowEnd)
                                END FUNCTION
                                '--------------------------------------------------------------
                                SUB CreateRowColArray(rows AS LONG,cols AS LONG,RowsCols() AS STRING)
                                  LOCAL r,c AS LONG
                                  DIM RowsCols(1 TO rows,1 TO cols) 'create data array
                                  FOR r = 1 TO rows
                                    FOR c = 1 TO cols
                                      RowsCols(r,c)=USING$("r#_,c#",r,c)
                                    NEXT
                                  NEXT
                                END SUB
                                '--------------------------------------------------------------
                                FUNCTION Mlg_GetEx_Bind(hGrid AS LONG, _
                                                        rowstart AS LONG, _
                                                        rowend   AS LONG, _
                                                        colStart AS LONG, _
                                                        colEnd   AS LONG, _
                                                        sBind()  AS STRING) AS LONG
                                'Return bound array sBind$() ready for inserting into SQLitening
                                'MyLittleGrid$(rows,cols)
                                'SQLitening$(cols,rows)
                                  LOCAL r,c AS LONG
                                  REDIM FromGrid(RowStart TO RowEnd,ColStart TO ColEnd) AS STRING
                                  Mlg_GetEx hGrid,FromGrid()
                                  REDIM sBind(ColStart TO colEnd, RowStart TO RowEnd) AS STRING 'cols,rows
                                  FOR r = RowStart TO RowEnd 'reverse (rows,cols) to (cols,rows)
                                    FOR c = ColStart TO ColEnd
                                      sBind(c,r)= slBuildBindDat(FromGrid(r,c),"") 'need to bind
                                    NEXT
                                  NEXT
                                END FUNCTION
                                Attached Files
                                Last edited by Mike Doty; 1 Nov 2015, 06:12 AM. Reason: Placed slexebind within a transaction - much faster
                                https://duckduckgo.com instead of google

                                Comment


                                • #36
                                  The same thing demonstrating MLG_GETEX done without needing to reverse the array dimensions to pass the data to SQLitening.
                                  slExeBind passes JOIN$(s(),"") which is a string so just built the string using stringbuilder.
                                  Code:
                                  FUNCTION PBMAIN() AS LONG  'mlg4.bas
                                    LOCAL hDlg,hGrid,r,c,rows,cols AS LONG
                                    LOCAL sb AS ISTRINGBUILDERA
                                    LOCAL RowStart,RowEnd,ColStart,ColEnd AS LONG
                                    LOCAL RowsCols(),sResult() AS STRING
                                    sb = CLASS "StringBuilderA"
                                  '------------------------------------------------------------------------------
                                    DIALOG NEW 0, "MLG4",,, 520, 370, %WS_SYSMENU OR %WS_THICKFRAME TO hDlg
                                    CONTROL ADD "MYLITTLEGRID",hDlg,%GridID,"",0,0,520,370,%MLG_STYLE OR %WS_TABSTOP
                                    CONTROL HANDLE hDlg,%GridID TO hGrid
                                    DIALOG SHOW MODELESS hDlg
                                  '------------------------------------------------------------------------------
                                    rows = 20
                                    cols  =5
                                    DIM RowsCols(1 TO rows,1 TO cols) AS STRING 'create sample data array
                                    FOR r = 1 TO rows
                                      FOR c = 1 TO cols
                                        RowsCols(r,c) = USING$("r#c#",r,c)
                                      NEXT
                                    NEXT
                                    Mlg_PutEx hGrid,RowsCols(),-4,1             'put array to MLG
                                  '------------------------------------------------------------------------------
                                    RowStart=6    'select first row from mlg
                                    RowEnd=10     'select last  row from mlg
                                    ColStart=1    'need entire record
                                    ColEnd=cols
                                    REDIM FromGrid(RowStart TO RowEnd,ColStart TO ColEnd) AS STRING 'dim array
                                    MLG_GETEx hGrid,FromGrid()                                      'fill array
                                    FOR r = RowStart TO RowEnd
                                      FOR c = colStart TO colEnd
                                        sb.add slbuildbindDat(FromGrid(r,c),"") 'add bind string into stringbuilder
                                      NEXT
                                    NEXT
                                  '------------------------------------------------------------------------------
                                    slOpen "sample.db3","C"
                                    slexe  "drop table if exists t1"
                                    slexe  "create table if not exists t1(c1,c2,c3,c4,c5)"
                                    slexe "begin exclusive"
                                    slexebind "insert into t1 values(?,?,?,?,?)",sb.string,"V" + FORMAT$(cols)
                                    slexe "end"
                                    slSelAry "select rowid,* from t1",sResult(),"Q9"
                                    ? JOIN$(sResult(),$CR),,USING$("Inserted rows # to #",RowStart,RowEnd)
                                  END FUNCTION
                                  Last edited by Mike Doty; 3 Nov 2015, 05:51 AM. Reason: sSelected() replaced by FromGrid()
                                  https://duckduckgo.com instead of google

                                  Comment


                                  • #37
                                    Hello Mike,

                                    All great techniques. Thanks.

                                    But the records saved are in unreadable format. For instance, the first record looks like this: (㙲ㅣ㙲㉣ 㙲㍣ 㙲㑣 㙲㕣) I checked the t1 table of the Sample DB using SQLite Expert Personnel. Please do check table t1 at your end. Is it the same with mine?

                                    Kind regards.
                                    Last edited by Fredrick Ughimi; 4 Nov 2015, 09:14 AM.
                                    Fredrick Ughimi
                                    www.meganetsoft.com

                                    Comment


                                    • #38
                                      If slexe "drop table if exists t1" is removed the table correctly grows.
                                      Try changing "sample.db3","C" to something like "test.db3".
                                      I tested again with current version of SQLite3.dll which is 3.9.2.
                                      I have run many times and displays correct results using 32-bit sqlite3.dll
                                      Post #35 even displays the results.

                                      Please drop the table before starting in case the structure of the table has changed.


                                      Tested with and without Jose Roca includes.

                                      If you want to run on my server, please send a private message to get ip and port.

                                      This is the code tested:
                                      Code:
                                      #INCLUDE "win32api.inc"
                                      #INCLUDE "mlg.inc"
                                      #INCLUDE "sqlitening.inc"
                                      %GridID = 101
                                      'My Little Grid selected rows into a SQLitening table
                                      ' ----------------------------------------------------------------------------------
                                      FUNCTION PBMAIN() AS LONG      'MLG4.BAS - name for later reference
                                        LOCAL RowsCols() AS STRING   'Mlg_PutEx hGrid,RowsCols(),-4,1 fills MLG grid
                                        LOCAL sResult() AS STRING '  'SQLitening select statement results at end
                                        LOCAL hDlg,hGrid,r,c,rows,cols,RowStart,RowEnd,ColStart,ColEnd AS LONG
                                        LOCAL sb AS ISTRINGBUILDERA  'grid elements selected for adding to SQLite table
                                        sb = CLASS "StringBuilderA"  'sb.add slbuildbindDat(FromGrid(r,c),"")
                                      ' ----------------------------------------------------------------------------------
                                        DIALOG NEW 0, "MLG4",,, 520, 370, %WS_SYSMENU OR %WS_THICKFRAME TO hDlg
                                        CONTROL ADD "MYLITTLEGRID",hDlg,%GridID,"",0,0,520,370,%MLG_STYLE OR %WS_TABSTOP
                                        CONTROL HANDLE hDlg,%GridID TO hGrid
                                        DIALOG SHOW MODELESS hDlg 'callback would be added in a normal program
                                      '----------------------------------------------------------------------------------
                                        rows = 20  'Create sample data for My Little Grid
                                        cols  =5
                                        DIM RowsCols(1 TO rows,1 TO cols) AS STRING '1-based rows and 1-based columns
                                        FOR r = 1 TO rows                           'grid row loop
                                          FOR c = 1 TO cols                         'grid column loop
                                            RowsCols(r,c) = USING$("r#c#",r,c)      'put sample data into element
                                          NEXT c
                                        NEXT r
                                        Mlg_PutEx hGrid,RowsCols(),-4,1             'put array to grid
                                      '----------------------------------------------------------------------------------
                                        RowStart=6   'block of MLG grid rows to later write to SQLitening table
                                        RowEnd=10
                                        ColStart=1
                                        ColEnd=cols
                                        REDIM FromGrid(RowStart TO RowEnd,ColStart TO ColEnd) AS STRING
                                        MLG_GETEx hGrid,FromGrid()                  'fill array with grid elements
                                        FOR r = RowStart TO RowEnd                  'add each to sb object
                                          FOR c = colStart TO colEnd                'bind=special formats for SQLitening
                                            sb.add slbuildbindDat(FromGrid(r,c),"") 'bind each column, "" nothing special
                                          NEXT
                                        NEXT
                                      ' Open database/create table if not exists. write grid elements to a table
                                        slOpen "sample.db3","C"
                                        'slexe  "drop table if exists t1" 'this is optional
                                        slexe  "create table if not exists t1(c1,c2,c3,c4,c5)" '5-columns
                                        slexe "begin exclusive" '1-lock, MUCH faster than lock database on each insert
                                        slexebind "insert into t1 values(?,?,?,?,?)",sb.string,"V" + FORMAT$(cols)
                                        slexe "end" 'end transaction/unlock database
                                        slSelAry "select rowid,* from t1",sResult(),"Q9" 'Q9= 1-dim, $TAB delimited columns
                                        ? JOIN$(sResult(),$CR),,USING$("Inserted rows # to #",RowStart,RowEnd)
                                      END FUNCTION
                                      Last edited by Mike Doty; 4 Nov 2015, 09:32 AM. Reason: Tested with and without Jose Roca includes
                                      https://duckduckgo.com instead of google

                                      Comment


                                      • #39
                                        I updated m SQLite Expert (don't use) and get garbage displaying the table. I started a function executor written in PowerBASIC and works fine.
                                        https://duckduckgo.com instead of google

                                        Comment


                                        • #40
                                          Does this work?
                                          If so, there is an issue with SQLite Expert.
                                          Code:
                                          #INCLUDE "sqlitening.inc"
                                          FUNCTION PBMAIN () AS LONG
                                            DIM s() AS STRING
                                            slopen "sample.db3"
                                            slSelAry "select rowid,* from t1",s(),"Q9c"
                                            ? JOIN$(s(),$CR)
                                          END FUNCTION
                                          https://duckduckgo.com instead of google

                                          Comment

                                          Working...
                                          X