• Welcome to SQLitening Support Forum. Please login.
 
January 17, 2022, 09:53:25 PM

News:

Welcome to the SQLitening support forums!


Scripting vs coding (list all tables with a single statement)

Started by mikedoty, November 16, 2008, 06:46:13 PM

Previous topic - Next topic

mikedoty

'Script vs code.

'Writes directly to disk eliminating having to read recordset.
'SQLite's command line processor (CLP) "sqlite3.exe" at work.
'This example takes the script "input.txt" and returns a tab delimited file.
'The type of output returned ".mode csv" ".mode html", ...   Use .help for help.
'See Definitive Guide to SQLite page 34 CLP in Shell Mode.

'Sqlite's sqlite3.exe program can accept SQL commands and . commands.
'This technique can be used instead of writing code.

'Using this technique a grid can be virtually filled as needed without
'having to fill an array or read through a recordset.

'This technique also gives easy access to schemas, pragmas, unions without coding.
'Can also be used for scheduled backups, import/export and many others.

'This script shows all tables in the database using UNION.
SELECT name FROM
   (SELECT * FROM sqlite_master UNION ALL
       SELECT * FROM sqlite_temp_master)
          WHERE type='table'
             ORDER BY name;

or if you don't need the temporary tables just pass:
.TABLES as input


#COMPILE EXE
#DIM ALL

DECLARE SUB CLP(sDataBaseName   AS STRING,  _
                sBatchFile      AS STRING,  _
                sInputFile      AS STRING,  _
                sOutputFile     AS STRING,  _
                sSqlite3Pgm     AS STRING)
               
DECLARE SUB ExpandTabFile(sOutputFile AS STRING)

FUNCTION PBMAIN AS LONG
   LOCAL h&, sDataBase$, sBatchFile$, sInputFile$, sOutputFile$,sCLPProgramName$
   sDatabase       = "test.db"
   sBatchFile      = "easy.bat"
   sInputFile      = "input.txt"
   sOutPutFile     = "output.txt"
   sCLPProgramName = "sqlite3.exe"
   CHDIR "\sqlite"      'otherwise use full paths
   KILL sOutputFile:ERRCLEAR  'get rid of previous recordset
   h = FREEFILE
   OPEN  sInputFile FOR OUTPUT AS #h
   PRINT #h, ".echo off
   PRINT #h, ".mode columns
   PRINT #h, ".output output.txt
   PRINT #h, "SELECT name FROM
   PRINT #h, "  (SELECT * FROM sqlite_master UNION ALL
   PRINT #h, "     SELECT * FROM sqlite_temp_master)
   PRINT #h, "       WHERE type='table'
   PRINT #h, "         ORDER BY name;
   PRINT #h, " .exit"
   CLOSE #h
   CALL CLP(sDataBase,sBatchFile,sInputFile,sOutputFile,sCLPProgramName)
END FUNCTION

SUB CLP(sDataBaseName AS STRING, _
        sBatchFile    AS STRING, _
        sInputFile    AS STRING, _
        sOutputFile   AS STRING, _
        sSqlite3Pgm   AS STRING)

  LOCAL buffer AS STRING, h AS LONG

  'select database
  h = FREEFILE
  OPEN sBatchFile FOR OUTPUT AS #h
  IF ERR THEN ? "Could not create: " + sBatchFile:EXIT SUB
  PRINT #h, sSqlite3Pgm + " "  + sDataBaseName + " < " + sInputFile
  CLOSE #h

  'execute
  SHELL sBatchFile
  CALL ExpandTabFile(sOutputFile) 'expand tab file
 
  'see results or load into your grid/program
  SHELL "notepad " + sOutputFile
END SUB

SUB ExpandTabFile(sOutputFile AS STRING)
  'Optionally modify TAB file with $CRLF after each line
  LOCAL h AS LONG, sBuffer AS STRING
  h = FREEFILE
  OPEN sOutputFile FOR BINARY AS #h
  IF ERR THEN
    ? "Error using output file: " + sOutputFile + " error" + STR$(ERR)
    EXIT SUB
  END IF
  sBuffer = SPACE$(LOF(h))
  GET #h, 1, sBuffer
  'REPLACE $LF with "{END}" + $CRLF in buffer 'try it and see results
  REPLACE $LF WITH $CRLF IN sBuffer
  REPLACE CHR$(195,138) WITH "" IN sBuffer
  'REPLACE $TAB WITH "{TAB}" IN BUFFERr       'try it
  SEEK #h, 1  'get to byte 1
  SETEOF #h   'truncate
  PUT #h, 1, sBuffer  'rewrite new file
  CLOSE #h
END SUB

'results: 'Customers'LineItems'Orders'Parts'sqlite_sequence


mikedoty

The commands can be placed into an input file and
you can execute this:
sqlite3 test.db
.read input.txt
.exit