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

[Oliver Simith]

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?

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:

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.

 

[Erel]

I am just curious if there's an alternative way to do it if I want to use "?" marks.

No. Column names cannot be parameterized.

 

[Oliver Simith]

Hi, DonManfred, thank you for your reply. Sadly it gave the same error message.

sql.ExecNonQuery2("CREATE TABLE persons(? text, ? integer, ? integer)",Array As Object("Name","Age","Score"))

 

[Oliver Simith]

No. Column names cannot be parameterized.

Thanks, Erel, for your timely reply.

 

[Mahares]

Thanks, Erel, for your timely reply.

You got the correct solution by Erel of course, but for information you can refer to the columns by their variables.

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)"$)

 

[Oliver Simith]

You got the correct solution by Erel of course, but for information you can refer to the columns by their variables.

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

 

[emexes]

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?

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:

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

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

 

[emexes]

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?

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:

'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:

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

sql.ExecNonQuery(MP( "CREATE TABLE persons(? text, ? integer, ? integer)", fields ))

 

[Oliver Simith]

You could roll your own:

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

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.

 

[AnandGupta]

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 ?

 

[Mahares]

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

What is the variable ChangeFrom. It is not defined.

 

[emexes]

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 )

 

[Mahares]

ways to fix it

You can optimize your sub some more if you want to since you like to tinker:

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)

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

 

[emexes]

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:

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

 

[Mahares]

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.