gb.db2 Order by

[grayghost4]

I got some of the substitutions working

  
   $con.Exec(Subst("delete From  [&1] where  [&2] = " & Quote(sFileIndex), sFileName, sFileField))

   ' the above line working for the lower line ... but can't get the third sub to work  :( 

   ' $con.Exec("delete from" & Quote(sFileName) & "where" & Quote(sFileField) & " = " & Quote(sFileIndex))

[grayghost4]

also found you don't need the [ ]

   $con.Exec(Subst("delete From  &1 where  &2 = " & Quote(sFileIndex), sFileName, sFileField))

but I can't get the third Subst to work

[Andreas_K]

The only way that is working on my machine is:

Public Function ImportDB(sTable As String, sField1 As String, sField2 As String, Optional sFilter As String) As Boolean

   Dim Res As Result
   Dim sSql As String = DB.Subst("SELECT * FROM &1 ORDER BY " & sField1, sTable)

   If sFilter <> "" Then
      ssql = db.Subst("SELECT * FROM &1 " & sFilter & " ORDER BY " & sField1, sTable)
   Endif

   res = db.Exec(sSql)

Order by works only without " ' " (Apostrophe) and db.subst put this evertime, so its not working.

https://gambaswiki.org/wiki/doc/db-quoting with this i get only error, table not found....

Sorry for the two questions in one post.

[thatbruce]

Ah, the fundamental misunderstand I believe that you may have is that DB.Subst is like Subst. It's not, nor was it ever intended to be.
It is for quoting values in SQL clauses in a way that Gambas can submit properly quoted sql.
If you want to insert bare strings into your sql then you must do it in 2 (or more) steps. Using Subst for the bare strings and using DB.Subst for actual values.

hth
b

[Poly]

Hi thatbruce

Ah, the fundamental misunderstand I believe that you may have is that DB.Subst is like Subst. It's not, nor was it ever intended to be.
It is for quoting values in SQL clauses in a way that Gambas can submit properly quoted sql.

However, this does not explain why it is shown differently in the wiki and why it runs in gb.db but not with gb.db2

It may be that some people here are only interested in a certain code to make something work. Not for me. I thought we should all be interested in finding and reporting errors or misunderstandings in the wiki or bugs in the individual components.

So if

[DataSource1.Table = DB.Subst("SELECT * From [&1] WHERE '&2' = &3 Order by '&4' DESC", "Namen", "Vorname", "Risa", "Nachname")

works for me fine with gb.db and gb.db.form, but not with gb.db2 and db.gb2.form, then something must have changed in the Db.Subst function in the two components.

But if this works for you, then it may also be due to my system.

I am using Devuan , x11 and Gambas 3.20.2

Best regards Poly

[grayghost4]

you are correct ... something has changed with the syntax of the gb.db to gb.db2
this line works with gb.db

      $con.Exec(db.Subst("delete from [&1] where  '&2'  Like  &3 ", sFileName, sFileField, sFileIndex))

but gives a syntax error and crashes with gb.db2

Gambas verison 3.20.1

[BruceSteers]

Perhaps DB.Subst IS supposed to work like Subst but it's a bug?

I cannot imagine why Benoit would call it Subst otherwise?

Maybe something for the bugtracker?

[thatbruce]

Nah, I think not a bug.
It does what it says it does. It 'correctly' quotes data values (oh and table/column names now) suitable for the underlying database.
As the wiki says, it is ""similar to" Subst$, which if you think about it does not mean "the same as".
and further down, "the argument is supposed to be a SQL value" noting that in SQL a "value" is a specific thing, not just any old string.
Why does the command have the same name? It doesn't, it's "DB.Subst".

Yet again, "read EVERY word of the wiki page until your eyes bleed".
(I'm just trying to save M. Minisini the bother.)
b

[grayghost4]

BUT ... something has changed in gb.db2 ?

[Poly]

Hello everyone

"read EVERY word of the wiki page until your eyes bleed".
(I'm just trying to save M. Minisini the bother.)

I have referred to the corresponding Wiki page several times. And it is clearly stated there.

> I strongly suggest to use these methods for security reasons, and never build your requests by simple string concatenation!

Then I use exact the Syntax which was specified there

Code: Select all

Print DB.Subst("SELECT ** FROM [&1] WHERE '&2' = &3 ORDER BY '&4'", "Table", "Column1", "a string", "Column2")

and get an Error only with gb.db2 and not with gb.db

and the same with my own Code which use the same syntax

1 [DataSource1.Table = DB.Subst("SELECT * From [&1] WHERE '&2' = &3 Order by '&4' DESC", "Namen", "Vorname", "Risa", "Nachname")

then I don't know what else to read so that my eyes bleed

I also don't know whether anyone here has really tried this exactly.
I can also post an example tonight if that's what you want.
I would say that something has changed and Benoît had specifically asked to report problems with the new component.

best regards
Poly