mySQL connection error

[lrieber]

Hi all,

I need to use LiveCode to build applications that will interact with an existing mySQL database — I just started and quickly ran into a snag. I can't get the database connection to work. I'm following the livecode lesson titled "Connecting to a MySQL database" and it seems simple enough (I'd give the URL to the lesson, but apparently I don't have permission to do that).

I copied and pasted the code directly from the lesson's web site (a copy is below with substitutions for my hosting address, username, and password; I've obviously included these in the original code). I searched this LiveCode database forum, but found nothing to help me.

The database I'm trying to connect with works fine — it's been running for almost a year (and tried it today via a Web site that uses it with no problem). So, I know the data is there and is working fine. Likewise, I am able to connect to it and work with the data just fine with Dreamweaver CS5.5. The hosting company is webhost4life.

I'm guessing I'm overlooking something very simple.

Thanks - Lloyd

=========================================
on mouseUp
-- use a global variable to hold the connection ID so other scripts can use it
global gConnectionID

-- set up the connection parameters - edit these to suit your database
put "[myDatabaseAddress]" into tDatabaseAddress
put "class_reminders" into tDatabaseName
put "[myUserName]" into tDatabaseUser
put "[myPassword]" into tDatabasePassword


-- connect to the database
put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult

-- check if it worked and display an error message if it didn't
-- & set the connection ID global
if tResult is a number then
put tResult into gConnectionID
answer info "Connected to the database." & cr & "Connection ID = " & gConnectionID
else
put empty into gConnectionID
answer error "Unable to connect to the database:" & cr & tResult
end if

end mouseUp

[lrieber]

I should have also provided the error message that is produced:

Unable to connect to the database: Can't connect to MySQL server on '[myDatabaseAddress]' (60)

(I again removed the actual "myDatabaseAddress" from this posting.)

Lloyd

[snm]

Do you use databaseAddress enclosed into [] brackets? If yes, you must remote it.

Marek

[lrieber]

Do you use databaseAddress enclosed into [] brackets? If yes, you must remote it.

Marek

The brackets were not part of the original code. I only put added the brackets to my posting to this forum to make it clear I wasn't giving the actual information.

[bangkok]

The database I'm trying to connect with works fine — it's been running for almost a year (and tried it today via a Web site that uses it with no problem). So, I know the data is there and is working fine. Likewise, I am able to connect to it and work with the data just fine with Dreamweaver CS5.5. The hosting company is webhost4life.

You mean you connect to the distant DB... with Dreamweaver installed on your local PC, the same on which you use LiveCode, right ?

But Dreamweaver can't connect directly to a MySQL DB, it uses instead a PHP server, no ?

Normally, a website that hosts a MySQL DB doesn't allow remote connexions (instead it accepts only local, like 127.0.0.1, through a PHP script for instance).

My point : with your webhost4life pannel control, you should have a look on the MySQL parameters... To check for instance the users privileges, and to see if there is a host "%" (means all)

[lrieber]

The database I'm trying to connect with works fine — it's been running for almost a year (and tried it today via a Web site that uses it with no problem). So, I know the data is there and is working fine. Likewise, I am able to connect to it and work with the data just fine with Dreamweaver CS5.5. The hosting company is webhost4life.

You mean you connect to the distant DB... with Dreamweaver installed on your local PC, the same on which you use LiveCode, right ?

But Dreamweaver can't connect directly to a MySQL DB, it uses instead a PHP server, no ?

Normally, a website that hosts a MySQL DB doesn't allow remote connexions (instead it accepts only local, like 127.0.0.1, through a PHP script for instance).

My point : with your webhost4life pannel control, you should have a look on the MySQL parameters... To check for instance the users privileges, and to see if there is a host "%" (means all)

Yes, I'm able to communicate with the same remote database (on a server located who knows where) with Dreamweaver on the same MacBookPro that has LiveCode. Dreamweaver uses the same basic connection protocol that LiveCode uses: Hostname, database name, username, and password. I'm using these same values for my LiveCode connection. So, I think the fact that I'm able to connect to the remote database with Dreamweaver (via PHP) means that the database should have the user privileges set up to allow me to connect with LiveCode. (As per your advice, I did look for the user privileges option within my control panels in webhost4life (and PhPMyAdmin) and everything (select, insert, update, etc.) were all set to "yes."

Thanks for your thoughts and if you other ideas, I'd love to hear them.

Lloyd

[bangkok]

Yes, I'm able to communicate with the same remote database (on a server located who knows where) with Dreamweaver on the same MacBookPro that has LiveCode. Dreamweaver uses the same basic connection protocol that LiveCode uses: Hostname, database name, username, and password. I'm using these same values for my LiveCode connection. So, I think the fact that I'm able to connect to the remote database with Dreamweaver (via PHP)

I'm afraid you should check again.

Dreamweaver seems unable to "directly" (natively) connect to a MySQL server, but rather seems to use a set of PHP scripts hosted on the same remote server... Actually, it's the PHP scripts which handle the connexions (through a local link 127.0.0.1).

Found on forum :

How can I tell Dreamweaver CS5 to connect via the ODBC driver so I can see the MySQL content in my Dreamweaver project?

You can't - at least not if you want to use Dreamweaver's server behaviors. Dreamweaver uses the original PHP MySQL extension. If you want to connect in any other way, you need to hand code everything yourself.

On the other hand, if you want to see the contents of your remote database in Dreamweaver, define your remote server as the testing server. Dreamweaver uploads a hidden folder to the remote server to connect to the database. Because the script is on your remote server, it's local to the MySQL database, so gets round the security issue. It then sends the details to your local computer.

So eventhough you can see the same parameters (DB name, user name, user pwd)... the method of connexion are different.

LiveCode is designed to handle direct connexions to a MySQL server (distant or local). Your user privileges seem okay... so you should check the list of authorized "host" in MySQL. Add the public Ip address of your laptop, or try to put "%" (but watch out,then your mySQL server will be open to all incoming traffic, even non authorized).

Other solution : some webhosting pannel have an option to activate "remote MySQL connexions".

[lrieber]

Thank you, Bangkok, for your insights on this. I think I now see the issue. I'll contact my hosting company to see about getting remote access to connect to the database directly.

I appreciate your help on this.

Lloyd