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

Oliver Simith · Oct 2, 2022

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.

Mahares · Oct 4, 2022

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

Erel · Oct 2, 2022

Oliver Simith said:
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 · Oct 2, 2022

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

DonManfred said:

B4X:

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

Oliver Simith · Oct 2, 2022

Erel said:
No. Column names cannot be parameterized.

Thanks, Erel, for your timely reply.

Mahares · Oct 2, 2022

Oliver Simith said:
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.

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

Oliver Simith · Oct 3, 2022

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

emexes · Oct 3, 2022

Oliver Simith said:
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)"

emexes · Oct 3, 2022

Oliver Simith said:
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 ))

Oliver Simith · Oct 3, 2022

emexes said:

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.

AnandGupta · Oct 3, 2022

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 · Oct 3, 2022

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

What is the variable ChangeFrom. It is not defined.

emexes · Oct 3, 2022

Mahares said:
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 · Oct 4, 2022

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

emexes · Oct 4, 2022

Mahares said:
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 · Oct 4, 2022

emexes said:
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.

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

Oliver Simith

Member

Mahares

Erel

B4X founder

Oliver Simith

Member

Oliver Simith

Member

Mahares

Expert

Oliver Simith

Member

emexes

Expert

emexes

Expert

Oliver Simith

Member

AnandGupta

Expert

Mahares

Expert

emexes

Expert

Mahares

Expert

emexes

Expert

Mahares

Expert

Similar Threads

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

Member

B4X founder

Member

Member

Expert

Member

Expert

Expert

Member

Expert

Expert

Expert

Expert

Expert

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency