Proph

SQLite and Arrays?

5 posts in this topic

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!

Share this post


Link to post
Share on other sites



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.


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

#3 ·  Posted (edited)

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 by Proph

Share this post


Link to post
Share on other sites

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.


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

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

 


small minds discuss people average minds discuss events great minds discuss ideas.... and use 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