SQLite semi Embedded database functionality in AutoIt

[ptrex]

@JSThePatriot

I don't see the need for a "addin", having the original DLL is the best and closest we can get.

This way it will follow the new versions of the SQLite easily, and there is no external addin or compilation needed.

Let's not make ik more complicated as it is.

regards,

ptrex

[webmedic]

ok I understand now.

Just like the hash plugin you would simply call funsctions directly from the dll.

Does this mean that you will have to code the functions that the udf takes care of now. I know this is not an entirely correct summation of it. But more or less we would dirrectly call the built in functions rather than use a udf. This would mean I suppose that some work would need to be done.

[JSThePatriot]

@JSThePatriot

I don't see the need for a "addin", having the original DLL is the best and closest we can get.

This way it will follow the new versions of the SQLite easily, and there is no external addin or compilation needed.

Let's not make ik more complicated as it is.

regards,

ptrex

I feel I would be making it simpler. There wouldnt need to be an AutoIt DLLCall Wrapper for the SQLite3.DLL. This wrapper bloats the size of any exe, where having a single DLL file that you could use native feeling AutoIt functions from would be smaller, simpler, easier to use, and easier to maintain as well. When a new version of the code comes out it would just be a matter of adding that to the AutoIt plugin DLL just the same as the SQLite3.dll is currently updated.

Please explain further if you still feel that a Plugin DLL wouldnt be useful for those wanting to use SQLite and AutoIt...

Thanks for voicing your opinion,

JS

[JSThePatriot]

ok I understand now.

Just like the hash plugin you would simply call funsctions directly from the dll.

Does this mean that you will have to code the functions that the udf takes care of now. I know this is not an entirely correct summation of it. But more or less we would dirrectly call the built in functions rather than use a udf. This would mean I suppose that some work would need to be done.

Basically as I explained beneath your post in reply to ptrex, it would be a bit of work to create, but no more work to create or update as the SQLite3.DLL is for whoever made it. Basically I would be creating the exact same thing as the SQLite3.DLL, except for including it in the AutoIt Plugin Architecture.

All the functions the UDF takes care of are due to the DLLCall() of the SQLite3.dll. Those will all be in any release version of SQLite Library, which would be constantly updated in my Plugin, same as the author of the SQLite3.dll updates upon new versions of the SQLite Library.

JS

[wannabeeprogrammer]

@ptrex

Thanks, this will keep me busy for some time...

Regards,

Johan.

@wannabeeprogrammer

The record locking is not something that is handled by AutoIT but by SQLite itself.

See for more information.

SQLite Locking principles

Your other questions are hard to answer if you have little or no knowledge of DB' s or DB management.

Writing an application that uses a DB is hard to do even for more experienced users.

You need to have different skills :

- AutoIT to write the code to write the application.

- SQL knowledge to interact with the DB.

So it is not just know what files to use and know where to put them.

To start with it is download the LiteX Automation DLL where you can find the link in post 2.

Than put it in the Windows directory and register it like described in the 2 nd post.

Copy the code in the 2 nd post and run it. If it works you have a sample code that tells you how to get started.

Second alternative is use the DLLCall example from post 1. But is was never finished.

Which runs on the native SQLite DLL which you can download from the SQLite.Org site

Nevertheless it will also do what you are trying to accomplish.

I hope this get's you started.

Regards

ptrex

[ptrex]

@JSThePatriot

OK if it is just copy and pasting the SQLite Code in a AutoIT plugIn Architecture (that easy?).

Let's give it a try and see what people think of it.

I am interesting to see the result.

regards

ptrex

[JSThePatriot]

@JSThePatriot

OK if it is just copy and pasting the SQLite Code in a AutoIT plugIn Architecture (that easy?).

Let's give it a try and see what people think of it.

I am interesting to see the result.

regards

ptrex

Its not quite that easy, but shouldnt be too much harder than that.

I am glad you would like to see the results. We shall see how it turns out. The Author has emailed me, and shown some interest.

Thanks,

JS

[webmedic]

Not to bug you to much but do you think clucene might be an option also? I'm not shure what all would be involved I wouldn't mind working on it myself but I have no idea how to do plugins and I dont know anything about c or c++.

If there is a good place to start and a good compiler to use under windows I dont mind takign a stab at it.

(I would rather have something based on ming and not the other one. I also am not going to break down and pay for anything micrsoft.) I do compileing on linux all ther time I just have never taken the time to mess with windows and compiling anything. It always looked like pulling teeth to do it.

[JSThePatriot]

Not to bug you to much but do you think clucene might be an option also? I'm not shure what all would be involved I wouldn't mind working on it myself but I have no idea how to do plugins and I dont know anything about c or c++.

If there is a good place to start and a good compiler to use under windows I dont mind takign a stab at it.

(I would rather have something based on ming and not the other one. I also am not going to break down and pay for anything micrsoft.) I do compileing on linux all ther time I just have never taken the time to mess with windows and compiling anything. It always looked like pulling teeth to do it.

Are you wanting just a compiler or an IDE?

JS

[webmedic]

well I know of environments like codeblocks and such. I guess if you are doing programming the whole ide would be best.

Edited September 19, 2006 by webmedic

[JSThePatriot]

well I know of environments like codeblocks and such. I guess if you are doing programming the whole ide would be best.

Code::Blocks and Dev's C++ are the two best free IDE's. I actually prefer Code::Blocks now to Dev's C++ due to its ability to use multiple compilers. Ming, M$, Borlands, and others.

JS

[piccaso]

Hello everyone

Updated To SQlite 3.3.7

SQLite.zip

if someone ever feels the need to update the included sqlite version by itself, everything needed is in the repository (see 'svn' link in my sig)

there are some new 'beta' features of sqlite in version 3.3.7.

Virtual tables and modules.

well modules are beyond the scope of autoit and virtual tables depend on it so...

as far as i understad that this features cant be utilized without a seperate dll or something.

(please correct me if i am wrong)

@JS

Are you working on a plugin (allready) ?

please tell me what your up to, im interested

@ptrex

the repository has moved, and as far as i remeber you had access to it.

so if you get bored or motivated and want to work a bit let me know and i'll let you in

and can you tell me how you made that chm?

the one included is a bit out of date

Edited September 21, 2006 by piccaso

[JSThePatriot]

Hello everyone

@JS

Are you working on a plugin (allready) ?

please tell me what your up to, im interested

I am already working on converting the library to a AutoIt Plugin-DLL. I have contacted the Author of SQLite, and he seemed interested. I am trying to find the source code for other similar DLL's to save myself some time, and get some examples of how it should best be done before I just jump completely in. I hope to have this plugin completed and released by the end of the year at the latest. Once I have a compilable portion up I will create a thread, and post a link here. If you and ptrex dont mind.

My plugin DLL would have all of the same functionality of the other DLL if not more (not sure if the other DLL completely encompasses the SQLite Library).

I would like to know why you think Modules are out of the scope of AutoIt? I will be looking into the source obviously, and I hope that isnt the case, but I may be a little fuzzy on exactly the definition of Modules. I havent worked this much with databases in about 2 years, I remember most of the good stuff, but the advanced stuff I was barely getting into, and needing back when my job position changed.

Let me know if you have any other questions or comments,

JS

[piccaso]

sounds nice

but sure will be a lot of work...

I would like to know why you think Modules are out of the scope of AutoIt?

just by reading stuff on the wiki, maybe i'm wrong.

i didnt find any examples to read jet...

[ptrex]

@piccaso

Hi good to see you' re back (in SQLite).

I have seen you in other post from time to time, like you will have seen me here and there.

Well I know that I promised to help you in some way, specially in the documenting the functions.

I haven't been progression on this for 2 main reasons :

1. I am fully booked till the end of the year,

with projects in the real world. So time is limited.

2. I (and some others in the thread share the same idea) feel that some functionality is missing in your

approach of some UDF's.

Or maybe it might not be missing, but than the functionality is not porperly exposed in the UDF's.

I think to proseed we first need to agree on the UDF's functionality and structure.

Let me give an example :

It is more clear to have a functions split for the OPEN DB file and InMemory, like.

- Open( [path] ) and a separate one for OpenInMemory()

Naming is not always clear what it does :

- _SQlite_Query() is more like _SQLite_QueryPrepare()

-_SQLite_QueryFinalize() is more like _SQLite_QueryExecute()

- _SQLite_FetchNames() is more like _SQLite_ColumnNames()

- Etc.

This might not seem important, but it is because we need to agree on certain standards, before we continue.

Nevertheless I like your approach better than an Addin approach.

So I would not like to see it fade away !!

Let me know what you think.

regards

ptrex

PS:

The CHM file is created using HTML Help Workshop. But if I see the time I will update it again and sent it to you.

Appreciate the work done already !!

Edited September 21, 2006 by ptrex

[webmedic]

Cant speak as to which is better really becasue I have only used the one that directly uses the dll. I do find it to work well and don't really want it to change though.

As far as db access goes it does give me access and is stable.

[piccaso]

Hi good to see you' re back (in SQLite).

I have seen you in other post from time to time, like you will have seen me here and there.

havent realy been gone just quiet

Well I know that I promised to help you in some way, specially in the documenting the functions.

I haven't been progression on this for 2 main reasons :

1. I am fully booked till the end of the year,

with projects in the real world. So time is limited.

no problem, im low on time to

2. I (and some others in the thread share the same idea) feel that some functionality is missing in your

approach of some UDF's.

Or maybe it might not be missing, but than the functionality is not porperly exposed in the UDF's.

I think to proseed we first need to agree on the UDF's functionality and structure.

Let me give an example :

It is more clear to have a functions split for the OPEN DB file and InMemory, like.

- Open( [path] ) and a separate one for OpenInMemory()

Naming is not always clear what it does :

- _SQlite_Query() is more like _SQLite_QueryPrepare()

-_SQLite_QueryFinalize() is more like _SQLite_QueryExecute()

- _SQLite_FetchNames() is more like _SQLite_ColumnNames()

- Etc.

This might not seem important, but it is because we need to agree on certain standards, before we continue.

haveing _SQLite_OpenInMemory() seems useless for me but if its needed its easy to do.

_SQlite_Query() is just prepareing the query but it would be a code breaking change...

_SQLite_QueryFinalize() is realy just finalizeing querys, if its not executed bevore its deleted.

_SQLite_FetchNames() was named to stand in line with _SQLite_FetchData() but would be better named _SQLite_FetchColumnNames(), codebreaking again

sorry i would realy like to avoid breaking compatibility to existing scripts unless its necessary.

Nevertheless I like your approach better than an Addin approach.

So I would not like to see it fade away !!

it wont fade away that quickly

there is one thing i will add in the upcomeing week(s)

something like in ie.au3 thats prints out errors in interpreted mode.

[ptrex]

@picasso

Hi thanks for the feedback.

I know what I and other are asking, may result in a codebraking event. But this is a 1 time effort.

Changing the functions name to a more descriptive way, will result in better comprehension of the UDF's content.

Especially for those who want to start using your library.

The current names are not well chosen I must say (even for me it prevents me from using the UDF lib. a lot).

I would rather go for the LiteX at the moment, because there is a good description of Methods and Properties to use.

Therefore I suggest to go and identify UDF's naming, with the ones listed on the SQLite site.

result-codes

sqlite3_aggregate_context

sqlite3_aggregate_count

sqlite3_bind_blob

sqlite3_bind_double

sqlite3_bind_int

sqlite3_bind_int64

sqlite3_bind_null

sqlite3_bind_parameter_count

sqlite3_bind_parameter_index

sqlite3_bind_parameter_name

sqlite3_bind_text

sqlite3_bind_text16

sqlite3_busy_handler

sqlite3_busy_timeout

sqlite3_changes

sqlite3_clear_bindings

sqlite3_close

sqlite3_collation_needed

sqlite3_collation_needed16

sqlite3_column_blob

sqlite3_column_bytes

sqlite3_column_bytes16

sqlite3_column_count

sqlite3_column_database_name

sqlite3_column_database_name16

sqlite3_column_decltype

sqlite3_column_decltype16

sqlite3_column_double

sqlite3_column_int

sqlite3_column_int64

sqlite3_column_name

sqlite3_column_name16

sqlite3_column_origin_name

sqlite3_column_origin_name16

sqlite3_column_table_name

sqlite3_column_table_name16

sqlite3_column_text

sqlite3_column_text16

sqlite3_column_type

sqlite3_commit_hook

sqlite3_complete

sqlite3_complete16

sqlite3_create_collation

sqlite3_create_collation16

sqlite3_create_function

sqlite3_create_function16

sqlite3_data_count

sqlite3_db_handle

sqlite3_enable_shared_cache

sqlite3_errcode

sqlite3_errmsg

sqlite3_errmsg16

sqlite3_exec

sqlite3_expired

sqlite3_finalize

sqlite3_free

sqlite3_free_table

sqlite3_get_autocommit

sqlite3_get_table

sqlite3_global_recover

sqlite3_interrupt

sqlite3_last_insert_rowid

sqlite3_libversion

sqlite3_malloc

sqlite3_mprintf

sqlite3_open

sqlite3_open16

sqlite3_prepare

sqlite3_prepare16

sqlite3_progress_handler

sqlite3_realloc

sqlite3_release_memory

sqlite3_reset

sqlite3_result_blob

sqlite3_result_double

sqlite3_result_error

sqlite3_result_error16

sqlite3_result_int

sqlite3_result_int64

sqlite3_result_null

sqlite3_result_text

sqlite3_result_text16

sqlite3_result_text16be

sqlite3_result_text16le

sqlite3_result_value

sqlite3_rollback_hook

sqlite3_set_authorizer

sqlite3_sleep

sqlite3_soft_heap_limit

sqlite3_step

sqlite3_table_column_metadata

sqlite3_thread_cleanup

sqlite3_total_changes

sqlite3_trace

sqlite3_transfer_bindings

sqlite3_update_hook

sqlite3_user_data

sqlite3_value_blob

sqlite3_value_bytes

sqlite3_value_bytes16

sqlite3_value_double

sqlite3_value_int

sqlite3_value_int64

sqlite3_value_text

sqlite3_value_text16

sqlite3_value_text16be

sqlite3_value_text16le

sqlite3_value_type

sqlite3_vmprintf

There maybe better references, but this one will do just fine.

This was also suggested by JPM as far as I can recall, to refer to the PHP site.

Please consider doing this, and I will help you updating the HELP documentation.

Regards,

ptrex

[ptrex]

For those whore more interested in SQLite I found this

Webinar Introduction to SQLite (Google Video)by the Author

Enjoy !!

Edited September 22, 2006 by ptrex

[webmedic]

Myself I like the way the dll version runs already thats why I chose it over the other ones. I would also really hate to have to rewrite a bunch of scripts.

I also don't find the actuall usability to be any lower.