Using MySQL with on-rev database

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
Ron Zellner
Posts: 106
Joined: Wed May 31, 2006 9:56 pm
Contact:

Using MySQL with on-rev database

Post by Ron Zellner » Tue Apr 20, 2010 7:43 pm

I have been using a script for accessing a MySQL database on my local server- I am now trying to move it to my on-rev account. I created a database in the on-rev account and then created a new user name (and password) and assigned it to the database. I modified the script from the original functioning stack with what I think are the appropriate changes, however, I cannot get it to accept the password when connecting:

It returns this Error message: Access denied for user 'theNewLogonID'@'myName.myUniversity.edu' (using password: YES)

I don't have the same phpMyAdmin tools as in the University server so I can't check the db permissions the way I am used to.

This is the essence of the code (with changes to the names):

Code: Select all

 global dbresult, dbID, dbSQL, sqlResult
   
   Answer "Connected to the Internet and ready to submit?" with "Yes" or "No"
   if it ="Yes" then
      put revOpenDatabase("MySQL", "MyAccount.on-rev.com","LearningDemo","theNewLogonID","theNewPassword")  into dbresult
      put dbresult
      answer dbresult
      if dbresult is a number then
         put dbresult into dbID
      else
         answer dbResult
      end if

  put "INSERT INTO Flying VALUES ('" & field "SubmitDate"  & "'," into dbSQL
      put "'" & field "SubmitTime"  & "'," after dbSQL
      put "'" & field "FirstName"  & "'," after dbSQL
      put "'" & field "LastName"  & "'," after dbSQL


      put "'" & field "TotalTime" & "')" after dbSQL

  if last char of dbSQL = ";" then delete last char of dbSQL
      revExecuteSQL dbID, dbSQL
      put the result into sqlResult
      
      
      if sqlResult is a number then
         answer "Submission OK: " & sqlResult & " record(s) altered." with "OK"
      else
         answer  sqlResult with "OK"
      end if      
   end if
I tried creating another user and password, but I get the same result.
Does anyone recognize the source of the error?
This is a scripting format that I found and modified a while back, so if there is a better way to do this I'm open to it.
Thanks,

Ron Zellner
Posts: 106
Joined: Wed May 31, 2006 9:56 pm
Contact:

Re: Using MySQL with on-rev database

Post by Ron Zellner » Tue Apr 20, 2010 10:40 pm

I have had a similar problem on my servers related to "localhost" and submission from a Revolution stack. What do I need to do to access the 0n-rev SQL database from a Revolution Stack?

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

Re: Using MySQL with on-rev database

Post by bangkok » Wed Apr 21, 2010 1:41 pm

check :

http://forums.runrev.com/phpBB2/viewtop ... 861#p21604

and be sure to declare the IP range for users who might connect to your mysql DB, in the "remote MySQL" function, on the CPanel menu page.

Ron Zellner
Posts: 106
Joined: Wed May 31, 2006 9:56 pm
Contact:

Re: Using MySQL with on-rev database

Post by Ron Zellner » Wed Apr 21, 2010 2:20 pm

I accessed a web page on my server and then used the IP address that I found on the cPanel home page "Last login from". That is the only IP address I could find.
Using that I don't get the "access denied" message, but it goes busy quite a while and then finally gives error:

Can't connect to MySQL server on '75.xxx.xx.xxx ' (60)

Type revdberr,invalid connection id
Object Submit The Data
Line revExecuteSQL dbID, dbSQL
Hint revExecuteSQL


I used the % wildcard symbol in the "remote MySQL" function which I believe allows any host to connect.

I'm using the ID and password of the user I created for the database, not my main ID & Password.
Last edited by Ron Zellner on Wed Apr 21, 2010 5:53 pm, edited 1 time in total.

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

Re: Using MySQL with on-rev database

Post by bangkok » Wed Apr 21, 2010 3:01 pm

I forgot : you want from a .irev file on your on rev account to access your local MySQL DB, right ?

In that case, you have to change the address by using "localhost" :

put revOpenDatabase("mysql","localhost","yourDBname","yourLogin","yourPassword") into dbID

Ron Zellner
Posts: 106
Joined: Wed May 31, 2006 9:56 pm
Contact:

Re: Using MySQL with on-rev database

Post by Ron Zellner » Wed Apr 21, 2010 5:01 pm

No, this is a stack that will be used as a standalone or as a .revlet on a web page.
Will that work?

I haven't tried any .irev file applications yet.

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

Re: Using MySQL with on-rev database

Post by bangkok » Thu Apr 22, 2010 7:30 am

Ron Zellner wrote:No, this is a stack that will be used as a standalone or as a .revlet on a web page.
Will that work?.
Sure. I use it a lot.

Sorry for the misunderstanding. So back to basic.

-have you checked the privileges of your db user ? through Cpanelx, button "MySQL databases", and after click on the user

-have you linked your db user to the db you wish to connect to (function "Add User To Database")

-last but not least you should perhaps create a new db user with a very simple password.

Ron Zellner
Posts: 106
Joined: Wed May 31, 2006 9:56 pm
Contact:

Re: Using MySQL with on-rev database

Post by Ron Zellner » Fri Apr 23, 2010 3:42 pm

I created a new user, assigned it to the database, and gave it all privileges to the database.
Still get the basic error:
Type revdberr,invalid connection id
Object Submit The Data
Line revExecuteSQL dbID, dbSQL
Hint revExecuteSQL



This is the specific error:
Access denied for user 'XX'@'cdm-75-###-###-###.xxxxxx.dh.suddenlink.net' (using password: YES)

This is a different address than was returned earlier.

Is this likely a problem in the database settings (user/PW)
or is it a problem in the general access settings in the "Remote Access Hosts" in the "Remote SQL Panel"?
Is there documentation on how to set the wildcards there?
Thanks

Post Reply