Page 1 of 1

Athentication using Mysql users

PostPosted: Mon Mar 31, 2014 4:46 am
by DaveJ
Hi,
I have an issue where I want to list the users from a Mysql database and import the results into a drop down box.
By executing the following sql instruction it creates variables but cannot open the table or import to a variable.

.Create guest account and set it ability to select from the mysql.user table
dbpExecSQL "mydb" "CREATE USER 'guest'@'localhost' IDENTIFIED BY 'guest';||" ""
dbpExecSQL "mydb" "GRANT SELECT, CREATE TEMPORARY TABLES, CREATE VIEW, SHOW VIEW ON * . * TO 'test'@'localhost';" ""
dbpExecSQL "mydb" "FLUSH PRIVILEGES;" ""
.select user table
dbpExecSQL "mydb" "select User from mysql.user;" ""
dbpOpenTable "mydb" "mysql" ""
.export field list to comma delimited variable.
dbpFieldToVar "mydb" "mysql" "user" "mysqluser" "Delimiter=,;SkipBlanks=Yes;NoDuplicates=Yes;DelimiterInContext=Leave"
dbpShowGrid "mydb" "mysql" "Rectangle25"

When I view the variables in the Debugger I see....
mydb.mysql.user;.$RecCount=9
mydb.mysql.user;.$RecNum=1
mydb.mysql.user;.$State=Browse
mydb.mysql.user;.User=Adam

As soon as I try to export I get the error that the table isn't open.

Does anyone know how I can get this to work?

Thanks

Dave J

Re: Athentication using Mysql users

PostPosted: Mon Mar 31, 2014 9:34 am
by Neosoft Support
I thing you've got the name of the table wrong. Is it "mysql", "user" or "mysql.user"? The debugger info would suggest that it's "mysql.user", but in your code you're using "mysql".

If possible, I would avoid using a period as part of a table name since DBPro uses periods for its variables.

Re: Athentication using Mysql users

PostPosted: Tue Apr 01, 2014 3:41 am
by DaveJ
Hi,
You were absolutely right I needed to open the table mysql.user

Please note though 'mysql.user' is an inbuilt variable within the mysql database, I didnt create it.

There are several other fields that may be of use to other users such as mysql.host and mysql.password

Thanks for the help.

Dave J

Re: Athentication using Mysql users

PostPosted: Tue Apr 01, 2014 10:32 am
by Neosoft Support
Please note though 'mysql.user' is an inbuilt variable within the mysql database, I didnt create it.

There are several other fields that may be of use to other users such as mysql.host and mysql.password


You're right. I had forgotten about those.