SQLitening Support Forum

This problem is probably a function of my beginner status with PB9. However, since it concerns my use of SQLitening as well , I'm posting it here first.

Briefly, when running an SQLlite transaction involving the import of several million records from a .csv file, PB seems to start a separate thread for the transaction loop - at least that is my assumption since two instances of the application appear in Task Manager. Just before the 'BEGIN TRANSACTION' statement, I disable various controls and enable an 'Abort' button. I have a 'DoEvents' statement in the loop to ensure that any abort button click will be actioned - and it IS actioned. It initiates the following code:


slExe "END TRANSACTION"
slClose
Close #1

         

The #1 being the input .csv file. The requirement is to be able to gracefully abort an import-in-progress.

Problem is that SQLite returns an error saying that there is no transaction in progress and there is no open SQL database, when there most certainly are both  since the transaction loop continues to run to successful completion.

I suspect it has to do with either the separate thread or 'sub-classing' (or both) - with which I am as yet in total ignorance.

Any help / observations welcome


Peter Presland

Peter,

We'd need to see much more code to make an accurate assessment, but AFAIK, and I'm 99% sure of this, PB does not start any subsequent threads on its own regardless of the operation.  Sub-classing wouldn't play a role in this either as far as I can see.

My gut reaction is that the action taken when the 'abort' button is pressed is not closing down the files and process in the way you believe it is, which is inadvertently closing the SQL database before the "END TRANSACTION" statement is reached.

Well Joe, you asked for it 

I built the form with EZGUI and have used a good few of the EZ commands/functions. I know you are familiar with them from posts on other boards etc. Also, please excuse the sloppy/dirty coding - my need is for something that works rather than elegance - and this does work - like greased lightening too compared to farting around with MS Access as has been my wont for a good few years. Nonetheless, if I'm doing anything obviously silly or 'makeWork' a steer would also be appreciated.

It's just the Abort button that I'm having a problem with.

As you can see the guts of the processing is done following a click event in a single Sub.

The abort button is in a separate Sub.



SUB TTTMAIN_GO_BUTTON_Events( MyID&, CMsg&, CVal&, Cancel&)
     SELECT CASE CMsg&
         CASE %EZ_Click
              IF EZ_GetText("TTTMAIN", 155) = "" THEN
                 EZ_MsgBox("TTTMAIN", "{!}    No Source File    ", "ATTENTION", "OK")
                 EZ_SetFocus  "TTTMAIN",  155
                 EXIT SELECT
              END IF
              IF EZ_GetText("TTTMAIN", 165) = "" THEN
                 EZ_MsgBox("TTTMAIN", "{!}    No SQL Database File    ", "ATTENTION", "OK")
                 EZ_SetFocus  "TTTMAIN",  165
                 EXIT SELECT
              END IF
              IF EZ_GetText("TTTMAIN", 175) = "" THEN
                 EZ_MsgBox("TTTMAIN", "{!}    No SQL Table    ", "ATTENTION", "OK")
                 EZ_SetFocus  "TTTMAIN",  175
                 EXIT SELECT
              END IF
              LOCAL IFile$, Fld$               ' For Input file name and field names variables
              LOCAL x&                         ' for size of input file
              LOCAL t&                         ' for timer
              DIM Fld(7) AS STRING
              IFile$ = EZ_GetText("TTTMAIN", 155)
              OPEN IFile$ FOR INPUT AS #1
              x& = LOF(#1)/1000000             ' Size of input file in Kb
'
              INPUT #1, Fld(0), Fld(1), Fld(2), Fld(3), Fld(4), Fld(5), Fld(6), Fld(7)
              IF Fld$(0) <> "Date" THEN     'Test input file
                 CLOSE #1
                 EZ_MsgBox("TTTMAIN", "{!}  Input file stucture not useable   ", "ATTENTION", "OK")
                 EXIT SELECT
              END IF
              IF EZ_MsgBox("TTTMAIN", "{?}  Parameters appear OK   "+ _
                                   "||   Do you wish to proceed?   "+ _
                                   "||   File Size is: "+ STR$(X&), "ATTENTION", "YN") = 0 THEN
                 CLOSE #1
                 EXIT SELECT
              END IF
'
              EZ_ShowC    "TTTMAIN", 115, 115  ' Show Abort button
              EZ_HideC    "TTTMAIN", 100, 110  ' Hide Go, Clear & Exit Buttons
              EZ_DisableC "TTTMAIN", 100       ' Clear Button
              EZ_DisableC "TTTMAIN", 105       ' Go button
              EZ_DisableC "TTTMAIN", 110       ' Exit button
              EZ_DisableC "TTTMAIN", 160       ' File input select button
              EZ_DisableC "TTTMAIN", 170       ' SQL select button
              EZ_ShowC    "TTTMAIN", 120,150   ' Show progress bar + messages etx
'
              LOCAL DbName$, TblName$, Date1$, Date2$, Time1$, Time2$, Price1$, Ssize$
              LOCAL Bid$, BidSize$,Ask$,AskSize$,BA$
              LOCAL B1$, Bs1$, A1$, As1$, Ci&, Co&
              DbName$ = EZ_GetText("TTTMAIN", 165)
              TblName$ = EZ_GetText("TTTMAIN", 175)
              slOpen (DbName$,"C")
              slExe "Create Table "+TblName$+" (Date INTEGER,Time TEXT,Price REAL,Size INTEGER, _
                                                Bid REAL,BidSize INTEGER,Ask REAL,AskSize INTEGER,BA TEXT)"
'
              slExe "BEGIN IMMEDIATE TRANSACTION"
              LOCAL TStart&, TEnd&                                        ' Start & end time values
              TStart& = TIMER
              LOCAL NSeconds&, ESeconds&, EMins&                          ' To calculate elapsed time
              Ci& = 0
              Co& = 0
              t&  = 0
              WHILE ISFALSE EOF(1)
                  INPUT #1, Date1$, Time1$, Price1$, Ssize$, Bid$, BidSize$, Ask$, AskSize$
                  ci& += 1
                  t&  += 1
                  IF Price1$ = "" THEN B1$ = Bid$: Bs1$ = BidSize$ : A1$ = Ask$ : As1$ = AskSize$
                  IF VAL(Price1$) <= VAL(B1$) THEN
                     BA$ = "B"
                  ELSEIF VAL(Price1$) >= VAL(A1$) THEN
                     BA$ = "A"
                  ELSE
                     BA$ = "I"
                  END IF
                  IF Price1$ <> "" THEN
                      Date2$ = MID$(Date1$,7,4) & MID$(Date1$,4,2) & MID$(Date1$,1,2)
                      Time2$ = MID$(Time1$,1,2) & MID$(Time1$,4,2) & MID$(Time1$,7,2)
                      slExe "Insert into "+TblName$+" values ("+"'"+ Date2$ +"','"+ Time2$ +"','"+ Price1$ _
                            +"','"+ Ssize$ +"','"+ B1$ +"','"+ Bs1$ +"','"+ A1$ +"','"+ As1$ +"','"+ BA$ +"')"
'
                      Co& += 1
                  END IF
                  EZ_DoEvents  1                                  ' clear windows messages
'
                  IF t& > 1000  THEN
                    TEnd& = TIMER - TStart&
                    EZ_SetPBar "TTTMAIN", 120, 0, x&, TEnd&       ' This assumes 1 Mb/Sec processed - more work needed
                    EZ_SetText "TTTMAIN", 130, STR$(Co& / 1000)   ' Records written text
                    TEnd& = TIMER
                    NSeconds& = TEnd& - TStart&                   ' elapsed time in seconds
                    EMins& = Nseconds& \ 60                       ' minutes portion of elapsed time
                    ESeconds& = NSeconds& - (EMins& * 60)         ' seconds portion of elapsed time
                    EZ_SetText "TTTMAIN", 135, STR$(Emins&)+" Min"+STR$(ESeconds&)+" Sec"
                    t& = 0
                  END IF
'
              WEND
              slExe "END TRANSACTION"
              slClose
              CLOSE #1
              TEnd& = TIMER
              NSeconds& = TEnd& - TStart&                   ' elapsed time in seconds
              EMins& = Nseconds& \ 60                       ' minutes portion of elapsed time
              ESeconds& = NSeconds& - (EMins& * 60)         ' seconds portion of elapsed time
              EZ_MsgBox("TTTMAIN", "{!}  File Conversion Complete||    Records processed: "+STR$(Ci&)+ _
                                   "    ||    Records written: "+ STR$(Co&)+ _
                                   "    ||    Elapsed time: "+STR$(Emins&)+" Min"+STR$(ESeconds&)+" Sec", "ATTENTION", "OK")
'
              EZ_HideC   "TTTMAIN", 115, 115  ' Hide Abort button
              EZ_ShowC   "TTTMAIN", 100, 110  ' Show Go, Clear & Exit Buttons
              EZ_EnableC "TTTMAIN", 100       ' Clear Button
              EZ_EnableC "TTTMAIN", 105       ' Go button
              EZ_EnableC "TTTMAIN", 110       ' Exit button
              EZ_EnableC "TTTMAIN", 160       ' File input select button
              EZ_EnableC "TTTMAIN", 170       ' SQL select button
              EZ_HideC   "TTTMAIN", 120,150   ' Hide progress bar + messages etc
'
          CASE ELSE
     END SELECT
END SUB
'
SUB TTTMAIN_EXIT_BUTTON_Events( MyID&, CMsg&, CVal&, Cancel&)
     SELECT CASE CMsg&
          CASE %EZ_Click
              EZ_UnloadForm   "TTTMAIN"
          CASE ELSE
     END SELECT
END SUB
'
SUB TTTMAIN_ABORT_BUTTON_Events( MyID&, CMsg&, CVal&, Cancel&)
     SELECT CASE CMsg&
          CASE %EZ_Click
                slExe "END TRANSACTION"
                slClose
                CLOSE #1
          CASE ELSE
     END SELECT
END SUB




Peter Presland

Peter,

Ok, what is happening (I'm pretty sure) is that the program flows to the abort sub when the button is clicked, which closes the SL connection BUT the flow returns to the main processing loop without any knowledge that the abort was set, and therefore the slite database is now closed.

What I do is use the EZ_SETUSERDATA flag (although you could use a global variable instead) to flag "Ok or Abort".  I initialize the flag to zero then in the Abort SUBroutine I set the flag to non-zero.  So if the user clicks 'Abort' the subroutine sets the flag.  Then in the primary loop, usually just before the WEND, I check the abort flag.  If its zero, continue on, if not, exit the loop, clean up, and leave.

The way you're doing it now, it doesn't appear that the main processing loop is aware that the abort has been executed so it tries to continue on its merry way.

Thanks a bunch Joe - Much appreciated. 

That worked a treat. Although I was certain that when it first happened, the transaction was NOT ended and the process went through to completion, even displaying the end message box way after the main form had unloaded. I must have changed something in between though because it's fine now.

I feel I am making real progress at last.


Peter Presland