Sign in to follow this  
Followers 0
notta

SQLite question

9 posts in this topic

#1 ·  Posted (edited)

With the help from Weapon a while back and learning from dmob's cash register program I have been incorporating the use of SQLite to display some data, but I'm a little confused. It seems to me that I still need to create large arrays and test for a lot of conditions if I want to display my data. Let me give an example

Let's say my data looks this:

firstname, lastname, building,model,room,.....25

.

.

.

.

.

1000 lines

Let's say I have a fixed(10) amount of models to choose from, I still need to have case statements for each model to build the new table, correct? I have 25 columns, but I'm trying to write only 4 specific columns to a web page.

While _SQLite_FetchData ($SQL, $aRow) = $SQLITE_OK

    $ProdArr[$d][0] = $aRow[0]
    $ProdArr[$d][1] = $aRow[1]
    $ProdArr[$d][2] = $aRow[2]
    $ProdArr[$d][3] = $aRow[3]
    $ProdArr[$d][4] = $aRow[4]
    $ProdArr[$d][5] = $aRow[5]
    $ProdArr[$d][6] = $aRow[6]
    $ProdArr[$d][7] = $aRow[7]
    $ProdArr[$d][8] = $aRow[8]
    $ProdArr[$d][9] = $aRow[9]
    $ProdArr[$d][10] = $aRow[10]
    $ProdArr[$d][11] = $aRow[11]
    $ProdArr[$d][12] = $aRow[12]
    $ProdArr[$d][13] = $aRow[13]
    $ProdArr[$d][14] = $aRow[14]
    $ProdArr[$d][15] = $aRow[15]
    $ProdArr[$d][16] = $aRow[16]
    $ProdArr[$d][17] = $aRow[17]
    $ProdArr[$d][18] = $aRow[18]
    $ProdArr[$d][19] = $aRow[19]
    $ProdArr[$d][20] = $aRow[20]
    $ProdArr[$d][21] = $aRow[21]
    $ProdArr[$d][22] = $aRow[22]
    $ProdArr[$d][23] = $aRow[23]
    $ProdArr[$d][24] = $aRow[24]
    
    
    $ProdArr[0][0] += 1
    $d += 1

WEnd

$modelColumns = 'lastname,firstname,model,computername'

$SQL1 = StringFormat("CREATE TABLE model (%s);",$modelColumns)
_SQLite_Exec(-1,$SQL1)

;Build table to gx280
For $X = 1 to Ubound($ProdArr)-1
    if $ProdArr[$X][3] = "GX280" Then
     ;$ProdArr[$X][3] = "GX150" Then
        $SQL1 = _SQLite_Exec(-1,"INSERT INTO model (lastname, firstname, model, computername) VALUES ('" & $prodArr[$X][0] & "', '" & $prodArr[$X][1] & "', '" & $prodArr[$X][3] & "', '" & $prodArr[$X][7] & "' );",$SQL1)
    EndIf
Next
    
_SQlite_Query (-1, "SELECT * FROM model ORDER BY lastname DESC", $SQL1)

I'm just learning SQL commands and it seems to be working very well, but it seems like I'm going to have to check for each case. Am I right or am I thinking of this all wrong?

Edited by notta

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

With the help from Weapon a while back and learning from dmob's cash register program I have been incorporating the use of SQLite to display some data, but I'm a little confused. It seems to me that I still need to create large arrays and test for a lot of conditions if I want to display my data. Let me give an example

Let's say my data looks this:

firstname, lastname, building,model,room,.....25

.

1000 lines

Let's say I have a fixed(10) amount of models to choose from, I still need to have case statements for each model to build the new table, correct? I have 25 columns, but I'm trying to write only 4 specific columns to a web page.

While _SQLite_FetchData ($SQL, $aRow) = $SQLITE_OK
    $ProdArr[$d][0] = $aRow[0]
    $ProdArr[$d][1] = $aRow[1]
    $ProdArr[$d][2] = $aRow[2]
    $ProdArr[$d][3] = $aRow[3]
    $ProdArr[$d][4] = $aRow[4]
    $ProdArr[$d][5] = $aRow[5]
    $ProdArr[$d][6] = $aRow[6]
    $ProdArr[$d][7] = $aRow[7]
    $ProdArr[$d][8] = $aRow[8]
    $ProdArr[$d][9] = $aRow[9]
    $ProdArr[$d][10] = $aRow[10]
    $ProdArr[$d][11] = $aRow[11]
    $ProdArr[$d][12] = $aRow[12]
    $ProdArr[$d][13] = $aRow[13]
    $ProdArr[$d][14] = $aRow[14]
    $ProdArr[$d][15] = $aRow[15]
    $ProdArr[$d][16] = $aRow[16]
    $ProdArr[$d][17] = $aRow[17]
    $ProdArr[$d][18] = $aRow[18]
    $ProdArr[$d][19] = $aRow[19]
    $ProdArr[$d][20] = $aRow[20]
    $ProdArr[$d][21] = $aRow[21]
    $ProdArr[$d][22] = $aRow[22]
    $ProdArr[$d][23] = $aRow[23]
    $ProdArr[$d][24] = $aRow[24]
    
    
    $ProdArr[0][0] += 1
    $d += 1
WEnd
Most would just do:
While _SQLite_FetchData ($SQL, $aRow) = $SQLITE_OK
    For $n = 0 To 24
        $ProdArr[$d][$n] = $aRow[$n]
    Next
    $ProdArr[0][0] += 1
    $d += 1
WEnd

$modelColumns = 'lastname,firstname,model,computername'

$SQL1 = StringFormat("CREATE TABLE model (%s);",$modelColumns)
_SQLite_Exec(-1,$SQL1)

;Build table to gx280
For $X = 1 to Ubound($ProdArr)-1
    if $ProdArr[$X][3] = "GX280" Then
;$ProdArr[$X][3] = "GX150" Then
        $SQL1 = _SQLite_Exec(-1,"INSERT INTO model (lastname, firstname, model, computername) VALUES ('" & $prodArr[$X][0] & "', '" & $prodArr[$X][1] & "', '" & $prodArr[$X][3] & "', '" & $prodArr[$X][7] & "' );",$SQL1)
    EndIf
Next
    
_SQlite_Query (-1, "SELECT * FROM model ORDER BY lastname DESC", $SQL1)

I'm just learning SQL commands and it seems to be working very well, but it seems like I'm going to have to check for each case. Am I right or am I thinking of this all wrong?

Each case of what? If you don't like assembling the 2D array yourself, you could use _SQLite_GetTable2D() vice _SQlite_Query() and _SQLite_FetchData().

:)

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

We need a little more information of the overall picture. What are these 'cases' you are speaking of? What are you trying to accomplish? The great thing about using sql is that you can use it to do most of the 'heavy lifting'. You don't need separate tables for each format, you just need to use proper queries on your existing table.

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

I'm sure the problem is that I don't know SQL enough yet. New example, here is the data once again:

buildings could range from 1 - 25 and that's it.

Dim $columns = 'lastName,firstname,phone,building,model,serial,domain,comment'

_fileReadToArray("data.csv",$data);for simplicity all my data is in a csv file in the form lastName,firstname,phone,building,model,serial,domain. 1 series like this per line.

Dim $prodArr[$data[0] + 1][7]; Create 2 dimensional array [number of machines][7 columns]

$ProdArr[0][0] = 1; Increment [0][0] counter element

for $k = 1 To $data[0];Loop through each line (single csv file)
    
    Local $temp = ""
    $tempArray = ""
    
    $tempArray = StringSplit($data[$k],",");create temporary array for the current line(single csv file) and split on the comma
    
    for $l = 1 To 25 
        if $tempArray[$l] = "" Then $tempArray[$l] = "-------";if any element is empty filll with --------- to avoid SQL issues
        $temp &= $tempArray[$l] & ",";7 columns, but only keep 6 since the comments column was left blank a lot of time. Was causing SQL issues.
    Next
    
    $temp = StringTrimRight($temp,1); Trim off the comma
    $data[$k] = $temp;same line as when we started except for empty cells were filled, date was adjusted into the format needed, and comment element was stripped off
Next

_SQLite_Startup()
_SQLite_Open()

;Create table
$SQL = StringFormat("CREATE TABLE tblData (%s);",$columns);creates the table and sets the columns
_SQLite_Exec(-1,$SQL)

For $X = 1 to Ubound($data)-1
;Escape single quotes
    $result = StringReplace($data[$X],"'", "''");if any lines contain ' then another ' is added to escape it
   
 ;Encapsulate all values with single quotes
    $result = StringRegExpReplace($result,"[^,]+", "'$0'")

    $SQL = StringFormat("INSERT INTO tblData VALUES (%s);",$result)
    _SQLite_Exec(-1,$SQL)
Next

$d = 1

_SQlite_Query (-1, "SELECT * FROM tblData ORDER BY building DESC", $SQL)

While _SQLite_FetchData ($SQL, $aRow) = $SQLITE_OK;run the insert statement on each row
    $ProdArr[$d][0] = $aRow[0]
    $ProdArr[$d][1] = $aRow[1]
    $ProdArr[$d][2] = $aRow[2]
    $ProdArr[$d][3] = $aRow[3]
    $ProdArr[$d][4] = $aRow[4]
    $ProdArr[$d][5] = $aRow[5]
    
    $ProdArr[0][0] += 1;increment the counter of the array
    $d += 1;the row number
Wend

_SQLite_QueryFinalize($SQL)

_buildingTable();Create table the listing the buildings

Func _buildingTable()
$buildingColumns = 'lastName,firstname,phone,building,model,serial,domain'

$SQL1 = StringFormat("CREATE TABLE buildings (%s);",$buildingColumns)
_SQLite_Exec(-1,$SQL1)


For $X = 1 to Ubound($ProdArr)-1
    if $ProdArr[$X][3] = "building 10" Then;create table for building 10
        $SQL1 = _SQLite_Exec(-1,"INSERT INTO building (lastname, firstname,building,model) VALUES ('" & $prodArr[$X][0] & "', '" & $prodArr[$X][1] & "', '" & $prodArr[$X][3] & "', '" & $prodArr[$X][4] & "' );",$SQL1) [b];Only want to display these 4 columns[/b]
    EndIf
Next

_SQlite_Query (-1, "SELECT * FROM buidlings ORDER BY model DESC", $SQL1);only going to be machines for building 10 so sort by model

$y = 1

While _SQLite_FetchData ($SQL1, $aRow1) = $SQLITE_OK
    
    $sText = $aRow1[0] & "," & $aRow1[1] & "," & $aRow1[2] & "," & $aRow1[3]

    $text = StringSplit($sText,",")
    
    If $SBUDebugIt Then 
        ConsoleWrite(StringFormat(" %-2s  %-2s  %-30s  %-8s  %-10s  %-5s  %-5s  %-5s  %-5s  %-5s",$y, $aRow1[0], $aRow1[1], _
                            $aRow1[2], $aRow1[3]) & @CR)
    EndIf
    
    $y += 1                 
WEnd

_SQLite_QueryFinalize($SQL1)
EndFunc

So I want to create a web page that shows all the computers I have listed for each buidling. So create a page for each building, but it seems I have to create a table for each building. I know I'm looking at this wrong, so I could use some help.

Right now I'm just using straight HTMl, but I'll change that later.

[Edit]

I guess after reading this it's still difficult for people to understand. If my data is sorted by building and there are 25 buildings the huge array will be sorted, but how can I pull out the data for specific buildings?

lastName,firstname,phone,building,model,serial,domain

day,ron,444-3433,1,gx280,34343,home

davi,asdd,444-3433,1,gx150,42343,home

sdsd,asd,444-3433,1,gx150,34366,home

sad,adas,444-3433,2,gx240,64334,home

asda,adasd,444-3433,2,gx260,43534,home

dsfd,asd,444-3433,2,gx620,12331,home

sad,asd,444-3433,2,gx260,56354,home

adas,asd,444-3433,2,gx280,45313,home

asdaron,sad,444-3433,3,gx280,34521,home

asdas,adas,444-3433,3,gx240,74344,home

asd,asd,444-3433,4,gx240,43542,home

asdhdf,dfd,444-3433,4,gx250,23423,home

What if I want to generate all page that just lists machine in building 2? I want to also only display lastname, firstname,building,model. See what I mean?

Edited by notta

Share this post


Link to post
Share on other sites

You can do all of this with one table, you just need to learn how to use some more complex queries. For instance, if you want only the machines in building 10 and sorted by model you could use this query:

SELECT * FROM tblData WHERE building = 'building 10' ORDER BY model DESC;

And then you could write the HTML with something like this:

Dim $sSQL, $aResult, $iRows, $iColumns

For $i=1 To 25
    $sSQL = "SELECT * FROM tblData WHERE building = 'building " & $i & "' ORDER BY model DESC;"
    If _SQLite_GetTable2d(-1, $sSQL, $aResult, $iRows, $iColumns) = $SQLITE_OK Then
    ;Write HTML using $aResult
    EndIf
Next

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

Considering my jibberish you answered this perfectly zorphnog. I have been fooling with this since you posted it and I think this is exactly what I need. I plan on trying to implement this all night tonight. Two more question if you have time, I have some entries that the team entered some data in the wrong case. I tried using COLLATE NOCASE but it doesn't seem to be working.

SELECT * FROM tblData WHERE building = '1' COLLATE NOCASE

I guess I could convert everything to lower or upper case, but the collate nocase would be easier.

The second question, I'm trying to use the date between clause, but it's not returning the correct items.

$startDate = "2008/08/01"
$endDate = "2008/10/01"

SELECT * FROM tblData WHERE Date BETWEEN '" & $startDate & "' & AND & '" $endDate & "' ORDER BY date;", $Query)

My date data is also in the form of yyyy/mm/dd.

This is for another page I'm trying to generate. The table is returned with 2 entries and their from 2008/1/23 and 2008/1/18 or something like that. I have more entries between that date range and plus the 2 dates returned are not even in the range specified. Any idea on these two questions? Thanks.

You can do all of this with one table, you just need to learn how to use some more complex queries. For instance, if you want only the machines in building 10 and sorted by model you could use this query:

Edited by notta

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

First of all, if you haven't already found it by now http://www.sqlite.org/lang.html. This is the syntax page and contains a lot of information. Also, you may want to buy an sql introduction book (I use O'Reilly's Learning SQL) for quick referencing of the more complex SQL queries.

For case sensitivity, you can use the upper or lower function of sqlite. If you want the results to be formatted a then use it in your result definition, or use it when comparing in WHERE statements:

SELECT upper(lastName),upper(firstname),building,model FROM tblData WHERE building = '1' ORDER BY upper(model) DESC;

As for the date question. Are you specifying the date column as a date type when you create the table? Dates should be in the format of YYYY-MM-DD.

CREATE TABLE myTable (lname tinytext, fname tinytext, model tinytext, recvd_date date);
Edited by zorphnog

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

I'm loving this sqlLite. After creating the initial array it generated my pages in a fraction of a second. What do I need to know about memory? I have a lot of pages that I need to generate, so does the array created by _SQLite_GetTable2d get deleted when I move onto the next one or do I manually need to clear the memory? I know _SQLite_QueryFinalize($sql) ends the query, but does it free the memory as well? The reason I ask is that it blew through that 26 list in like 5 seconds, so I was thinking of taking it a step further and generating pages for each user that would list all the computers in their name.

;Names of different date items I'm searching for. Stuff1 = GX260; Stuff2 = GX240...
Dim $List[27] = ["26", "stuff1", "stuff2", "stuff3", "stuff4", "stuff5", "stuff6", "stuff7", "stuff8", "stuff9", "stuff10", "stuff11", "stuff12", "stuff13", "stuff14", "stuff15", "stuff16", "stuff17", "stuff18", "stuff19", "stuff19", "stuff20", "stuff21", "stuff22", "stuff23", "stuff24", "stuff25", "stuff26" ] 

    
    for $listNumber = 1 To $List[0] 

        $fOpen = FileOpen($List[$listNumber] & ".html",2);create new document;ie stuff5.html
    
    _genheader($fOpenStuff);Create the standard header for each page
    
       ;html Stuff
    FileWriteLine($fOpenStuff,"<div id=""CenterContainer"">")
    FileWriteLine($fOpenStuff,"<div id=""Number"">" & $List[$sbuNumber] & "</div>")
    FileWriteLine($fOpenStuff,"<div id=""b"">")
    FileWriteLine($fOpenStuff,"<table class=""bTable"" border=""1"" width=""90%"">")
    FileWriteLine($fOpenStuff,"<tr>")
    FileWriteLine($fOpenStuff,"<td class=""bcellHead"">Computer Name</td>")
    FileWriteLine($fOpenStuff,"<td class=""bcellHead"">Last Name</td>")
    FileWriteLine($fOpenStuff,"<td class=""bcellHead"">Fisrt Name</td>")
    FileWriteLine($fOpenStuff,"<td class=""bcellHead"">Phone</td>")
    FileWriteLine($fOpenStuff,"</tr>")
    
    $SQL = "SELECT * FROM tblData WHERE stuff1 = '" & $List[$listNumber] & "' ORDER BY computerName ASC;"
                    
    $iRval = _SQLite_GetTable2d (-1,$SQL, $stuffList, $iRows, $iColumns) 
    If $iRval = $SQLITE_OK Then
        for $j = 1 To UBound($stuffList) - 1
            FileWriteLine($fOpenStuff,"<tr>")
            FileWriteLine($fOpenStuff,"<td class=""missingCells"">" & $List[$j][7] & "</td>")
            FileWriteLine($fOpenStuff,"<td class=""missingCells"">" & $List[$j][0] & "</td>")
            FileWriteLine($fOpenStuff,"<td class=""missingCells"">" & $List[$j][1] & "</td>")
            FileWriteLine($fOpenStuff,"<td class=""missingCells"">" & $List[$j][2] & "</td>")
            FileWriteLine($fOpenStuff,"</tr>")
        Next
    Else
        MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg ())
    EndIf
        
        FileWriteLine($fOpenStuff,"</table>")
        FileWriteLine($fOpenStuff,"</div>")
        FileWriteLine($fOpenStuff,"</div>")
        
        _genFooter($fOpenStuff)
        
        FileClose($fOpenStuff)
        _SQLite_QueryFinalize($sql)
        
    Next
Edited by notta

Share this post


Link to post
Share on other sites

If you're using the same array variable then its cleared out each time you call _SQLite_GetTable2d. You shouldn't have any memory issues with any of the things your trying to do.

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