Android Question I wonder if this is a correct sentence of creating a table with sql database?

Hello, everyone, I am new to the SQL library. By studying tutorials, I understand that we could use the "?" mark as a placeholder in some SQL Sentences, and then replace the "?" marks with an array.
I wonder if it is also possible to use "?" in a table creation sentence. In other words, is it possible to use "?"marks" in the following sentence?
a problematic sentence:
Private fields(3) As String
fields(0)="Name"
    fields(1)="Age"
    fields(2)="Score"
...
sql.ExecNonQuery2("CREATE TABLE persons(? text, ? integer, ? integer)",fields)' my program boke at this line.

And here's the log:
the log message:
Error occurred on line: 53 (Main)
android.database.sqlite.SQLiteException: near "?": syntax error (code 1 SQLITE_ERROR): , while compiling: CREATE TABLE persons(? text, ? integer, ? integer)

I know it definitely works if I use CREATE sentences in a standard form. I am just curious if there's an alternative way to do it if I want to use "?" marks.
thank you in advance for any comment.
 
Solution
ways to fix it
You can optimize your sub some more if you want to since you like to tinker:
B4X:
Dim fields() As String =Array As String("Name", "Age", "Score")
Log(MP("persons", "? text, ? integer, ? integer", fields ))  'logs: CREATE TABLE IF NOT EXISTS persons (Name text, Age integer, Score integer)
B4X:
Sub MP(TableName As String, Template As String, ChangeTo() As String) As String
    For I = 0 To ChangeTo.Length - 1
        Dim P As Int = Template.IndexOf("?")
        If P < 0 Then
            Exit   
        End If
        Template =  Template.SubString2(0, P) & ChangeTo(I) & Template.SubString(P + "?".Length)
    Next
    Template=$"CREATE TABLE IF NOT EXISTS ${TableName} (${Template})"$
    Return 
End Sub
You got the correct solution by Erel of course, but for information you can refer to the columns by their variables.
B4X:
Dim fields() As String =Array("Name", "Age", "Score")
SQL1.ExecNonQuery($"CREATE TABLE IF NOT EXISTS persons (${fields(0)} text, ${fields(1)} integer, ${fields(2)} integer)"$)
Wow, thanks! This information is very useful for my program
 
Upvote 0

emexes

Expert
Licensed User
I wonder if it is also possible to use "?" in a table creation sentence. In other words, is it possible to use "?"marks" in the following sentence?
a problematic sentence:
Private fields(3) As String
fields(0)="Name"
    fields(1)="Age"
    fields(2)="Score"
...
sql.ExecNonQuery2("CREATE TABLE persons(? text, ? integer, ? integer)",fields)' my program boke at this line.

You could roll your own:

B4X:
Private fields(3) As String
fields(0) = "Name"
fields(1) = "Age"
fields(2) = "Score"

TestExecNonQuery2("CREATE TABLE persons(? text, ? integer, ? integer)", fields)


Sub TestExecNonQuery2(CmdTemplate As String, CmdParams() As String)
    TestExecNonQuery( ReplaceMany(CmdTemplate, "?", CmdParams) )
End Sub


Sub TestExecNonQuery(Cmd As String)
    Log("TestExecNonQuery(""" & Cmd & """")
End Sub


Sub ReplaceMany(Template As String, ChangeFrom As String, ChangeTo() As String) As String
 
    Dim Temp As String = Template
 
    For I = 0 To ChangeTo.Length - 1
        Dim P As Int = Temp.IndexOf(ChangeFrom)
        If P < 0 then
            Exit    'no more to find, may as well finish now
        End If

        Temp = Temp.SubString2(0, P) & ChangeTo(I) & Temp.SubString(P + ChangeFrom.Length)
    Next
 
    Return Temp
     
End Sub

Log output:
Waiting for debugger to connect...
Program started.
TestExecNonQuery("CREATE TABLE persons(Name text, Age integer, Score integer)"
 
Upvote 0

emexes

Expert
Licensed User
I wonder if it is also possible to use "?" in a table creation sentence. In other words, is it possible to use "?"marks" in the following sentence?
a problematic sentence:
Private fields(3) As String
fields(0)="Name"
    fields(1)="Age"
    fields(2)="Score"
...
sql.ExecNonQuery2("CREATE TABLE persons(? text, ? integer, ? integer)",fields)' my program boke at this line.

Now I am thinking it would be better to add a common function like:

B4X:
'MP = Multiple Parameters / Many Parameters
Sub MP(Template As String, ChangeTo() As String) As String
 
    Dim Temp As String = Template
 
    For I = 0 To ChangeTo.Length - 1
        Dim P As Int = Temp.IndexOf("?")
        If P < 0 then
            Exit    'no more to find, may as well finish now
        End If

        Temp = Temp.SubString2(0, P) & ChangeTo(I) & Temp.SubString(P + "?".Length)
    Next
 
    Return Temp
  
End Sub

and then your end of the deal would look like:

B4X:
Private fields(3) As String
fields(0) = "Name"
fields(1) = "Age"
fields(2) = "Score"

sql.ExecNonQuery(MP( "CREATE TABLE persons(? text, ? integer, ? integer)", fields ))
 
Last edited:
Upvote 0
You could roll your own:

B4X:
Private fields(3) As String
fields(0) = "Name"
fields(1) = "Age"
fields(2) = "Score"

TestExecNonQuery2("CREATE TABLE persons(? text, ? integer, ? integer)", fields)


Sub TestExecNonQuery2(CmdTemplate As String, CmdParams() As String)
    TestExecNonQuery( ReplaceMany(CmdTemplate, "?", CmdParams) )
End Sub


Sub TestExecNonQuery(Cmd As String)
    Log("TestExecNonQuery(""" & Cmd & """")
End Sub


Sub ReplaceMany(Template As String, ChangeFrom As String, ChangeTo() As String) As String
 
    Dim Temp As String = Template
 
    For I = 0 To ChangeTo.Length - 1
        Dim P As Int = Temp.IndexOf(ChangeFrom)
        If P < 0 then
            Exit    'no more to find, may as well finish now
        End If

        Temp = Temp.SubString2(0, P) & ChangeTo(I) & Temp.SubString(P + ChangeFrom.Length)
    Next
 
    Return Temp
    
End Sub

Log output:
Waiting for debugger to connect...
Program started.
TestExecNonQuery("CREATE TABLE persons(Name text, Age integer, Score integer)"
Many thanks, emexes! Your approach has opened my mind.
 
Upvote 0

AnandGupta

Expert
Licensed User
Longtime User
What a beauty. Once there is no way.
Then suddenly there are so many that one is spoilt for choice.
Thanks to our selfless members 🙏
 
Upvote 0

emexes

Expert
Licensed User
What is the variable ChangeFrom. It is not defined.

Good point. 🤔

When I simplified ReplaceMany() to MP(), I thought the ChangeFrom parameter was only used once, so I just hard-coded that as string literal "?".

I missed the second use.

There were two obvious ways to fix it, and I went for the more entertaining option (when editing out my mistake, so now it's like it never happened :rolleyes: )
 
Last edited:
Upvote 0

Mahares

Expert
Licensed User
Longtime User
ways to fix it
You can optimize your sub some more if you want to since you like to tinker:
B4X:
Dim fields() As String =Array As String("Name", "Age", "Score")
Log(MP("persons", "? text, ? integer, ? integer", fields ))  'logs: CREATE TABLE IF NOT EXISTS persons (Name text, Age integer, Score integer)
B4X:
Sub MP(TableName As String, Template As String, ChangeTo() As String) As String
    For I = 0 To ChangeTo.Length - 1
        Dim P As Int = Template.IndexOf("?")
        If P < 0 Then
            Exit   
        End If
        Template =  Template.SubString2(0, P) & ChangeTo(I) & Template.SubString(P + "?".Length)
    Next
    Template=$"CREATE TABLE IF NOT EXISTS ${TableName} (${Template})"$
    Return 
End Sub
 
Upvote 0
Solution

emexes

Expert
Licensed User
since you like to tinker:

Guilty as charged, Your Honour!

But the idea of "it would be better to add a common function like" MP(template, params()) was that it could be used with other SQL methods too:

1664847470138.png


or indeed with many situations having a single string parameter where you'd rather use a template + array of parameters.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
idea of "it would be better to add a common function like" MP(template, params())
I made the changes to stay within the original intent of the thread which is: CREATE TABLE. The idea of a common function could be a good topic for a code snippet.
 
Upvote 0
Top