Android Question String Literals for SQL Commands

[rodmcm]

I have a DB with 53 columns and wanted to create a generalised method to create a SQL table and B4xTableView
The following shows the B4X Col Names which I use for the headers in the table and the DB items.. Only the first three are shown

    Starter.DBName = "CABLES"
    Starter.TotColNumber=3               '53 in final count
    Starter.FrozenCols=3
    Starter.FirstColCheck=False
    Starter.ColNames(0) = "No_CORES"
    Starter.ColNames(1) = "AREA_MM2"
    Starter.ColNames(2) = "TYPE"
    
    Starter.ColDataTypes(0) = "INT"
    Starter.ColDataTypes(1) = "TEXT"
    Starter.ColDataTypes(2) = "TEXT"

This shows query for the setup of the SQL

    Query = $"CREATE TABLE CABLES (${Starter.ColNames(0)} TEXT,${Starter.ColNames(1)} TEXT,${Starter.ColNames(2)} TEXT) "$
    Starter.SQLCABLE.ExecNonQuery( Query )

Creates the DB and works fine, and I can write to B4X table using the same headers

However if I try to develop a generalised solution for a much larger SQL DB then problems

This is one solution using the above three items

    Query = "$" & Chr(34) & "CREATE TABLE CABLES ("
    For i = 0 To Starter.TotColNumber-2
        Query = Query & "${Starter.ColNames(" & i & ")} TEXT , "
    Next
    Dim i As Int =  Starter.TotColNumber-1
    Query = Query & "${Starter.ColNames(" & i & ")} TEXT)" & Chr(34) & "$"               ' adds in final element without comma

As I can't enter as string value "$"" I have had to use "$" & chr(34).
This logs identically to the first Query but SQL does not recognize the $ character..

I am sure that writing this way negates the literal operator $" "$
Is there any other way to approach this?

 

[LucaMs]

Use DBUtils2 CreateTable.

 

[mcqueccu]

To insert variables you only need to use ${variable name}

 

[alwaysbusy]

You are confusing the Smart String literals value with the 'compiled' value.
You are giving the Query variable a 'B4J source code' value. When you run your B4J code, you get 'Smart String text', not a normal string 'compiled' from your Smart string. SQL does not know Smart Strings, it is a typical B4J concept Erel invented in the B4J language.

The result of a Smart String is just a normal string after compiling:

dim Num as int = 5
Log($"This is number ${num}!"$) ' -> This is number 5 and not: $This is number ${5}!$

So your generic method is as simple as this:

    Dim Query As String
    Query = "CREATE TABLE CABLES ("
    For i = 0 To Starter.TotColNumber-1
        If i > 0 Then
            Query = Query & ", "
        End If
        Query = Query & Starter.ColNames(i) & " " & Started.ColDataTypes(i)
    Next
    Query = Query & ")"

The result is the same as your original 'compiled' Smart String and can be understood by MySQL.

Alwaysbusy

 

[rodmcm]

Brilliant........

 

[Mahares]

generalised method to create a SQL table

And if you are mesmerized by Smart String Literals like I am, here is a very similar to @alwaysbusy code using smart string literals:

Query = $"CREATE TABLE CABLES ( ${Starter.ColNames(0)} ${Starter.ColDataTypes(0)}"$
    For i = 1 To Starter.TotColNumber-1        
        Query = $"${Query}, ${Starter.ColNames(i)} ${Starter.ColDataTypes(i)} "$
    Next
    Query = $"${Query} )"$

 

[rodmcm]

Thanks.. They certainly are powerful,

Your combined suggestions lead quickly to generalized solutions to create and modify anySQL DB
Here is some code that
a) sets up the field headers in a form for both SQL and B4XTable use
b) Creates an SQL data base from a generalized sub
c) Adds data from a generalized sub

I would be interested in your comments

'Headers for the Equipment database
Sub EquipmentHeaders
    Log("Equipment Headers")
    Starter.DBName = "EQUIPMENT"
    Starter.TotColNumber=5
    Starter.FrozenCols=0
    Starter.FirstColCheck=True
    Starter.ColNames(0) = "EQUIP"
    Starter.ColNames(1) = "DESCR"
    Starter.ColNames(2) = "COMMENT1"
    Starter.ColNames(3) = "COMMENT2"
    Starter.ColNames(4) = "COMMENT3"
        
    Starter.ColDataTypes(0) = "TEXT"
    Starter.ColDataTypes(1) = "TEXT"
    Starter.ColDataTypes(2) = "TEXT"
    Starter.ColDataTypes(3) = "TEXT"
    Starter.ColDataTypes(4) = "TEXT"
End Sub

' Generalised sub to Create a database
Sub CreateSQLDB(DBName As String, TotColNo As Int)
    Private Query As String  = "CREATE TABLE " & DBName & "("
    For i = 0 To TotColNo-1
        If i > 0 Then
            Query = Query & ", "
        End If
        Query = Query & Starter.ColNames(i) & " " & Starter.ColDataTypes(i)
    Next
    Query = Query & ")"
    Starter.SQLEquip.ExecNonQuery(Query )
End Sub

'Generalised sub to Add a list to a database
Sub AddValues (DBname As Object, TotColNo As Int, Values As List)
    Dim Query As String = "INSERT INTO " & DBname & " VALUES ("
    For i = 0 To  TotColNo-1
        If i > 0 Then
            Query = Query & "?,"
        End If
    Next
    Query = Query & "?)"
    Starter.SQLEquip.ExecNonQuery2(Query, Values)
End Sub


'Creates the  Database for the first time and adds some equipment
Sub CreateEquipDB
    Log("Create Equipment SQL")
    'Create DB
    CreateSQLDB(Starter.DBName, Starter.TotColNumber)                'Creates the database

    'Insert some start values
    Dim StartEquip As List
    StartEquip. Initialize
    StartEquip.Add("BearLarge")
    StartEquip.Add("Rupert Bear")
    StartEquip.Add("Paddington")
    StartEquip.Add("Station")
    StartEquip.Add("Paraquay")
    AddValues(Starter.DBName,Starter.TotColNumber,StartEquip)

End Sub

 

[OliverA]

I would be interested in your comments

When building strings, I would suggest using StringBuilder. See docs for reasons why: https://www.b4x.com/android/help/core.html#stringbuilder

 

[Mahares]

I would be interested in your comments

1. Sub AddValues (DBname As Object, TotColNo As Int, Values As List) should be:

Sub AddValues (DBname As Object,  Values As List)

and this one in the same sub For i = 0 To TotColNo-1 should be:

For i = 0 To  values.size-1  ' No need to have a separate parameter for TotColNo

.

2. I like to refer to the table columns names and data type this way: using fewer lines:

ColNames = Array As String("EQUIP", "DESCR", "COMMENT1", "COMMENT2", "COMMENT3")
    ColDataTypes=Array As String("TEXT", "TEXT", "TEXT",  "TEXT", "TEXT")

3. Also, you keep referring to the table as database. As you know the database can have several tables, but you should not refer to the table as database. It is probably just a habit. And of course I prefer smart strings in all statements

 

[rodmcm]

Thank you again... I have so much to learn about this. Also based on your reply to a previous question I have lots of success with smart string literals

Query = $"SELECT ${ColName} FROM ${DBname} WHERE ${ColName} = '${SearchItem}'"$