How does XOJO deal with table names containing spaces and apostrophe’s.
I can’t seem to get either of the following to work:
tSQL = “DROP TABLE ‘Fred’‘s Table’”
pDB.ExecuteSQL(tSQL)
tSQL = “DROP TABLE ?”
tTable = “‘Fred’‘s Table’”
pDB.ExecuteSQL(tSQL,tTable)
A little guidance would be really appreciated.
Thanks
You need to put double-quotes around the table name. So the raw sql would be:
drop table "Fred's Table"
Then put that string in a Xojo variable requires doubling up the double quotes inside the string, thus:
sql = "drop table ""Fred's Table"""
See:
https://www.sqlite.org/faq.html
and look at Q24.
Thank you for your time.
Your suggestions worked perfectly.
Usually, TABLE names are service data (the developer use a name for the program, not for the user, to refer to the contents); so why are you using human language as name ?
Now, if you fear to forget the TABLE names, read the application design book; or simply write (for your own use) a Data Base Service Helper document where you document the Data Base, all TABLEs, all Records (COlumns) / whatever
Then, it will be easy to refresh your memory from that document. (now and in the future; hours, days, months, years…).
It’s actually a financial programme with accounts.
Accounts details are entered by the user including the account name. These details are kept in a table called accountsLIst.
When the user enters the details (account name) a table is created using that name hence my original post.
I suppose it would be possible to create my own name in programme and redirect to the user entered account name but why reinvent the wheel?
Thanks
not a good idea… you never know what may happen if any special (accentuated) char is used for the name of a person.
you should have a table with ID (integer) and account name
then make another table with all the accounts and records, and use the ID of the owner to identify each record.
No one ever does (at least shouldn’t be doing it) this way, doing that, you are reinventing the wheel. All table names are known and controlled by the app as designed by devs. No access to someone’s tables should be done by guessing a table name for example, or trying to open another app table like a “list_of_users” table by entering a table name in a box. Although you being able to use a multitude of chars and accents in some DB engines (some incompatible with others), to avoid conflicts, table naming have conventions, and they are lowercase_words_separated_by_underlines without special chars in the middle and avoiding accented chars, use pontos_de_atencao instead of “Pontos de Atenção” for example.
As someone with an apostrophe in his name, I can tell you that there are still commercial systems that don’t get this right. I can’t tell you how many services just don’t accept my name.
In fact I was going to follow up on my post but was busy. As others have said or implied, letting users choose names that are inside your code is a mugs game. It will fail at some point.
What you should do is have a name that the user chooses, and when they do, associate an id with it in accountsList. Use that (internal) id to reach into another table of user details, based on their id. Further, this allows the user to change their account name without you having to do big work in your database.
accented.
Wovels with accents
Accented chars, now only vowels. E.g. Ç Ñ
And what did you do with homonym (same name and same surname)? And with people who get married?
Definitively not a good idea
A login id (what Jean-Yves called “account name”) and password unique identify you. So you can have an user sebremy and another sebast, each one with their respective passwords, but coincidentally both share the same Name Surname, “Sebastien Remy”, but are different persons and users.
Mistyping. Accented chars, not only vowels. E.g. Ç Ñ
In short: stay with ASCII characters only (value 33 thru 122 *), do not start with a number, do not use ponctuation…
BE CAREFUL !
This is what I’d call a bug in Xojo’s DB code. I’ve reported that many years ago but the response was “works as designed”.
What Xojo should do is ALWAYS escape table and column names when it builds SQL statements from them. But they don’t, and that’s why you run into these issues.
It’s also happening if you use reserved names such as “order”.
They can all be worked around by you escaping them yourself, though. So, instead of
tTable = "Fred`s Table"
you’d write:
tTable = """Fred`s Table]"""
I agree with them, don’t mess with the user code, if the user makes a bad approach, a DB exception will teach them. But, for example, the user says correctly SELECT user_name, current_date FROM my_users in a POSTGRESQL DB, and Xojo tries to be some kind of smart azz, it can possibly break it ending with something like a SELECT “user_name”, “current_date” FROM “my_users” (ERROR: column “current_date” does not exist)
Rick, you don’t understand what I am saying. If you write the SQL yourself, then of course that should remain unchanged. What Xojo does wrong is when you let Xojo construct the SQL out of the parameters you give it.
Yep, I’m not understanding. Could you please show a sample?