Bind a table name?

Hi,
Quick question - is the code below valid?
I normally only insert question marks where the column values get bound, and therefore, was wondering if the Table Name can be bound in the same manner?

I can’t test this, as I am on my iPad, but was wondering if this was possible.

Thank you all in advance.

[code] // PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = db.Prepare(“CREATE TABLE ? (SRef INTEGER PRIMARY KEY, Title TEXT, Code BLOB)”)

// DEFINE THE BIND TYPE
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)

// BIND THE TEXT FIELD VALUES
ps.Bind(0, TitleField.text)

// INSERT THE VALUE INTO THE DATABASE
ps.SQLExecute[/code]

If my above code is indeed valid, am I also correct in thinking that I can create JUST a table in the same manner:

[code]// PREPARE THE STATEMENT
Dim ps As SQLitePreparedStatement = db.Prepare(“CREATE TABLE ?”)

// DEFINE THE BIND TYPE
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)

// BIND THE TEXT FIELD VALUES
ps.Bind(0, TitleField.text)

// INSERT THE VALUE INTO THE DATABASE
ps.SQLExecute[/code]

No. You can only bind data values, not table or column names.

Sad Face

So if I wanted a button which created a table (named after whatever is entered into a text field) - how would I go about that?
Any pointers :slight_smile:

What is it that makes you want a new table each time ?
That seems like a less than optimal design

Norman, Just trying to learn new things, and wondered how I could make a small app which allows me to create a database from scratch.

yeah you can’t use binding with DDL (data definition language) statements like create table create index etc
you have to write sql

DML (data manipulation language) statements like insert, update, select you can use hidings

bindings are for VALUES (column values) not portions of statements

personally I’d go grab one of the many tools around that manipulate sqlite databases
OR if you’re on OS X you can just use the command line in terminal :stuck_out_tongue:

sometimes starting off with that mode is best as you have to understand it well to use it
then once you do moving to writing code that writes the sql is a lot simpler

Norman, I’m on OS X and currently use SQLite Database Browser to create and edit database files.

I just thought it would be a good learning experience to make my own small app which does a similar thing, and therefore, wondered if it was possible to dynamically create a table in Xojo, as opposed to hardcoding the table name.

Guess it is not possible then.

Thanks anyway Norman - I will use my time to learn a different aspect of Xojo.
The Dictionary class looks interesting :slight_smile:

Richard,
you can create databases and tables within your code at runtime, but not with bindings. Use sql commands to create a table.

Please have a look into the docs
http://documentation.xojo.com/index.php/Database.SQLExecute

Torsten - I looked there, but there is only an example of a hardcoded table name, and I need the table name to be dynamic (take the name from a text field) - that’s why I was wondering if it was possible :slight_smile:

Try this

Dim sql As String

sql = "CREATE TABLE " + TableNameTextField.Text + " (ID INTEGER NOT NULL, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID));"

TableNameTextField is the Textfield you used to enter the tablename. If other people make the table name entries, you should be prepared for SQL injections (more to this in other threads in this forum).

Thanks Torsten - that was exactly what I was trying to achieve.
Regarding the SQL injections - I hate needles :frowning:

Hi,
Is it possible to create just a table (without columns) - such as in the code below?

Dim sql As String = "CREATE TABLE " + TableNameTextField.Text; db.SQLExecute(sql)

Or, does a table have to have columns defined at the time the table is created - such as:

Dim sql As String = "CREATE TABLE " + TableNameTextField.Text + " (Id INTEGER NOT NULL PRIMARY KEY, Title TEXT, Code TEXT);" db.SQLExecute(sql)

Thanks.

no
tables always have columns
see http://sqlite.org/lang_createtable.html
you can create a new table as the result of a sql select OR by specifying the columns

Norman, I didn’t think I could, but I thought I would ask anyway.
I was hoping I could use a button to create just a table, and then later use another button to create the columns.
Thank you.

no its a one shot deal
once created you can alter it but some db’s (like sqlite) don’t let you remove columns - just add

if you’re still just looking to play around with a DB the command line is not bad or some of the free or inexpensive tools
get used to writing the actual sql then move on to how to write code to do that
really it might be best as then you can just avoid trying to learn 2 things at once :stuck_out_tongue:

How does other software allow users to click on a button in order to create just a table??

For example:

Click on the create new database button and enter a name for it.
Click on the create new table button, then enter a name for it.
Click on the create a column button, then enter a name and data type.
Click on the create database button - resulting in the actual file being created.

That’s what confused me - I presumed it must be possible, but as you kindly taught me - it isn’t.

The table isn’t actually “created” until the very end. It’s all “in memory” so to speak until you press that last button that writes it all out to the actual database. In other words, when you click “create table”, all that happens is the field is opened to allow you to put in the name. It doesn’t physically create the table yet.

Sounds like you are describing a wizard. Like Tim said you are being walked through building the table template. When you get to the last step a single create table statement is being sent to the Database.

Ahhhhhh - that makes more sense!
So basically I could save all of the user entered strings to variables, and then create an SQLExecute statement using the variables where needed.

Now I am awake it makes much more sense :slight_smile:

Thank you all very much for the clarification - I really appreciate all your help.