• Welcome, Guest. Please login.
 
September 17, 2019, 10:33:46 am

News:

Welcome to the SQLitening support forums!


Function to process/display multiple SELECT and non-SELECTS?

Started by cj, December 29, 2012, 01:43:50 pm

Previous topic - Next topic

cj

I have a a program that executes all statements in %LOWER_TEXTBOX and attempts to display in %UPPER_TEXTBOX.
My logic  processes all the statements, but can't handle multiple/mixed SELECT and NON-SELECT statements.
They do execute because if an error is in any statement the transaction fails.

If this logic is figured out,  scripts can be sent to perform tasks and eliminate a lot of hard-coding.
Later down the road it might be possible to pre-process the scripts to handle LOOPS, etc.
Trying to get all datasets to display in the upper text box or to a text file


Data in %LOWER_TEXTBOX to execute:

Begin;
Drop Table If Exists Patient;
Create Table If Not Exists Patient(
  PatientRow    INTEGER PRIMARY KEY,
  PatientClient INTEGER,
  PatientName   TEXT);
Create Index If Not Exists PatientClient_idx On Patient(PatientClient);
INSERT INTO Patient values(null,1,'Patient One');
INSERT INTO Patient values(null,2,'Patient Two');
SELECT  last_insert_rowid();
SELECT * FROM Patient;
End


SUB Execute(hDlg AS DWORD, s AS STRING)
CONTROL GET TEXT hDlg, %LOWER_TEXTBOX to s
IF UCASE$(LEFT$(s,6)) = "SELECT" OR UCASE$(LEFT$(s,6)) = "PRAGMA" OR UCASE$(LEFT$(s,7)) = "EXPLAIN" THEN
  result = slSel(s)
  'see if error or no lines returned
  if result then EXIT FUNCTION
  columns = slGetColumnCount
  if columns = 0 then EXIT FUNCTION

  DO WHILE slGetRow
    FOR x = 1 TO Columns
      sb.add  slf(x) + Delimiter
    NEXT
    sb.add $CRLF
  LOOP
  CONTROL SET TEXT hDlg, %TEXT_UPPER_TEXTBOX, sb.string 

ELSE
  llChangeCount = slGetChangeCount("T")
  result = slEXE(s)
  IF result = 0 THEN
     llChangeCount = slGetChangeCount("T") - llChangeCount
     CONTROL SET TEXT hDlg, %LBL_RESULTS, "Changes" + STR$(llChangeCount)
     'slexe "END"
     IF llChangeCount THEN
        Selectall hDlg
     ELSE
        ClearTop hDlg
        'LISTBOX ADD hDlg, %LISTBOX, "No changes"
        CONTROL SET TEXT hDlg, %TEXT_UpperTextBox, "No changes"
     END IF
  ELSE  'error occurred so rollback
    llChangeCount = 0
    CONTROL SET TEXT hDlg, %LBL_RESULTS, slGetError
    slexe "ROLLBACK","EO" 'forget any error
    SelectAll hDlg
    'LISTBOX ADD hDlg, %LISTBOX, "ROLLBACK"
    CONTROL SET TEXT hDlg, %TEXT_UpperTextBox, "ROLLBACK"
    BEEP
  END IF
END IF
END SUB



Just noticed that SQLite supports comments

Begin;     -- Comment to the end of the line
Drop Table If Exists Patient;    /* C style comment */
End