Database connection so frustrating

[LeeC]

First of I should say I know next to nothing about programming though I have dabbled a little over the years. Over the last week I have been trying to learn Livecode which was going reasonably well until I stumbled on a forum post about database security and not having your SQL credentials in the main stack. This made sense so I looked up on how to combat this and using livecode scripts on livecode server seemed to make sense. This is where my frustration has started because no matter what I do I am not able to get it work.

The server is configured and appears to be working in so far the test script displays correctly. The error I get is as follows

• file "/var/www/html/includes/db_connect.lc"
  row 14, col 6: Function: error in function handler (revOpenDatabase)
  row 14, col 6: put: error in expression

My Code ( edited for security reasons )

Code: Select all

<?lc
global gConnectionID

-- setup error reporting
	set the errorMode to "inline"

-- set up the connection parameters
	put "xxxx" into tDatabaseAddress
	put "xxxx" into tDatabaseName
	put "xxxx" into tDatabaseUser
	put "xxxx" 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
?>

Any help would be gratefully received.

[Martin Koob]

In the dictionary it shows the database types as all lower case. I am not sure if it is case sensitive but try 'mysql' instead of "MySQL" for the database parameter.

Martin

[LeeC]

Thanks for the reply Martin, unfortunately it hasn't made a difference.

[Martin Koob]

OK.

I a site running on on-rev and this is the line of code to connect to the database.

put revOpenDatabase("mysql", "localhost", dbName, dbUser, dbPass) into DB

Looks basically the same as yours. Have you tried "localhost" for the database address?

Martin

[LeeC]

Hi,

I have tried everything from 127.0.0.1, localhost and the public IP of my server.

Also, I have tried populating the connector with all the details. The script works fine if I do it from my desktop specifying the public IP.

[Martin Koob]

Here is another post on connecting with mySQL on on-rev. Maybe there are some ideas there.

http://forums.livecode.com/viewtopic.php?f=12&t=5226

Martin

[LeeC]

Still cant get it working. I have written several different scripts trying to get this to work. Checked all livecode server file and even set them to 777 but still get the same error from the webpage or from the command line.
Any other suggestions for a quickly balding man.

[dave.kilroy]

Hi Leec - this is from a script (with valid credentials) that is currently in use...

Code: Select all

<?lc
put revOpenDatabase ("mysql","localhost","db_name","user_name","password") into tConID

put "statusID" into tFieldName
put $_GET["status"] into tStatus

put "SELECT name,memberid from members WHERE statusID='" & tStatus & "'" into tSQL

if tConID is a number then
  put revDataFromQuery(tab, return, tConID, tSQL) into tList
  put tList
end if
put empty
revCloseDatabase tConID
?>

I can't see anything obviously wrong with your script so in your place I would check every single little thing (like trying with no space after the commas, lower case "mysql", use of "localhost", values in the variables in your login credentials, extra spaces or other characters spoiling things) and insert sequential 'puts' and run the script again and again to check values etc at each single stage...

Good luck!

Dave

[LeeC]

Thanks Dave ill give it a try when I get home this evening.

[bangkok]

First, don't be frustrated. Many of use use MySQL along with LiveCode Server, on VPS, On-Rev, dedicated server, etc.

It works perfectly.

You should perhaps explain and describe your environnement server.

What is it ? Linux ? Windows ? VPS ? Shared server ?

What about the MySQL server ? Working fine for other apps ? Can you connect to it, from let's say a SQL client like HeidiSQL, with your login and password ? Do you have the ad hoc rights ?

[LeeC]

Hi,

As I said I am a beginner to all of this but I working in the computer environment. The server I have is a virtual server which is hosted with Digital Ocean. The server is Linux Ubuntu 14 installed as a LAMP server. I can connect to MySQL with PHPMyadmin and I have tried my script with the root credentials and that produces the same error.
Creating a user that is allowed from and IP address to connect and using the same script from my desktop I can connect fine.

If I can provide more information that will enable to you help me please let me know.

Thanks
Lee

[bangkok]

The server I have is a virtual server which is hosted with Digital Ocean. The server is Linux Ubuntu 14 installed as a LAMP server. I can connect to MySQL with PHPMyadmin and I have tried my script with the root credentials and that produces the same error.
Creating a user that is allowed from and IP address to connect and using the same script from my desktop I can connect fine.

That sounds perfect.

So I think it allows you to "narrow" the source of the problem.

-the installation of the LC files on your server.

Is your LC server working with a simple script (without any call to the database) ?
Like :

Code: Select all

<?lc
put the date
?>

If yes. Then have you created correctly the directories "externals" and "drivers" ? Do they contain the proper files ? revdb.so and dbmysql.so with 755 for the rights ?

[LeeC]

Thanks again for the assistance.

Yes I believe the server configuration is correct.
The date script can be run correctly here 178.62.12.137 / date.lc and the Hello world script to 178.62.12.137 / test.lc

The externals and drivers are installed into /usr/lib/cgi-bin and I have chmod them to 755 again

[dave.kilroy]

I can confirm your date.lc and test.lc scripts work fine from here...

[bangkok]

The externals and drivers are installed into /usr/lib/cgi-bin and I have chmod them to 755 again

-And the file livecode-server is at the same level ?

-Have you tried to put "MySQL" in small letters ?

Code: Select all

put revOpenDatabase("mysql","127.0.0.1","xxxxxxx","yyyyyy","zzzzzz") into tResult

-what is the version number of your MySQL server ?

Addendum : i just managed to reproduce the error you get.

Only 1 way : by putting a bad CHMOD on the externals directory !

You need to put 755 on the "externals" directory.