SQLdeLite question

I use SQLdeLite for querying SQLite and cubeSQL databases with no problem using its SQLdeLIteSelect method. However, it’s not clear to me how to use the remaining two methods to do an Insert or an Edit:

SQLdeLiteExecute(sqlStatementAsText, SQLdeLiteRecordObject) CreateInsertStatement(databaseObject, TableNameAsText, TableAndFieldNamesQuotedAsBoolean)

Anyone here using SQLdeLite for more than queries? Suppose I want to insert a single record with two fields (“John”, “Doe”) into table “People” in database DB.

With an edit you would create your query like you would a select.

Dim _db As New SQLiteDatabase
_db… -> Open database, etc.

Dim _edit As New SQLdeLite.Record
_edit.FirstName = “Phillip”
_edit.LastName = “Zedalis”

_db.SQLdeLiteExecute(“UPDATE Users SET FirstName = $FirstName, LastName = $LastName”, _edit)


However I am lazy and I did not want to create long winded INSERT statements each time that have every column in them. So instead I wrote the ‘CreateInsertStatement’ method which does exactly what it says it does. It returns a Text value of a valid INSERT statement for the database in question. SQLdeLite is database agnostic but it is aware of minor differences between them like whether to use quotations or not, etc.

Dim _db As New SQLiteDatabase
_db… -> Open database, etc.

Dim _insert As New SQLdeLite.Record
_insert.FirstName = “Ralph”
_insert.LastName = “Alvy”

Dim _insertStatement As Text
_insertStatement = _insert.CreateInsertStatement(_db, “Users”) // The third parameter has a default that is valid for most databases.

_db.SQLExecute(_insertStatement)

// Of course you could also do that in one step.
_db.SQLExecute(_insert.CreateInsertStatement(_db, “Users”))

Thanks, Phillip. Very clear. Perhaps it would be a good idea to put those two examples in the README file.

Also, I notice that you didn’t announce your recent SQLdeLite January update in this forum. I’m not sure someone who doesn’t subscribe to your Git repo would know about it. It seems a very critical update for cubeSQL users.

I don’t want too many people using it because it’s my little productivity secret. Haha. Just kidding you are right - I also owe someone else more examples. I am working on it.

[quote]Dim _insertStatement As Text
_insertStatement = _insert.CreateInsertStatement(_db, “Users”) // The third parameter has a default that is valid for most databases.[/quote]

That third parameter was what most confused me when I originally posted here. What databases will require it? What exactly is it?

Some databases you need to put quotes around table and column names. The default (true):

INSERT INTO “Users” (“FirstName”, “LastName”) VALUES (“Ralph”, “Alvy”)

If you set it to false:

INSERT INTO Users (FirstName, LastName) VALUES (“Ralph”, “Alvy”)

[quote=311941:@Phillip Zedalis]Some databases you need to put quotes around table and column names. The default (true):

INSERT INTO “Users” (“FirstName”, “LastName”) VALUES (“Ralph”, “Alvy”)

If you set it to false:

INSERT INTO Users (FirstName, LastName) VALUES (“Ralph”, “Alvy”)[/quote]
Hmmm…will SQLdeLite handle this automatically in a future version?

Well the default is usually the right thing. I might be able to make it automatic and probably should… I never disable it so it might be unnecessary - I need to review all the supported DBMS again.

[quote]Dim _db As New SQLiteDatabase
_db… -> Open database, etc.

Dim _insert As New SQLdeLite.Record
_insert.FirstName = “Ralph”
_insert.LastName = “Alvy”

Dim _insertStatement As Text
_insertStatement = _insert.CreateInsertStatement(_db, “Users”) // The third parameter has a default that is valid for most databases.

_db.SQLExecute(_insertStatement)

// Of course you could also do that in one step.
_db.SQLExecute(_insert.CreateInsertStatement(_db, “Users”))[/quote]

You’re missing the database record parameter in the _db.SQLExecute statements above. Should be

[code]
_db.SQLExecute(_insertStatement, _insert)

// Of course you could also do that in one step.
_db.SQLExecute(_insert.CreateInsertStatement(_db, “Users”), _insert)[/code]

_db.SQLExecute(_insertStatement)

calls the original SQLExecute() method provided by the database class. You would not pass in the record parameter because it does not know what a SQLdeLite.Record is. It takes one String/Text parameter as the SQL to execute.

However the SQLdeLiteExecute and SQLdeLiteSelect methods take the SQL and the SQLdeLite.Record parameter because that’s how it constructs the SQL behind the scenes.

Unless I am not following what you mean?

Ah. I was using SQLdeLiteExecute when I experimented with it, not SQLExecute. Didn’t notice you were using the latter.

I know thread is almost a year old, but didn’t want to create a new one because I have a related question.

SQLdeLite is working great for me so far. However, is there a similar statement for creating an update statement? I don’t see a CreateUpdateStatement(). I’m thinking something like CreateUpdateStatement(tableName, recordObject, primaryKey [integer | string]).

Any other SQLdeLite users come up with something for that?

The main issue with an Update() method is you need to identify the primary key or at least some columns to be used as the WHERE clause otherwise you update too many rows.

I have been working on a new release with some quality of life improvements like a legitimate Insert() vs CreateInsertStatement and some other enhancements. Also working on a manual and more example projects.

I use it in every one of my internal and consulting projects. Glad you are having success with it.


To answer your question I recommend you just manually create the UPDATE command with the $parameters per the Readme. A future Update() method will exist where you can pass in the where parameters to be used.

I have c# code that does dynamic update and inserts, you just pass a dataTable with data, tableName and the primary key (usually the id). The project is for syncing data between the mobile device and a SQL Server backend. I’m modifying it for Xojo and can send you the method when it’s done (hopefully this weekend) so you can check it out.

Upon further reflection I am not going to add an Update() and Delete() method. Honestly it is ideal for the developer to identify how they want to update/delete.

The crux of the problem is an Update() without any WHERE parameters in SQL updates ALL rows. I do not want SQLdeLite to be destructive by default. There is too much risk that a user unknowingly calls the method and does not pass any parameters and ends up with a damaged database. Too many do not read the notes before trying it out and so I think explicitly writing your UPDATE/DELETE is better overall.

However I am improving SQLdeLite with two new features for the next release.

A SQLdeLiteInsert() method for each database provider that takes ANY object. It will loop through the properties and attempt to add the object to the database.

So you can create your object hierarchy and quickly add and develop SQL statements without the need for prepared statements or bindings. You can also use SQLdeLite.Record in situations where you do not have an existing object model which makes for quick query creation as it does today.

Also I will add that if you really need Update/Delete you should use the default RecordSet object that is returned with queries which does track primary keys and such.

However primary keys are really just the surface of SQL queries and often times my queries are much more complex. This is one reason I shy away from ActiveRecord because it lacks table relationship capabilities and too much emphasis on a single primary key.