gb.db2 Order by

[Poly]

Hi Quincunxian

Can you give me an example of what you would pass in sWhere please, as I can't quite make sense of your SQL statement.

I have simply taken Andreas' example and tried to quote correctly.
The sWhere doesn't make sense to me in this context either, but he wrote that this example worked for gb.db.
That's why I assumed that it could just be a problem with the quoting. As explained in my link to Wiki.

But it is not.

The following works for me with gb.db and gb.db.form but not with gb.db2 and gb.db2.form

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

So I can confirm the problem.

[grayghost4]

why bother with .subst ?
I use sqlite not sql ... is there a diffenance ?.... Try this :

rs = DB("SELECT * From" & Quote(Namen) & "WHERE" & Quote(Vorname) & " = " & Quote(Risa) " Order by " & Quote(Nachmame) & " DESC")  

Or maybe all the variables need to be in brackets > [&3]

[Poly]

Try this :

rs = DB("SELECT * From" & Quote(Namen) & "WHERE" & Quote(Vorname) & " = " & Quote(Risa) " Order by " & Quote(Nachmame) & " DESC")  

Many thanks,

I I had to adapt your code a bit, but it doesn't work.

rs = DB("SELECT * From" & Quote("Namen") & "WHERE" & Quote("Vorname") & " = " & Quote("Risa") & "Order by" & Quote("Nachname") & " DESC")

with gb.db2 I get: Typ missmatsch wanted DB, got String instead
with gd.db I get: not a function

I think there is a problem with gd.db2 or it works differently.
I get even with the simplest form that works normally under gd.db, with gd.db2:

rs = DB.Subst("SELECT * FROM [&1]", "Namen")

the following error.

No such table: 'Name'

But I have to check this again in a programme without IDE. Because I use a data browser to display it.
That's why the code looks exactly like this for me:

Code: Select all

DataSource1.Table = DB.Subst("SELECT * FROM [&1]", "Namen")

Works perfectly with gd.db and gb.db.form but not with gb.db2 and gb.db2.form

Thank you very much for your attention.

Best regards
Poly

[grayghost4]

Or maybe all the variables need to be in brackets > [&3] if that puts them in " " quotes.

edit ... I see that does not work

[grayghost4]

This is my code which work with both versions

Public Sub GetRecordData(sFileName As String, sFileColumn As String, sFileIndex As String) As String[]
   
   Dim saInfo As New String[]    ' must have "NEW" or you will get a null error with .add 
   Dim obj As ResultField
   
   RS = $con.Exec("Select * From" & Quote(sFileName) & "where" & Quote(sFileColumn) & "like" & Quote(sFileIndex))
   
   For Each obj In RS.Fields
      sainfo.Add(RS[obj.Name])
   Next 
   Return saInfo
   
End 

[Poly]

In my case it recognises it as a table, but says it doesn't know this table.

So it is definitely a different behaviour due to the new component.
It may be that my code is somehow badly built, I will test this again in a very simple database without an IDE.
So far I can't say that this is a bug or error.

I will test your code immediately

Thanks Poly

[grayghost4]

This is the complete module "

' Gambas module file

'' Module file to handle SQLite file 
'' SQLite  file handeling subroutines 
'' writen by  GrayGhost4  Marvin Clavey  June 2022 

Private RS As Result
Private sadata As String[]
Private $con As New Connection

Public Sub GetFileData(sfilename As String, scolumnName As String) As String[]
   
   With $con
      Try .Close()             ' Close the connection. The try allows it to fail without error
      .Type = "sqlite3"        ' Defines the type of connection
      .Host = Application.path ' Host will be the path where the sqlite db file is
      .Name = "data.SQL"  '  database name is the name of the database file"
      Try .Open()                  ' We activate and open the connection, the try is to allow an error
      If Error Then Print "Cannot Open Database. Error = "; Error.Text
      RS = .Exec("Select * From " & sfilename) 
   End With 
   Return RS.All(scolumnName)   ' return all the data from one column 
   
End

Public Sub GetRecordData(sFileName As String, sFileColumn As String, sFileIndex As String) As String[]
   
   Dim saInfo As New String[]    ' must have "NEW" or you will get a null error with .add 
   Dim obj As ResultField
   
   RS = $con.Exec("Select * From" & Quote(sFileName) & "where" & Quote(sFileColumn) & "like" & Quote(sFileIndex))
   
   For Each obj In RS.Fields
      sainfo.Add(RS[obj.Name])
   Next 
   Return saInfo
   
End 

Public Sub UpdateFileData(sFileName As String, sFileIndex As String, saInfo As String[])
   
   Dim saCommand As String   
   Dim obj As ResultField
   
   saInfo = saInfo.Reverse()
   For Each obj In RS.Fields                'assemble the update command "column" = "data"
      saCommand &= Quote(obj.Name) & " = " & Quote(saInfo.Pop()) & ","
   Next 
   saCommand = Left(saCommand, -1)        ' remove the last ","  from saCommand string
   $con.Exec("update" & Quote(sFileName) & "set" & saCommand & "where accname = " & Quote(sFileIndex))
   
End 

Public Sub InsertNewRecord(sFileName As String)
   
   Dim sNewRecord As String
   
   For Each obj As Object In RS.Fields    ' This assenbles the File column names it to A string
      sNewRecord &= Quote(obj.Name) & "," ' to insert into a blank record to be edited later 
   Next 
   sNewRecord = Left(sNewRecord, -1)         'remove the last ","  from the string 
   sNewRecord = Replace(sNewRecord, "accname", "newAcc-" & Str(Rand(20, 100)))  ' replace first field (random) for index
   $con.Exec("INSERT INTO " & Quote(sFileName) & " VALUES (" & sNewRecord & ")")
   
End

Public Sub deleatOneRecord(sFileName As String, sFileField As String, sFileIndex As String)
   '  Deleat one reccord from the table with an indexed coluem 
   
   $con.Exec("delete from" & Quote(sFileName) & "where" & Quote(sFileField) & " = " & Quote(sFileIndex))
   
End

[Poly]

Hi Graygost

It works as you said, but then of course the DB for the string has to be removed.
This is also how it works for me with gb.db2

DataSource1.Table = "SELECT * From" & Quote("Namen") & "WHERE" & Quote("Vorname") & " = " & Quote("Risa") & "Order by" & Quote("Nachname") & " DESC"

Thank you very much.

But then it is a bug or not.
So the substitution with Db.Subst does not work.
That shouldn't be the case, should it?

[grayghost4]

how about trying to Quote the subitutions

Quote(&3) ....

also I dont think you need the " " around the varable name... that is what Quote() does

[Poly]

Your code works perfectly and the code I gave works with gd.db and is also described in the wiki exactly the same way.

https://gambaswiki.org/wiki/doc/db-quoting

Of course it is nonsense that there are two asterisks ** , of course there is only one, which stands for all columns.

Therefore, I would now clearly say that it is a bug in the DB.Stubst function.
But it would be great if someone else could confirm this.


And I have to set the inverted commas in the quote functions, otherwise I get an error.

unknown identifier

Your code is brilliant. Thank you very much.