SQLite question

[notta]

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.

expandcollapse popup

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 October 2, 2008 by notta

[PsaltyDS]

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 October 2, 2008 by PsaltyDS

[zorphnog]

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.

[notta]

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.

expandcollapse popup

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 October 2, 2008 by notta

[zorphnog]

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

[notta]

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 October 2, 2008 by notta

[zorphnog]

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 October 2, 2008 by zorphnog

[notta]

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.

expandcollapse popup

;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 October 3, 2008 by notta

[zorphnog]

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.