name myswl table from variable

This is the place to post technical queries about SQL Yoga

Moderators: FourthWorld, heatherlaine, Klaus, robinmiller, trevordevore

Post Reply
paulalsmith1000
Posts: 58
Joined: Sat Jun 15, 2019 10:09 am

name myswl table from variable

Post by paulalsmith1000 » Tue Nov 26, 2019 9:19 am

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

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 937
Joined: Fri Aug 15, 2008 7:15 am

Re: name myswl table from variable

Post by bangkok » Tue Nov 26, 2019 11:54 am

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

paulalsmith1000
Posts: 58
Joined: Sat Jun 15, 2019 10:09 am

Re: name myswl table from variable

Post by paulalsmith1000 » Tue Nov 26, 2019 5:14 pm

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

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 937
Joined: Fri Aug 15, 2008 7:15 am

Re: name myswl table from variable

Post by bangkok » Wed Nov 27, 2019 10:43 am

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:

Code: Select all

answer tSQL

Code: Select all

answer the result
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

paulalsmith1000
Posts: 58
Joined: Sat Jun 15, 2019 10:09 am

Re: name myswl table from variable

Post by paulalsmith1000 » Wed Nov 27, 2019 3:34 pm

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

Post Reply

Return to “SQL Yoga”