Android Question String Literals for SQL Commands

rodmcm · Apr 25, 2020

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

B4X:

    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

B4X:

    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

B4X:

    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 · Apr 26, 2020

Use DBUtils2 CreateTable.

mcqueccu · Apr 26, 2020

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

alwaysbusy · Apr 26, 2020

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:

B4X:

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:

B4X:

    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 · Apr 26, 2020

Brilliant........

Mahares · Apr 26, 2020

rodmcm said:
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:

B4X:

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 · Apr 27, 2020

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

B4X:

'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 · Apr 27, 2020

rodmcm said:
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 · Apr 27, 2020

rodmcm said:
I would be interested in your comments

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

B4X:

Sub AddValues (DBname As Object,  Values As List)

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

B4X:

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:

B4X:

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 · Apr 28, 2020

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}'"$

Android Question String Literals for SQL Commands

rodmcm

Active Member

LucaMs

Expert

mcqueccu

Well-Known Member

alwaysbusy

Expert

rodmcm

Active Member

Mahares

Expert

rodmcm

Active Member

OliverA

Expert

Mahares

Expert

rodmcm

Active Member

Similar Threads

Android Question String Literals for SQL Commands

Active Member

Expert

Well-Known Member

Expert

Active Member

Expert

Active Member

Expert

Expert

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency