Sign in to follow this  
Followers 0
StungStang

SQL question

9 posts in this topic

#1 ·  Posted (edited)

Hi to all, i want create a database for storing my radio station. Now i use an .ini file to do that, but it's too much slow now that have added too much radio. I want create a database...i see the _SQL function on autoit help.

I want create a database like that :

;   Radio name  |Streaming Link    | WebSite Link       | Genre       |
;   ----------------------------------------------------|-------------|
;   MyRadio     |mms://myradio.asx |www.myradio.com     |Rock         |
;   Myprefradio |mms://mypref.asx  |www.mypref.net      |Pop          |
;   MyExample   |http.//link.mp3   |www.myexample.com   |Dance        |

How i can create .db the file with autoit? I dont see a funcion like that in the help file.

EDIT :

I think I've solved the problem, i've created the database file with this command :

_SQLite_Open(@ScriptDir & "\Database.db",$SQLITE_OPEN_READWRITE + $SQLITE_OPEN_CREATE ,$SQLITE_ENCODING_UTF8)

Next Step i've created a table :

_SQLite_Exec (-1, "CREATE TABLE Radio (Name,Streaming,WebSite,Genre);")

And add the values :

_SQLite_Exec (-1, "INSERT INTO Radio VALUES ('MyRadio','mms://myradio.asx','www.myradio.com','Rock');")

Now i want to display the contenet of this database on a listview...how i can that?

Hi!

THanks!

Edited by StungStang

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

Hi,

Here's a bit of code I use in my app to do similar:

$GPquery = InputBox("Look up GP details?", "Enter GP Surname")
        Select
            Case $GPquery = "" ; cancel pressed
                Sleep(100)
            Case $GPquery <> ""


                Local $aResult, $iRows, $iColumns
                _SQLite_Startup()

                Local $aResult, $iRows, $iColumns
                _SQLite_Open(@ScriptDir & "\gp.sqlite")
                If @error Then
                    MsgBox(16, "SQLite Error", "Can't Load Database!")

                EndIf

                $sSQL = "SELECT GPcode, GPname, GPsurgery, GPadd1, GPadd2, GPadd3, GPadd4  FROM GPs WHERE GPname LIKE '% " & $GPquery & "%'"

                Local $get = _SQLite_GetTable2d(-1, $sSQL, $aResult, $iRows, $iColumns)
                ;MsgBox (4096, "sSQL", $sSQL & @CR & @CR  & @error)
                If $iRows <> 0 Then
                    ClipPut ("")
                Local $GPline = _ResultsDisplay ($aResult, "Please select the Correct GP and press button below to proceed")
                ;  displays matching GPs in modified arrayDisplay box, allowing single selection only
                ; returns 0 if closed with 'x'
                ; returns string with data items from selected line separated by | otherwise


                ;_ArrayDisplay($aResult, "Please select the Correct GP and press COPY SELECTED below  then close this box to proceed")

                    ;Local $GPline = ClipGet()
                    ;MsgBox (1, "GPline", $GPline)
                    Select
                        Case $GPline <> ""
                        Local $aGPcode = StringSplit($GPline, "|", 2)
                        ;MsgBox (1, "Count", $aGPcode[0])
                        $sGPcode = $aGPcode[1]
                        Case $GPline = 0
                        $sGPcode = ""
                    EndSelect
                EndIf

                _SQLite_Close()

                _SQLite_Shutdown()

_ResultsDisplay is a n modification of _arraydisplay that I've customised - the old line that used the native _ArrayDisplay command is in the comments.

William

Edited by saywell

Share this post


Link to post
Share on other sites

Thanks, your code was helpfully, but now i've another question...how i can display the resoult on listview?

Hi!

Share this post


Link to post
Share on other sites

That was why I modified the _arraydisplay function...

.

William

Share this post


Link to post
Share on other sites

#include <SQLITE.au3>
#include <SQLite.dll.au3>
#include <GuiListView.au3>
#include <GUIConstantsEx.au3>


_SQLite_Startup()

_SQLite_Open(@ScriptDir & "\Database.db",$SQLITE_OPEN_READWRITE + $SQLITE_OPEN_CREATE ,$SQLITE_ENCODING_UTF8)

_SQLite_Exec (-1, "CREATE TABLE Radio (Name,Streaming,WebSite,Genre);")

_SQLite_Exec (-1, "INSERT INTO Radio VALUES ('MyRadio','mms://myradio.asx','www.myradio.com','Rock');")
_SQLite_Exec (-1, "INSERT INTO Radio VALUES ('MyRadio2','mms://myradio2.asx','www.myradio.com','Noise');")
_SQLite_Exec (-1, "INSERT INTO Radio VALUES ('MyRadio3','mms://myradio3.asx','www.myradio.com','Pop');")



$hGui = GUICreate("Radio Stations",800,400, Default, Default)
$hListView = GUICtrlCreateListView("Name|Streaming|WebSite|Genre",20, 20, 740, 300)
_GUICtrlListView_SetColumnWidth($hListView, 0, 80)
_GUICtrlListView_SetColumnWidth($hListView, 1, 230)
_GUICtrlListView_SetColumnWidth($hListView, 2, 230)
_GUICtrlListView_SetColumnWidth($hListView, 3, 80)

GUISetState()

Local $aStations, $iRows, $iColumns
_SQLite_GetTable2d(-1,"Select Name,Streaming,WebSite,Genre From Radio",$aStations,$iRows,$iColumns)


For $i = 1 to $iRows

    $Line = $aStations[$i][0] & "|" & $aStations[$i][1] & "|" & $aStations[$i][2] & "|" & $aStations[$i][3]
    ConsoleWrite($Line)
    GuiCtrlCreateListViewItem($Line,$hListView)

Next


While 1
   $msg = GUIGetMsg()
   If $msg = $GUI_EVENT_CLOSE Then ExitLoop
WEnd
GUIDelete()

_SQLite_Close()
_SQLite_Shutdown()

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

Thanks now i've this code :

#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <GuiListView.au3>
#include <ComboConstants.au3>

Local $aResult, $iRows, $iColumns, $iRval, $hQuery, $aRow, $sMsg
$Form1 = GUICreate("Try", 627, 464, 192, 124)
$Input1 = GUICtrlCreateInput("Input1", 8, 8, 121, 21)
$Search = GUICtrlCreateButton("Search", 136, 8, 75, 25)
$Create = GUICtrlCreateButton("Create", 8, 432, 75, 25)
$Add = GUICtrlCreateButton("Add", 88, 432, 75, 25)
$Delete = GUICtrlCreateButton("Delete", 168, 432, 75, 25)
$ListView = GUICtrlCreateListView("Name|Streaming|WebSite|Genre|Continent|State", 0, 40, 626, 382)
$State = GUICtrlCreateCombo("England", 224, 8, 145, 25, BitOR($CBS_DROPDOWNLIST, $CBS_AUTOHSCROLL, $WS_HSCROLL, $WS_VSCROLL))
GUICtrlSetData (-1,"California")
GUISetState(@SW_SHOW)

_SQLite_Startup()
If @error > 0 Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit
EndIf

_SQLite_Open(@ScriptDir & "\Database.db")
If @error > 0 Then
    MsgBox(16, "Error", "Database not exist.")
    Exit
Else
    Local $aStations, $iRows, $iColumns
_SQLite_GetTable2d(-1,"Select Name,Streaming,Site,Genre,Continent,State From Radio",$aStations,$iRows,$iColumns)
For $i = 1 to $iRows

    $Line = $aStations[$i][0] & "|" & $aStations[$i][1] & "|" & $aStations[$i][2] & "|" & $aStations[$i][3] & "|" & $aStations[$i][4] & "|" & $aStations[$i][5]
    ConsoleWrite($Line)
    GuiCtrlCreateListViewItem($Line,$ListView)
Next
EndIf

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Create
            $DATABASE = _SQLite_Open(@ScriptDir & "\Database.db",$SQLITE_OPEN_READWRITE + $SQLITE_OPEN_CREATE ,$SQLITE_ENCODING_UTF8)
           _SQLite_Exec (-1, "CREATE TABLE Radio (Name,Streaming,Site,Genre,Continent,State);")
        Case $Add
            _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('BBC Radio 1','http://bbc.co.uk/radio/listen/live/r1.ram','http://www.bbc.co.uk/radio1/','Top 40/Dance','Europe','England');")
            _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('BBC Radio 2','http://bbc.co.uk/radio/listen/live/r2.ram','http://www.bbc.co.uk/radio1/','Adult Contemporary','Europe','England');")
            _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('BBC Radio 3','http://bbc.co.uk/radio/listen/live/r3.ram','http://www.bbc.co.uk/radio1/','Classical','Europe','England');")
            _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('Absolute Radio','http://aacplus-vr-32.smgradio.com:80/listen.pls','http://www.absoluteradio.co.uk/','Hot Adult Contemporary','Europe','England');")
            _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('KLOS','http://citadelcc-klos-fm.wm.llnwd.net/citadelcc_KLOS_FM','http://www.955klos.com/','Classic Rock','America','California');")
        Case $Delete
            $Element = _GUICtrlListView_GetItemTextArray($ListView,0)
            For $x = 1 To $Element[0]
                $Info = $Element[$x]
            Next
            _SQLite_Exec (-1, "DELETE FROM Radio WHERE Name = '" & $Info[1] & "'")
        Case $State
            $Selected = GUICtrlRead ($State)
            _Display_ListView($Selected)
        Case $Search
            ;I want to show the searched radio...
    EndSwitch
WEnd


Func _Display_ListView($sState)
;How i can show the radio station for selected state?
EndFunc

Now i want to show the result of search radio, and want to spect the radio station for selected state. How i can do that?, with command with sql?.And delete function dont work why? :)

Thanks!

Edited by StungStang

Share this post


Link to post
Share on other sites

Beware that you open the DB more than once, but still refer to it with -1 instead of a proper handle. Doing so you open several connection to the same DB as if you were using several processes in parallel.

I'd recommend using a proper handle instead of -1 and removing the open in the GUI loop.

Also, even if SQLite will silently create a hidden integer primary key for you, I'd advise declaring one explicitely. Doing so doesn't increase the size of your DB and will save your life should your project eveolve into anything more complex.

Mixing GUI* and _GUI functions is not recommended in general.

For your delete, are your sure that $Info[1] is the name column?


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

I dont understand that :

Also, even if SQLite will silently create a hidden integer primary key for you, I'd advise declaring one explicitely. Doing so doesn't increase the size of your DB and will save your life should your project eveolve into anything more complex.

What i've to declaring explicitely?

I've Fixed the State Display :

#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <GuiListView.au3>
#include <ComboConstants.au3>

Local $aResult, $iRows, $iColumns, $iRval, $hQuery, $aRow, $sMsg
$Form1 = GUICreate("Try", 627, 464, 192, 124)
$Input1 = GUICtrlCreateInput("Input1", 8, 8, 121, 21)
$Search = GUICtrlCreateButton("Search", 136, 8, 75, 25)
$Create = GUICtrlCreateButton("Create", 8, 432, 75, 25)
$Add = GUICtrlCreateButton("Add", 88, 432, 75, 25)
$Delete = GUICtrlCreateButton("Delete", 168, 432, 75, 25)
$ListView = GUICtrlCreateListView("Name|Streaming|WebSite|Genre|Continent|State", 0, 40, 626, 382)
Local $hListView = GUICtrlGetHandle($ListView)
$State = GUICtrlCreateCombo("England", 224, 8, 145, 25, BitOR($CBS_DROPDOWNLIST, $CBS_AUTOHSCROLL, $WS_HSCROLL, $WS_VSCROLL))
GUICtrlSetData (-1,"California")
GUISetState(@SW_SHOW)

_SQLite_Startup()
 _SQLite_Open(@ScriptDir & "\Database.db")
If @error > 0 Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit
EndIf


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Create
            $DATABASE = _SQLite_Open(@ScriptDir & "\Database.db",$SQLITE_OPEN_READWRITE + $SQLITE_OPEN_CREATE ,$SQLITE_ENCODING_UTF8)
           _SQLite_Exec (-1, "CREATE TABLE Radio (Name,Streaming,Site,Genre,Continent,State);")
           _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('BBC Radio 1','http://bbc.co.uk/radio/listen/live/r1.ram','http://www.bbc.co.uk/radio1/','Top 40/Dance','Europe','England');")
           _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('BBC Radio 2','http://bbc.co.uk/radio/listen/live/r2.ram','http://www.bbc.co.uk/radio1/','Adult Contemporary','Europe','England');")
           _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('BBC Radio 3','http://bbc.co.uk/radio/listen/live/r3.ram','http://www.bbc.co.uk/radio1/','Classical','Europe','England');")
           _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('Absolute Radio','http://aacplus-vr-32.smgradio.com:80/listen.pls','http://www.absoluteradio.co.uk/','Hot Adult Contemporary','Europe','England');")
           _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('KLOS','http://citadelcc-klos-fm.wm.llnwd.net/citadelcc_KLOS_FM','http://www.955klos.com/','Classic Rock','America','California');")
       Case $Add
           $sName = InputBox ("Name","")
           If $sName <> "" Then
               $sStream = InputBox ("Stream","")
           ElseIf $sStream <> "" Then
               $sWeb = InputBox ("Web Site","")
           ElseIf $sWeb <> "" Then
               $sGenre = InputBox ("Genre","")
           ElseIf $sGenre <> "" Then
               $sContinent = InputBox ("Continent","")
           ElseIf  $sContinent <> "" Then
               $sState = InputBox ("State","")
           ElseIf $sState <> "" Then
                _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('" & $sName & "','" & $sWeb & "','" & $sGenre & "','" & $sContinent & "','" & $sState & "'" & ');")')
            EndIf
        Case $Delete
             $Element = _GUICtrlListView_GetItemTextArray($ListView,0)
            For $x = 1 To $Element[0]
                $Info = $Element[$x]
            Next
            _SQLite_Exec (-1, "DELETE FROM Radio WHERE Name = '" & $Info[1] & "'")
        Case $State
             $Selected = GUICtrlRead ($State)
            _Display_ListView($Selected)
    EndSwitch
WEnd

Func _Display_ListView($Value) ;Ok FIXED!
_GUICtrlListView_BeginUpdate($ListView)
_GUICtrlListView_DeleteAllItems($ListView)
Local $aStations, $iRows, $iColumns
_SQLite_GetTable2d(-1, "Select Name,Streaming,Site,Genre,Continent,State FROM Radio WHERE State = '" & $Value &"'",$aStations,$iRows,$iColumns)
For $i = 1 to $iRows
$Line = $aStations[$i][0] & "|" & $aStations[$i][1] & "|" & $aStations[$i][2] & "|" & $aStations[$i][3] & "|" & $aStations[$i][4] & "|" & $aStations[$i][5]
    ConsoleWrite($Line)
    GuiCtrlCreateListViewItem($Line,$ListView)
Next
_GUICtrlListView_EndUpdate($ListView)
EndFunc

But dont work the DELETE function...and i want to know what command i have to do for create my search function?

Hi!

Share this post


Link to post
Share on other sites

StungStang,

With regard to the "delete" routine:

- $Element = _GUICtrlListView_GetItemTextArray($ListView,0) returns an array of all columns for row = 0 to $element

- you are then setting $info to the value in the LAST column in that row in a for...to loop

- in the "delete" SQL cmd you are using $info as an array, it is a scalar variable (non-array)

Change $info[1] to $info...also consider using "-1" instead of "0" in _GUICtrlListView_GetItemTextArray($ListView,0) to operate on the selected row (see doc).

With regard to the search question:

- construct a standard SQL SELECT bsaed on some column value(s) and execute it.

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

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  
Followers 0