SQLite semi Embedded database functionality in AutoIt

[jpm]

Added 'example_TsvImport.au3', Demonstrates Tab Seperated values Import

Updated bundled SQLite Version to 3.2.8 (Changelog)

SQLite.zip

Beware: SQLite3.exe crashes if you try to '.import' into a Table that does not exist...

Thanks Picasso,

Why the import stop in my example around 7 columns and 49 rows?

any reason why the dll jump from 120k to 245k?

Edited December 23, 2005 by jpm

[randallc]

Hi,

@jpm,

Seems funny to say so, but post your example and I'll look!

import is very finnicky if there are mismatched rows in the csv, by number, blank row etc; expects a very uniform file from what I have tested.

FileReadLine to import by insert is slower for larger files than 1000 lines, say,, but you can check each line and modify if needed; see my 2 funcs (and GUI option buttons) in further updated example; [both of mine are slower than necessary at present as I also index every row for fast sorting, especially floating pont rows]

CreateDbCSVSlow() [EDIT]

_SQLite_ImportCsv( $sCSVFile,$s_Header,$s_Separator)

Best, randall

(eg my examples OK at 187 lines by 99 cols...)

Edited December 23, 2005 by randallc

[jpm]

Thanks randallc,

starting my 49 th row only 6 columns was defined. So now everything is OK

[piccaso]

any reason why the dll jump from 120k to 245k?

its not upx'ed this time.

i'm not sure if i should...

i Played a bit with the odbc driver (here)

looks nice

the only downside is that its statically linked with ver. 3.2.7.

but maybe its faster with prepare/step/... api ...

Merry Christmas everybody

Edited December 23, 2005 by piccaso

[randallc]

Hi,

Merry Xmas!

@piccaso, can we do without sqlite3.dll; or

1.does sqlite.exe use it? [i seem to be able to delete it without any errors]

2. are there any functions that cannot be converted?

Best, randall

PS Here's the example program all converted to not need sqlite3.dll;; working better and quicker with sqlite3.exe.

Needs options

1 of re-indexing a row

2. import or create large files without indexing initially (eg 10^6 rows too slow ; up to 500,000 ok)

3. improve ability to import variable row length files [although slow option now copes with rows shorter than 1st or up to 20 larger]

4. Choose displayed row easier in large dbs.

5. choose lateral display column start (limited to 20 at present for speed)

Then I will be happy as a limited display, sort, re-save function.

Best, Randall

Edited December 27, 2005 by randallc

[piccaso]

... can we do without sqlite3.dll?

at least i cant

1.does sqlite.exe use it? [i seem to be able to delete it without any errors]

No, its standalone (only needs msvcrt.dll)

2. are there any functions that cannot be converted?

the use of in memory databases or temp tables is limited.

_SQLite_SQliteExe() Opens and Closes the db every call and they are deleted.

but you can still use them for bigger transactions...

[randallc]

hi,

perhaps I asked the wrong question!

Can AutoIt seriously do without sqlite3.dll, to limit the files to only 1 extra?

Try my database browser now with nwind.db and tell me your thoughts.

Best, Randall

Edited December 28, 2005 by randallc

[randallc]

Hi,

Why is SQL not intuiitive?

What is Wrong with this to alter 1 cell in table "test", row numbered 99 on "a" , in column "c"?

BEGIN TRANSACTION;

INSERT INTO test ( c ) WHERE a= 99 VALUES('8');

COMMIT TRANSACTION;

Randall

EDIT; never mind, I got it in the end; hard to follow the syntax!

UPDATE test SET c= 8 WHERE a= 99;

Now my ListView will Edit a cell if not the index row; first CNTRL - click the column header, then double-click the listview row;

Best, Randall

[PS at present only on my created files or anyway if column 1 of Listview contains ROWID; I'll get there..]

Edited December 27, 2005 by randallc

[randallc]

This one is closer, not perfect; only edit external tables if you know its OK! [imported csvs not all editing yet either]

Randall

Edited December 28, 2005 by randallc

[randallc]

This time....!

Edited December 28, 2005 by randallc

[randallc]

Hi,

i have added a query button for query on imported csv files, in table named "test", result to table "query"

randall

Edited December 28, 2005 by randallc

[piccaso]

Updated bundled SQLite Version to 3.3.4

SQLite.zip

next week i'll start working on doc's again

[JSThePatriot]

Trying to use SQLite, and I keep getting an unknown function on...

_GUICtrlTreeViewItemGetTree()

I have checked the source, and even upadated to the newest beta, but still to no avail. The Function is in the proper include file, and the include file is included. I even tried to run the script from the help file and it wouldnt work.

JS

[randallc]

Hi,

Maybe it has changed to "_GUICtrlTreeViewGetTree"?

Best, Randall

[ptrex]

@JSThePatriot

If you show us some more code, maybe we better understand where the problem is.

Regards

[jpm]

Updated bundled SQLite Version to 3.3.4

SQLite.zip

next week i'll start working on doc's again

small correction for allowing debugging under autoIt debug version. Some time I need to use it to debug.

and small typo.

keep going your good work

[ptrex]

@Jpm

Thanks for the update.

Does this mean you plan to have the SQLite included as a standard UDF, like IE.au3. Including documentation etc ?

[jpm]

@Jpm

Thanks for the update.

Does this mean you plan to have the SQLite included as a standard UDF, like IE.au3. Including documentation etc ?

I will like just do the submission to JdeB who is responsible for UDF integration

[ptrex]

@Jpm

Haleluja !!

I am realy honered. Nevertheless all credits go to @piccaso and yourselve

I am willing to help in making a CHM helpfile, if that can help getting it past the "deliberation jury"

But if so I need some templates that are needed to create the CHM Helpfile in MS HTML HELP Workshop.

(You know like Big_Daddy, did for IE.au3)

Let me know or PM me about this.

[jpm]

@Jpm

Haleluja !!

I am realy honered. Nevertheless all credits go to @piccaso and yourselve

I am willing to help in making a CHM helpfile, if that can help getting it past the "deliberation jury"

But if so I need some templates that are needed to create the CHM Helpfile in MS HTML HELP Workshop.

(You know like Big_Daddy, did for IE.au3)

Let me know or PM me about this.

What has been done by picasso is good just the examples must be name the same func.txt and func.au3. There is an automated process which make the .htm and the .chm

It could be good to have a page giving the highlight of how to use all this functions, kind of starter/tutorial.

to day not really in place in the UDF .chm but I am sure JdeB will find a way.