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, 2: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 4th 2025, 6:46 PM

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 4th 2025, 6:13 PM

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.

try building your connection object …

then build your query …

not tested but should work …
 

Yes Delete and Select  are the two  command's that works correctly

this also works"

   Rs = $con.Exec(db.Subst("select * from [&1] where '&2' = &3", sFileName, sFileColumn, sFileIndex))


but not any command that used a string of values.

Hi @GrayGost
I put together an old test and added Edit.
Since you're planning on using DB, and I agree with your thoughts on security, you could use that instead of Subst; once you understand it, it's easier to use.
The fact that Subst in gb.db2 doesn't work could be a bug; I don't have time now, but I'll investigate.
Use DB.Debug to see what the queries are doing.
Create a connection (see images) to see database
It looks like the Date function has a bug…

I'm attaching the project and hope it's helpful.

test-db-0.0.2.tar.gz

Gianluigi said

It looks like the Date function has a bug…

From “Post #12,827”, November 5th 2025, 11:14 AM

It's fixed    according to the post on the mailing list.
Thanks for being stubborn  

gbWilly said

It's fixed    according to the post on the mailing list.
Thanks for being stubborn  

From “Post #12,828”, November 5th 2025, 2:35 PM

 

 

I finally got it to work using subst … I guess db.subst in broken   


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

 

 Gianluigi posted:

"The fact that Subst in gb.db2 doesn't work could be a bug; I don't have time now, but I'll investigate.
Use DB.Debug to see what the queries are doing."

I have tested with Debug and found that subst USES gb.db2.subst
   $con.Exec(Subst("update [&1] set &2 where &3 = '&4'", sFileName, saCommand, sBkAccColumn, sFileIndex))

Debug outputs this :

So it appares that is it is not nessary to use db.subst …. BUT just use  subset  

However, sticking with the example I posted, if you want to achieve the same result as Edit with Subst and Exec, the string should be combined something like this:
Keep in mind that without injection protection, the SQL string should look like this:
UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition;

So, corresponding code could be:


This is sample code, and obviously we need to find a better way to handle it…  
So, no bugs, Exec and Subst work as expected.

 

From my testing … don't use db.subst()  ….. just use subst() .
db.subst() gives an error with some data strings. 

GrayGhost said

From my testing … don't use db.subst()  ….. just use subst() .
db.subst() gives an error with some data strings. 

From “Post #12,859”, November 7th 2025, 6:18 PM

This is an answer the big boss gave me a while back:
"But if you omit to use Connection.Subst(), your code is utterly wrong,
as it introduces a possible SQL injection.
Regards,
Benoît Minisini"
And if he says so…

I'm attaching a specific project that demonstrates the proper functioning of connection.subst. Now you have the opportunity to prove your point, and I'm sorry if I don't take your word for it.

DB is a "superstructure" that Gambas provides. Many people don't use it, thinking (wrongly) that it slows down the database. I could also demonstrate that, in some cases, Gambas speeds up the database.

Note: Even if you can't see it, once the project is launched, the IDE contains the database folder (if you want to see it, you have to restart the IDE). You can create the connection, as shown in the images above, so you can see the database directly within the IDE.

 

DBSubstTest-0.0.1.tar.gz

Gianluigi
Thanks for the help ….. this is why I started this thread …. to learn the correct way to do it.
I am rewriting my file handling to conform to the proper syntax.
It turns out to be even simpler and less progaming do it this way.  

Public Sub InsertNewRecord(sFileName As String)
   Dim sNew As New String[]
   For Each obj As Object In RS.Fields    ' This assenbles the File column names it to A string[ ]
      sNew.Push(obj.Name)
   Next
sNew[0] = "newAcc" & Str(Rand(20, 99))
   $con.Exec(db.Subst("INSERT INTO [&1] VALUES   (&2, &3, &4, &5, &6, &7,&8, &9)", sFileName, sNew[0], sNew[1], sNew[2], sNew[3], sNew[4], sNew[5], sNew[6], sNew[7]))
End

I don't know how to explain it to you and I'll leave that task to those more experienced than me.
I'm a very amateur "programmer," and since I have no professional experience, I rely on those more experienced than me.
Fortunately, Minisini has provided us with the DB class, which, behind the scenes, transparently makes our queries secure and correct on any database.
Professionals like @gb.Willy can easily do without it because they know how to use queries correctly.
If the DB class doesn't always produce errors (?) when its syntax isn't respected, I think it's due to the risk of inoculation, but I'm not sure because I don't know how it works.
I just know that there's a lot of work behind it, and I'm happy it exists.
If I were you, I'd try to focus on Find and Edit. I've only used Delete for a few examples, but it's always a good idea to keep track of what's been done, and rather than removing an entire piece of information, I prefer to add columns to the table that tell me whether the information is current or not, who changed the state, and when.
Beyond that, I'd focus on Begin, Commit, Rollback, Create, Open, Close, constants, and properties.
As I said, I don't know what else to say.