I have been using Sqlite and making up my own string of data to quiry and update the files and it work ok …
But according to the tutorial , I shoud be using db.subst() to create the inquires of the data base.
That some time work , but not with all inquires.
I can not get this to work, I have tried all different permetaions of ' and " and ` with no success and always get similuar errors

Are there newer instruction somewere ?    I would like to do it correctly so I may switch to different databases and have the program work.
Using the deleat command work OK :

   $con.Exec(DB.Subst("delete from [&1] where  `&2`  = &3 ", sFileName, sFileField, sFileIndex))

The above works just fine … What am I doing wrong ???

From the wiki: "These arguments are quoted according to the underlying database".

I think you need to remove the quotation marks from the third argument

$con.Exec(db.subst("update [&1] set &2 where &3 = &4", sFileName, saCommand, sBkAccColumn, sFileIndex))

Greetings, Harpo.
 

I have tried that … and every other combination … always the same results

Hi GrayGhost,
Displaying the query as a message can often help you understand where the error is.  

Message(DB.Subst("delete from [&1] where  `&2`  = &3 ", sFileName, sFileField, sFileIndex))
(At a glance, I suspect that the apostrophes should be around &3 instead of &2 - apostrophes are required around literal string data not field names. )

I don't use DB.Subst as I've found it makes the line of query code harder to read and fault find.
I know that the tutorial states that this is for security reasons but SQLite is not really secure and that advice was more for MYSQL and PostGres which have far higher inbuilt security.


If you want to make the function work with different attributes, try something like this.

Provided your field types are the same, this should work with any table of those types.

Note# If you write query SQL commands in upper case and variables in Camel case then it can also help you quickly review and debug statements. 


Edit: Just noticed that you have used the variable 'DB' as your connection
DB is actually a Gambas class 'representing the first opened database'.  I've often used more that one physical database in an application and you can easily get muddled by using this as the default.
Just be aware of that as you start doing more complex database stuff.


 

I had only looked at the function's syntax.
Assuming it's an update statement, the string should be:

 $con.Exec(db.subst("UPDATE &1 SET &2 = &3 WHERE &4 = &5", TableName, FieldToUpdate, NewValue, FileIndex, ValueIndex))

 

GrayGhost said

I have been using Sqlite and making up my own string of data to quiry and update the files and it work ok …
But according to the tutorial , I shoud be using db.subst() to create the inquires of the data base.
That some time work , but not with all inquires.
I can not get this to work, I have tried all different permetaions of ' and " and ` with no success and always get similuar errors

Are there newer instruction somewere ?    I would like to do it correctly so I may switch to different databases and have the program work.
Using the deleat command work OK :

   $con.Exec(DB.Subst("delete from [&1] where  `&2`  = &3 ", sFileName, sFileField, sFileIndex))

The above works just fine … What am I doing wrong ???

From “Using GB.Subst for data base "sqlite3"”, November 4th 2025, 8:34 PM

To answer your question: "Listening to a tutorial telling you what you should do, when you already had a working solution.", I would say is what you are doing wrong 

Now, on to the solution. I can't help with with db.subst as I never ever used is. It got introduced into gambas somewhere along the road when I had my milage in using queries with Gambas.
So, you kind of don't pay attention to what's new but doesn't add anything to what you already do. I simpy use the regular Subst (and always have) and that has always worked for me, so, why change a winning team, right! 

I believe, and correct me if I'm wrong, that db.subst is trying to abstract the query a bit more from the underlying database used, so it works a bit different.
Since I only ever use mariadb and do often use very mariadb specific sql queries, regular Subt does the job just fine. (so you have: gb.subst gb.db.subst and gb.db2.subst)
Below a simple example from one of my applications where I use the selection made in a ComboBox to filter content using regular Subst.



 

as I said in my original post .. I have been able to create my own strings to make all the functions work.
Just trying to conform to Gambas standared for future updates to the Gambas language by following the correct programing form .

The topic should read "using gb.db.subst" as gb.subst is the regular Subst and not the database variant.

Post Marker #12812
The topic should read "using gb.db.subst" as gb.subst is the regular Subst and not the database variant.

I did not know there was another ,  will give it a try … thanks

where do I find gb.db.subst ????

GrayGhost said

where do I find gb.db.subst ????

From “Post #12,814”, November 5th 2025, 12:46 AM

gb = default gambas language. no components (gb.subst)
gb.db = gambas component gb.db (gb.db.subst)


 

GrayGhost said

as I said in my original post .. I have been able to create my own strings to make all the functions work.
Just trying to conform to Gambas standared for future updates to the Gambas language by following the correct programing form .

From “Post #12,811”, November 5th 2025, 12:13 AM

Then I'll have to write a page on the wiki using regular Subst for databases, just to confuse your concept of 'the correct programming form'     

So since db.subst does not seem to work with sqlite3 … I will just continue to make up my own strings to use sqlite.