Page 1 of 1
name myswl table from variable
Posted: Tue Nov 26, 2019 9:19 am
by paulalsmith1000
Hi People
I'm not sure if this is the right section of the forum, but I have a quick question.
Could someone tell me the syntax to name a mysql table from a variable (the one in question is hosted by livecodehosting.com:-
command databaseCreateTables
put tclientname & taccountkey into tTableName
put "CREATE TABLE "& tTableName &" (username varchar(32), password varchar(32))" into tSQL
revExecuteSQL tDatabaseID_members, tSQL
end databaseCreateTables
The above doesn't work, but if I just name the table manually its fine?
Any help appreciated.
Kind regards
Paul
Re: name myswl table from variable
Posted: Tue Nov 26, 2019 11:54 am
by bangkok
If you do not control the way you create tTableName, you'll continue to be in the dark ... In other words : what is the content of the 2 variables tclientname and taccountkey ?
You should begin here.
So in your script :
Code: Select all
command databaseCreateTables
put tclientname & taccountkey into tTableName
answer "Name = "&tTableName
put "CREATE TABLE "& tTableName &" (username varchar(32), password varchar(32))" into tSQL
revExecuteSQL tDatabaseID_members, tSQL
end databaseCreateTables
Re: name myswl table from variable
Posted: Tue Nov 26, 2019 5:14 pm
by paulalsmith1000
Hi Bangkok
Thanks for your reply.
Sorry my post was a bit brief (I took out the bits I didn't think were relevant), the whole command is below. the 2 variables are global.
The put command shows the right name.
command databaseCreateTables
put tclientname & taccountkey into tTableName
put tTableName
put "CREATE TABLE "& tTableName &" (username varchar(32), password varchar(32))" into tSQL
revExecuteSQL tDatabaseID_members, tSQL
if the result is a number then
answer info "New record added."
else
answer error "There was a problem adding the record to the database:" & cr & the result
end if
end databaseCreateTables
Kind regards
Paul
Re: name myswl table from variable
Posted: Wed Nov 27, 2019 10:43 am
by bangkok
paulalsmith1000 wrote: ↑Tue Nov 26, 2019 5:14 pm
the whole command is below. the 2 variables are global.
The put command shows the right name.
In that case, what the result says ?
Because if tTableName is okay, but the table creation stilll "
doesn't work"... it means you have a SQL error, hence "the result" should tell you what is this error.
In any case, you need to control everything : the input (the SQL query) and the output (the result).
For any type of debug (and help) you have to remember : we can not guess what your variables contain, and we can not guess if your connexion to the database is ok, and we can not see the error messages that could occur : you are in the front line, and in front of the screen.
You have to give us all those feedbacks.
Just place in your script:
Last but not least : have you checked the database user's permissions (MySQL server) ?
And what about the result when you open the connexion to the database ?
Code: Select all
put revOpenDatabase("mysql",dbAddr,dbName,dbUser,dbPass,,,,) into tDatabaseID_members
answer tDatabaseID_members
Re: name myswl table from variable
Posted: Wed Nov 27, 2019 3:34 pm
by paulalsmith1000
Thanks Bangkok
Sorry I'm a bit of an amateur at this. The reason I posted in the first place was because it didn't work and then I googled a bit and from what I read it suggested that you couldn't use variables to name tables in Mysql, unless you used method statements. So from that point on I was a bit distracted in that direction.
As it turns out, thanks to your help it was something painfully simple; the name part of the variable was "paul smith" and sql doesn't like the space in the middle, so I removed the space with 'replace' and it works!!!!!
Thanks again
Paul