SQLite and Arrays?

[Proph]

I'm new to SQLite and I was wondering... is it possible to send an entire array into a Table in sqlite instead of having to use a loop to add it?

I know in when I deal with ini files I can convert my array into a string and add the entire string at the bottom of the ini section easily to quickly add an array.  I was wondering if there is any tricks to do this in sqlite?

Also... is it easy to sort a sqlite table so that everything is descending?

I've searched for simple udf's that people may have created to help simplify using sqlite in autoit but I haven't found much.  I like the simplicity of in files but I hate the limitations of characters allowed.  Is there any simple udf's you would recommend?  I found some ini like udf's... but they were created with key and values.  I don't really think I need values.  Just wondering if anyone else has already created some simple sqlite udf's for someone coming from the simplicity of ini files before I go and try writing some myself.

 

Thanks!

[jchd]

There exists no such trick in SQLite. Arrays are just not relational entities.

That said, I don't see any problem here: you have to use a loop somewhere to convert your array into something that SQLite can digest.

Instead of talking about .INI format or existing UDFs which don't do what you need, tell us precisely what you need and the SQLite solution will appear magically.

And yes, you can easily collate (sort) using any criterion you can dream of.

[Proph]

Thanks for the input.  Basically what I am wanting to do is have my data setup something like this...

 

 

Column1 | Column2 | Column3 | Column4
Data1 | Information1 | Something1 | Request1
Data2 | Information2 | Something2 | Requests2
Data3 | Information3 | Something3 | Requests3
Data4 | Information4 | Something4 | Requests4
Data5 | Information5 | Something5 | Requests5
Data6 | Information6 | Something6 | Requests6
Data7 | Information7 | Something7 | Requests7
Data8 | Information8 | Something8 | Requests8
Data9 | Information9 | Something9 | Requests9

I want to be able to query each column and have a 1 Dimensional Array given to me with a list of the entire column where Array[0] = how many are found in the Column.

In the Autoit help file it looks like I can query Rows Across.  But I want to query Columns vertically. I'm sure its simple.  But I am kind of confused on the syntax.  I can see that SQLite looks like you can do so much more.  

Edited October 19, 2015 by Proph

[jchd]

No problem until there. You'll need to provide more specific information or, better, sample data to declare the correct datatypes of the columns and the kind of queries you intend to use. And yes, SQLite is very powerful.

OTOH if your data is already in a 2D array, you can still scan it counting rows matching your criterion.

[Gianni]

maybe you can be intereste to this "facility":https://www.autoitscript.com/forum/topic/166536-manage-arrays-by-means-of-sql/
it allows you to easily manage arrays by SQL query "on the fly"

#include <ArraySQL.au3> ; get it from following link:
; https://www.autoitscript.com/forum/topic/166536-manage-arrays-by-means-of-sql/?do=findComment&comment=1234441

Local $aMyArray[10][4] = [["Column1", "Column2", "Column3", "Column4"], _ ; your array
        ["Data1", "Information1", "Something1", "Request1"], _
        ["Data2", "Information2", "Something2", "Requests2"], _
        ["Data3", "Information3", "Something3", "Requests3"], _
        ["Data4", "Information4", "Something4", "Requests4"], _
        ["Data5", "Information5", "Something5", "Requests5"], _
        ["Data6", "Information6", "Something6", "Requests6"], _
        ["Data7", "Information7", "Something7", "Requests7"], _
        ["Data8", "Information8", "Something8", "Requests8"], _
        ["Data9", "Information9", "Something9", "Requests9"]]

_ArrayDelete($aMyArray, 0) ; remove first row since we want only data and no columns names

; in the SQL query you have to refer the columns of the array as column0, column1 column2 .... and so on

; following query sort your array by first column (column0) in descending order
$sQuery = "SELECT * FROM array ORDER BY column0 desc;" ; your SQL query here

$aResult = _ArraySQL($aMyArray, $sQuery) ; execute the query

If Not @error Then
    _ArrayDisplay($aResult) ; show result
Else
    MsgBox(0, "error", $g__sSQLiteError) ; any error is shown here
EndIf