Executing SQL query using SQLite. Windows Batch Script Example

This demo will show how to use SQLite inside Windows batch script.

DB Engine: SQLite 3.7.4
Script: Windows Command Prompt, Batch Script
OS: Microsoft Windows XP [Version 5.1.2600]

Screen Shots:

First Run

Insert Statement

Select Statement

Update Statement

Delete Statement

Custom SQL

Var Dump at script end

Source Code:

@echo off
:: Author: swapnil khare 5 at gmail c o m
setlocal
:: Global Variables here
set _debug=1
set "_db=q.db"
set "_table=qdemocmd"
set "_engine=sqlite3.exe"

:: SQL Statements
set "_testSql=select * from %_table% limit 0;"
set "_create=create table %_table% ( id integer primary key asc autoincrement, timestamp text default (datetime()), data text);"
set "_select=select * from %_table%;"
set "_insert=insert into %_table% ( data ) values ('%_data%');"
set "_update=update %_table% set data='' where id=%_id%;"
set "_delete=delete from %_table% where id=%_id%;"

:setup
 :: check if sqlite exist or not
 if not exist "sqlite3.exe" (
  @echo.Error: sqlite3.exe not found in current directory.
  goto:end
 )
 :: check table exist or not, if not then create
 call:executeSQL %_testSql%
 if %errorlevel% gtr 0 (
  @echo.Table may not exist, attempting create statement
  call:setupData
 )

:begin
 if _debug equ 0 (
  title Demo SQLite within Windows Shells
 )

:loop
 set _contd=
 set _input=
 ::
 echo.Demo SQLite within Windows Shells
 call:printMenu
 set /p _input="Enter your choice:"
 if "%_input%"=="c" call:createData
 if "%_input%"=="r" call:retrieveData
 if "%_input%"=="u" call:updateData
 if "%_input%"=="d" call:deleteData
 if "%_input%"=="s" call:customSQL
 if "%_input%"=="e" goto:end
 ::
 set /p _contd="Do You Want to Continue (y/n):"
 if /i "%_contd%"=="y" goto:loop

:end
 if defined _debug (
  @echo.
  set _
 )
 endlocal
 goto:eof
 exit/b
 @echo.Done, if still active, then it is safe to close.
 pause

:printMenu
 echo.*** * * * * * ***
 echo.
 echo.Available Options:
 echo.
 echo. C. Create Data Record
 echo. R. Retrieve Data
 echo. U. Update Data
 echo. D. Delete Data
 echo. E. Exit
 echo. S. Custom SQL Statement
 echo.
 echo.*** * * * * * ***
 goto:eof

:createData
 set _data=
 @echo.Create Data Record
 @echo.Enter Data and press return to save.
 set /p _data=
 set "_sql=insert into %_table% ( data ) values ('%_data%');"
 call:executeSQL  %_sql%
 goto:eof

:retrieveData
 @echo.Retrieve Data
 call:executeSQL %_select%
 goto:eof

:updateData
 set _id=
 set _data=
 @echo.Update Data
 set /p _id="Enter Id:"
 @echo.Enter Data and press return to save.
 set /p _data=
 set "_sql=update %_table% set data='%_data%' where id=%_id%;"
 call:executeSQL  %_sql%
 goto:eof

:deleteData
 @echo.Delete Data
 set _id=
 set /p _id="Enter Id:"
 set /p _tmp="Are you sure (y/n):"
 if /i "%_tmp%"=="y" (
  set "_sql=delete from %_table% where id=%_id%;"
  call:executeSQL %_sql%
 ) else @echo.Delete cancelled.
 goto:eof

:setupData
 @echo.Setup: Database, Data Installation.
 call %_engine% %_db% "%_create%"
 goto:eof

:executeSQL
 @echo.Executing SQL: %*
 :: -html -csv -list -separator 'x' -line -header
 call %_engine% -header -column %_db% "%*"
 if %errorlevel% gtr 0 @echo.Execution Failure
 goto:eof

:customSQL
 @echo.Custom SQL
 @echo.Enter SQL Statement and press return.
 set /p _tmp=
 call:executeSQL %_tmp%
 goto:eof

Download & Setup Instruction:

1. Download command-line shell SQLite 3.7.4 from this http://www.sqlite.org/ or try direct link.
2. Extract executable sqlite3.exe using any popular zip extractor like 7zip, winzip or inbuilt Windows extraction utility, inside a new folder (recommended), say "sqlite_demo_bat".
3. Create new text file inside folder "sqlite_demo_bat", rename this as demo.cmd (or demo.bat), if you are having trouble renaming file extension follow this How to Change a File Extension in Windows.
4. Paste above source code, in file and save it.
5. Run: Double click on demo.cmd (or demo.bat).

Note: For this demo to run properly, sqlite3.exe and demo.cmd (or demo.bat) should be in same directory.