Jump to content
Sign in to follow this  
ihudson

How do I add variables to a sqlite SELECT statement?

Recommended Posts

I'm just learning sqlite3 forgive me if my concepts are not right, anyway I'm trying to add two variables to a SELECT statement:

The variables would be $start and $end that the user would choose from a Combobox. The database holds 1 table (Distances) that has the distances between two locations ($start and $end).

In the code that follows (if possible) how can I place $start where "Midlothian MS" is and $end where "School Board" is in the Select statement? I've tried several types of concatenation but no joy.

Here is the schema:

CREATE TABLE fulldata (id integer primary key autoincrement, Start text,End text

,Distance integer);

_SQLite_Startup()
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)
_SQLite_Open('distance.db') ; open Database
_SQLite_QuerySingleRow(-1, "SELECT distance FROM fulldata WHERE Start = 'Midlothian MS' AND End = 'School Board';", $aRow)

_SQLite_Close()
_SQLite_Shutdown()
While 1
$nMsg = GUIGetMsg()
Switch $nMsg
Case $GUI_EVENT_CLOSE
Exit
Case $Button1
MsgBox(0, "The distance is:",$aRow[0])
Case $Button2
_ArrayToClip($aRow, 0) ;send result to clipboard

EndSwitch
WEnd

Thanks for your help,

ihudson

Share this post


Link to post
Share on other sites

Safer version:

_SQLite_QuerySingleRow(-1, "SELECT distance FROM fulldata WHERE Start = " & _SQLite_FastEscape($start) & " AND End = " & _SQLite_FastEscape($end) & ";", $aRow)
Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

_SQLiteFastEscape() _SQLite_FastEscape()

[edit: fixed.]

Edited by MvGulik

"Straight_and_Crooked_Thinking" : A "classic guide to ferreting out untruths, half-truths, and other distortions of facts in political and social discussions."
"The Secrets of Quantum Physics" : New and excellent 2 part documentary on Quantum Physics by Jim Al-Khalili. (Dec 2014)

"Believing what you know ain't so" ...

Knock Knock ...
 

Share this post


Link to post
Share on other sites

Correct, thanks.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By dmob
      So I am trying to implement an archive system of sorts for my (SQLite) DB app. I wrote a function to attach a separate (archive) DB and
      sync the columns with main DB. If archive DB file does not exist, create file with _SQLiteOpen then close the file (and thus connection) with SQLite_Close.
      This works as intended, however, after the create operation, all subsequent _SQLite_* functions returned a "Library misuse error".
      After a little digging I found the problem in the _SQLite_Close function: it clears the "last opened database" handle even when there still is a live
      DB connection open. All other functions then "think" there is no DB connection active. I hacked two functions in the UDF for a quick fix:

      In _SQLite_Close: Change
      ... $__g_hDB_SQLite = 0 __SQLite_hDel($__g_ahDBs_SQLite, $hDB) Return $iRval[0] to:
      $__g_hDB_SQLite = __SQLite_hDel($__g_ahDBs_SQLite, $hDB) Return $iRval[0] and in Func __SQLite_hDel changed
      Func __SQLite_hDel(ByRef $ahLists, $hGeneric) Local $iElement = _ArraySearch($ahLists, $hGeneric) If $iElement > 0 Then _ArrayDelete($ahLists, $iElement) EndFunc ;==>__SQLite_hDel to:
      Func __SQLite_hDel(ByRef $ahLists, $hGeneric) Local $iElement = _ArraySearch($ahLists, $hGeneric) If $iElement > 0 Then _ArrayDelete($ahLists, $iElement) Return $ahLists[UBound($ahLists)-1] ; Return last opened db EndIf Return 0 EndFunc ;==>__SQLite_hDel so it preserves last opened DB again.
       
      My archive function now works great
      I'm not sure if this should be classified as a bug, but I believe so...
      Hope this helps someone before
    • By argumentum
      #include <SQLite.au3> ;-- When SQLite is compiled with the JSON1 extensions it provides builtin tools ;-- for manipulating JSON data stored in the database. ;-- This is a gist showing SQLite return query as a JSON object. ;-- https://www.sqlite.org/json1.html Example() Func Example() _SQLite_Startup() ; "<your path>\sqlite3.dll", False, 1) ; https://www.autoitscript.com/autoit3/docs/libfunctions/_SQLite_Startup.htm _SQLite_Open() ; ...if you can not run this due to errors, get the latest DLL from https://www.sqlite.org/ If _SQLite_Exec(-1, "CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, full_name TEXT NOT NULL, email TEXT NOT NULL, created DATE NOT NULL );") Then Return 4 If _SQLite_Exec(-1, 'INSERT INTO users VALUES ' & _ '(1, "Bob McFett", "bmcfett@hunters.com", "32-01-01"),' & _ '(2, "Angus O''Vader","angus.o@destroyers.com", "02-03-04"),' & _ '(3, "Imperator Colin", "c@c.c", "01-01-01");') Then Return 5 ; -- Get query data as a JSON object using the ; -- json_group_object() [1] and json_object() [2] functions. _SQLite_GetTable2d_ArrayToConsole("SELECT" & _ " json_group_object(" & _ " email," & _ " json_object('full_name', full_name, 'created', created)" & _ " ) AS json_result" & _ " FROM (SELECT * FROM users WHERE created > ""02-01-01"");") ; {"bmcfett@hunters.com":{"full_name":"Bob McFett","created":"32-01-01"},"angus.o@destroyers.com":{"full_name":"Angus O'Vader","created":"02-03-04"}} ; -- Get query data as a JSON object using the ; -- json_group_array() function to maintain order. _SQLite_GetTable2d_ArrayToConsole("SELECT" & _ " json_group_array(" & _ " json_object('full_name', full_name, 'created', created)" & _ " ) AS my_json_result_OrAnythingReally" & _ " FROM (SELECT * FROM users ORDER BY created);") ; [{"full_name":"Imperator Colin","created":"01-01-01"},{"full_name":"Angus O'Vader","created":"02-03-04"},{"full_name":"Bob McFett","created":"32-01-01"}] ;-- Links ;-- [1] https://www.sqlite.org/json1.html#jgroupobject ;-- [2] https://www.sqlite.org/json1.html#jobj ; example found at https://gist.github.com/akehrer/481a38477dd0518ec0086ac66e38e0e2 EndFunc ;==>Example Func _SQLite_GetTable2d_ArrayToConsole($sSQL, $hDB = -1) Local $aResult, $iRows, $iColumns If _SQLite_GetTable2d($hDB, $sSQL, $aResult, $iRows, $iColumns) Then ConsoleWrite("! SQLite Error: " & _SQLite_ErrCode($hDB) & @CRLF & "! " & _SQLite_ErrMsg($hDB) & @CRLF) Else _SQLite_Display2DResult($aResult) EndIf ConsoleWrite(@CRLF) EndFunc ;==>_SQLite_GetTable2d_ArrayToConsole Based on this example, you can build your own query. 
      The code has all the explanations.
      Enjoy  
    • By argumentum
      #include <SQLite.au3> ;~ #include <SQLite.dll.au3> Local $hQuery, $aRow, $aNames _SQLite_Startup() ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF) _SQLite_Open() ; open :memory: Database _SQLite_Exec(-1, "CREATE TABLE aTest (A,B int not null unique ,C text);") _SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('c','2','World');") _SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('b','3',' ');") _SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('a','1','Hello');") _SQLite_Query(-1, "SELECT _ROWID_,* FROM aTest ORDER BY a;", $hQuery) _SQLite_FetchTypes($hQuery, $aNames) ; Read out Column Types ConsoleWrite(StringFormat(" %-10s %-10s %-10s %-10s ", $aNames[0], $aNames[1], $aNames[2], $aNames[3]) & @CRLF) _SQLite_FetchNames($hQuery, $aNames) ; Read out Column Names ConsoleWrite(StringFormat(" %-10s %-10s %-10s %-10s ", $aNames[0], $aNames[1], $aNames[2], $aNames[3]) & @CRLF) While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK ConsoleWrite(StringFormat(" %-10s %-10s %-10s %-10s ", $aRow[0], $aRow[1], $aRow[2], $aRow[3]) & @CRLF) WEnd _SQLite_Exec(-1, "DROP TABLE aTest;") _SQLite_Close() _SQLite_Shutdown() ; Output: ; INTEGER int text ; rowid A B C ; 3 a 1 Hello ; 2 b 3 ; 1 c 2 World Func _SQLite_FetchTypes($hQuery, ByRef $aTypes) Dim $aTypes[1] If __SQLite_hChk($hQuery, 3, False) Then Return SetError(@error, 0, $SQLITE_MISUSE) Local $avDataCnt = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_column_count", "ptr", $hQuery) If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; DllCall error If $avDataCnt[0] <= 0 Then Return SetError(-1, 0, $SQLITE_DONE) ReDim $aTypes[$avDataCnt[0]] Local $avColName For $iCnt = 0 To $avDataCnt[0] - 1 $avColName = DllCall($__g_hDll_SQLite, "wstr:cdecl", "sqlite3_column_decltype16", "ptr", $hQuery, "int", $iCnt) If @error Then Return SetError(2, @error, $SQLITE_MISUSE) ; DllCall error $aTypes[$iCnt] = $avColName[0] Next Return $SQLITE_OK EndFunc ;==>_SQLite_FetchTypes  If you wanna build a proper JSON string, you may want to know if is {"int":123} or {"text":"123"}
      and for that, this can help, obviously only when declared in the SQLite table.
    • By argumentum
      I was thinking but I don't have the experience, so you may have the experience.
      I was thinking to chop a DB with 100.000 ( 20 columns ) in 10.000 DB chunks ( 10 DBs ) and query concurrently all of them to speed up a search, then add the results. Is that a sound idea ? Or will I run in trouble down the road. Should a DB better be keep in one piece ?
      The DB is now in MySQL. I wanna do all this chopping and use SQLite. 
      Thanks
      [solved]
    • By Pickpocketz88
      First I would just like to say HELLO! to anyone reading. It has been a while since I've posted to the Forums but I'm always crawling around.
      Now to the matter at hand. I have been looking high and low for a simplistic answer my burned out brain can find but to no avail. I've only recently upped my AutoIt skill and only by a little bit such as ordering my script neatly with my own UDFs and using Global/Dim more often to make my GUI creation understandable and easy to keep things orderly. My current problem however is figuring out how to make my newest endeavor work which is my own "Debugger". I've made a GUI with an Edit Control to display what my Variables are holding and other information from a concurrently running Script. I have access to all of the scripts I'm attempting to connect but I'm dumbfounded on how I would separately read variable information from one running script into another. I'll provide my "Debugger" script that I want to read variables into and a "Meta Script" I'd want to pass info from.
      #Region Include Files #include <AutoItConstants.au3> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #include <ColorConstantS.au3> #EndRegion #Region AutoIt Options ;Opt("TrayAutoPause", 0) ;Opt("TrayMenuMode", 3) #EndRegion #Region Hotkeys HotKeySet("{ESC}", "ExitProgram") HotKeySet("{PGDN}", "PauseProgram") HotKeySet("!1", "Snippet_1") HotKeySet("!2", "Snippet_2") HotKeySet("!3", "Snippet_3") HotKeySet("!4", "Snippet_4") HotKeySet("!5", "Snippet_5") HotKeySet("!6", "Snippet_6") HotKeySet("!7", "Snippet_7") HotKeySet("!8", "Snippet_8") HotKeySet("!9", "Snippet_9") #EndRegion #Region Global Variables #Region Globals Global $gMain, $ctrlEdit ;, $gParent #EndRegion #Region $gMain Params Dim $gMainW = @DesktopWidth / 2 Dim $gMainH = @DesktopHeight / 2 Dim $gMainX = (@DesktopWidth / 2) - ($gMainW / 2) Dim $gMainY = (@DesktopHeight / 2) - ($gMainH / 2) Dim $gMainStyle = $WS_POPUP Dim $gMainStyleEx = -1 ;Dim $gMainParent = $gParent #EndRegion #Region $ctrlEdit Params Dim $ctrlEditW = Round($gMainW * 0.98) Dim $ctrlEditH = Round($gMainH * 0.98) Dim $ctrlEditX = ($gMainW - $ctrlEditW) / 2 Dim $ctrlEditY = ($gMainH - $ctrlEditH) / 2 Dim $ctrlEditStyle = -1 Dim $ctrlEditStyleEx = -1 #EndRegion #EndRegion #Region GUI Initialization ;$gParent = GUICreate("", -1, -1, -1, -1, -1, $WS_EX_TOOLWINDOW) $gMain = GUICreate("", $gMainW, $gMainH, $gMainX, $gMainY, $gMainStyle, $gMainStyleEx) GUISetBkColor($COLOR_BLACK, $gMain) $ctrlEdit = GUICtrlCreateEdit("MainW: " & $gMainW & @CRLF & "MainH: " & $gMainH & @CRLF & "EditW: " & $ctrlEditW & @CRLF & "EditH: " & $ctrlEditH, $ctrlEditX, $ctrlEditY, $ctrlEditW, $ctrlEditH, $ctrlEditStyle, $ctrlEditStyleEx) GUISetState(@SW_SHOW, $gMain) #EndRegion MainFunction() #Region Main Function (GUI) Func MainFunction() While 1 $msg = GUIGetMsg() If $msg = $GUI_EVENT_CLOSE Then Exit EndIf WEnd EndFunc #EndRegion #Region Functions Func Functions() EndFunc #EndRegion #Region Program 1 Func Snippet_1() EndFunc #EndRegion #Region Program 2 Func Snippet_2() EndFunc #EndRegion #Region Program 3 Func Snippet_3() EndFunc #EndRegion #Region Program 4 Func Snippet_4() EndFunc #EndRegion #Region Program 5 Func Snippet_5() EndFunc #EndRegion #Region Program 6 Func Snippet_6() EndFunc #EndRegion #Region Program 7 Func Snippet_7() EndFunc #EndRegion #Region Program 8 Func Snippet_8() EndFunc #EndRegion #Region Program 9 Func Snippet_9() EndFunc #EndRegion #Region Pause/Exit Functions Func PauseProgram() While 1 Sleep(1000) WEnd EndFunc Func ExitProgram() Exit EndFunc #EndRegion Pause/Exit Functions #Region Snippets #CS #CE #EndRegion #Region Other Information #CS #CE #EndRegion That's the Debugger script. Please forgive anything ignorant but point it out if you will, I'll take any pointers to get better! (I usually use a Select to get $GUI_EVENT_CLOSE but this is early on)
      Now if I made another script with a basic GUI similar to this and wanted to read say the GUI Width ($gMainW) into the Debugger Edit Control could I do it? If so, could I do it for every variable I have in a script? I read something about the Run function and adding the variables as an option parameter I believe which I think I could do with an array to keep it from being super long and ugly but would that be the only way to do this? Any information is going to be appreciated and thank you in advance for your time!
       
      Edit: Sadly it just dawned on me that I could make a UDF that will create a child window that will do this instead of having a separate script trying to invade another... I'll still be keeping an eye on this for any comments but I apologize if I wasted your time!
×
×
  • Create New...