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

ihudson,

Something like this should work

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

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

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  

  • Similar Content

    • 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!
    • By junichironakashima
      I have a windows 10 pc and Im trying to create an automatic solving in calculator with a simple worded question (example: What is 45 x 53 ?). thou I cant make the select...Case...EndSelect statement work, here is my code
      #include <AutoItConstants.au3> HotKeySet("{F4}", "ExitProg") Func ExitProg() Exit 0 EndFunc MouseClick($MOUSE_CLICK_LEFT, 417, 659, 2, 1) Send("^c") Func valData() $Chek = "What " If ClipGet() == $Chek Then Check() Else Do MouseClick($MOUSE_CLICK_LEFT, 417, 659, 2, 3) Send("^c") Sleep(500) Until ClipGet() == $Chek EndIf EndFunc Func Check() $Chek2 = "?" c1() c2() c3() c4() c5() c6() c7() Select Case c1() = $Chek2 ;two MouseClick($MOUSE_CLICK_LEFT, 453, 645, 2, 1) Send("^c") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 1347, 197, 1, 1) Send("^v") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 474, 645, 2, 1) Send("^c") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 1347, 197, 1, 1) Send("*") Send("^v") Send("{NUMPADENTER}") Case c2() = $Chek2 ;three MouseClick($MOUSE_CLICK_LEFT, 453, 645, 2, 1) Send("^c") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 1347, 197, 1, 1) Send("^v") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 482, 645, 2, 1) Send("^c") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 1347, 197, 1, 1) Send("*") Send("^v") Send("{NUMPADENTER}") Case c3() = $Chek2 ;four MouseClick($MOUSE_CLICK_LEFT, 453, 645, 2, 1) Send("^c") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 1347, 197, 1, 1) Send("^v") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 487, 645, 2, 1) Send("^c") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 1347, 197, 1, 1) Send("*") Send("^v") Send("{NUMPADENTER}") Case c4() = $Chek2 ;five MouseClick($MOUSE_CLICK_LEFT, 453, 645, 2, 1) Send("^c") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 1347, 197, 1, 1) Send("^v") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 495, 645, 2, 1) Send("^c") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 1347, 197, 1, 1) Send("*") Send("^v") Send("{NUMPADENTER}") Case c5() = $Chek2 ;six MouseClick($MOUSE_CLICK_LEFT, 453, 645, 2, 1) Send("^c") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 1347, 197, 1, 1) Send("^v") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 495, 645, 2, 1) Send("^c") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 1347, 197, 1, 1) Send("*") Send("^v") Send("{NUMPADENTER}") Case c6() = $Chek2 ;seven MouseClick($MOUSE_CLICK_LEFT, 453, 645, 2, 1) Send("^c") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 1347, 197, 1, 1) Send("^v") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 509, 645, 2, 1) Send("^c") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 1347, 197, 1, 1) Send("*") Send("^v") Send("{NUMPADENTER}") Case c7() = $Chek2 ;eight MouseClick($MOUSE_CLICK_LEFT, 453, 645, 2, 1) Send("^c") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 1347, 197, 1, 1) Send("^v") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 509, 645, 2, 1) Send("^c") Sleep(150) MouseClick($MOUSE_CLICK_LEFT, 1347, 197, 1, 1) Send("*") Send("^v") Send("{NUMPADENTER}") Case Else Exit EndSelect EndFunc Func c1() MouseClick($MOUSE_CLICK_LEFT, 485, 643, 2, 1) Send("^c") EndFunc Func c2() MouseClick($MOUSE_CLICK_LEFT, 493, 644, 2, 1) Send("^c") EndFunc Func c3() MouseClick($MOUSE_CLICK_LEFT, 498, 645, 2, 1) Send("^c") EndFunc Func c4() MouseClick($MOUSE_CLICK_LEFT, 508, 647, 2, 1) Send("^c") EndFunc Func c5() MouseClick($MOUSE_CLICK_LEFT, 514, 645, 2, 1) Send("^c") EndFunc Func c6() MouseClick($MOUSE_CLICK_LEFT, 523, 645, 2, 1) Send("^c") EndFunc Func c7() MouseClick($MOUSE_CLICK_LEFT, 530, 645, 2, 1) Send("^c") EndFunc valData() MouseClick($MOUSE_CLICK_LEFT, 1349, 196, 1, 1) ;clicking the answer in calc Send("^c") Send("{DEL}") MouseClick($MOUSE_CLICK_LEFT, 499, 706, 1, 1) Send("^v") Exit  
    • By Skysnake
      Hi
      I am trying to set Accelerator keys from an array.
      I select the KEY and CONTROL from a SQLite table, the Array looks like that generated for the Helpfile, but I can't get the CONTROLS to resolve...
      I though about Assign & Eval, but not sure if that's a step in the right direction. IsDeclared shows that the $var exists in Local Scope -1.  
       
      Local $Main = GUICreate("Custom MsgBox", 225, 80) GUICtrlCreateLabel("Please select a button.", 10, 10) Local $idButton_Yes = GUICtrlCreateButton("Yes", 10, 50, 65, 25) Local $idButton_No = GUICtrlCreateButton("No", 80, 50, 65, 25) Local $idButton_Exit = GUICtrlCreateButton("Exit", 150, 50, 65, 25) Local $query, $aResult, $iRows, $iColumns $query = "" ;reset $query = "Select hotkey_key, hotkey_ctrl from mytable where mykeys = 'hotkey' ; " ; ; Query $iRval = _SQLite_GetTable2d($sqliteDb, $query, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then Local $sizeofHotkeys = UBound($aResult) - 1 ConsoleWrite("$sizeofHotkeys " & $sizeofHotkeys & @CRLF) If $sizeofHotkeys > 0 Then Local $main__aAccelKeys[$sizeofHotkeys][2] For $i = 0 To $sizeofHotkeys - 1 $j = $i + 1 ; replace friendly text with code -- ! alt + Shift ^ Ctrl # Windows $aResult[$j][0] = StringReplace($aResult[$j][0], "Alt", "!") $aResult[$j][0] = StringReplace($aResult[$j][0], "Shift", "+") $aResult[$j][0] = StringReplace($aResult[$j][0], "Ctrl", "^") $main__aAccelKeys[$i][0] = $aResult[$j][0] ;--- $main__aAccelKeys[$i][1] = $aResult[$j][1] ;--- Next ;~ Row|Col 0|Col 1 ;~ Row 0|F2|$idButton_Yes ;~ Row 1|F3|$idButton_No _DebugArrayDisplay($main__aAccelKeys) Local $rv = GUISetAccelerators($main__aAccelKeys, $Main) GUISetState(@SW_SHOW) ; Display the GUI.  
      Please note that this is a modified Helpfile example.
      The Helpfile specifies (a) WinHandle and (b) last Gui created.
      --> the example uses a control not a WinHandle and (b) what happens with ChildGuis? Also, the HelpFile specifies lower case, yet the examples show "{F1}" upper case?
      Also, is there a way to check the result of the GuiSetAccelerator function? 
      Note, if I add these to lines after the FOR loop, then the F1 works, and the DebugArrayDisplays shows control 4... not it's name... So I am in the right place, but my $vars names do not convert to their control numbers in the GUI
      Next $main__aAccelKeys[$sizeofHotkeys - 1][0] = "{F1}" ; -- -- use the extra row for the F1 $main__aAccelKeys[$sizeofHotkeys - 1][1] = $ChmHLP ;--- Skysnake
    • By Skeletor
      Hi All,
      Here's a really simple question. 
      I ran the code from the helpfile under: _SQLite_Open

      Issue is I end up with an error message: SQLite3.dll Can't be Loaded!
      I placed the *.dll in the include folder, but still nothing. 

      Where must this file be placed.
       
    • By Pricehacker
      Hello!
      Im wondering if it is possible to 'empty' the variable value to save memory, for example i often use variable as a onetime use thing and would prefer to 'forget' it after is is used
      Maybe it is just as easy as to setting $vVar = Null, but i wanted to make sure that this is the case
×
×
  • Create New...