Database connection so frustrating

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

LeeC
Posts: 12
Joined: Thu Oct 30, 2014 5:11 pm

Database connection so frustrating

Post by LeeC » Thu Oct 30, 2014 5:22 pm

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 256
Joined: Sun May 27, 2007 8:19 pm

Re: Database connection so frustrating

Post by Martin Koob » Thu Oct 30, 2014 6:03 pm

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
Posts: 12
Joined: Thu Oct 30, 2014 5:11 pm

Re: Database connection so frustrating

Post by LeeC » Thu Oct 30, 2014 6:08 pm

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

Martin Koob
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 256
Joined: Sun May 27, 2007 8:19 pm

Re: Database connection so frustrating

Post by Martin Koob » Thu Oct 30, 2014 6:14 pm

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
Posts: 12
Joined: Thu Oct 30, 2014 5:11 pm

Re: Database connection so frustrating

Post by LeeC » Fri Oct 31, 2014 9:20 am

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 256
Joined: Sun May 27, 2007 8:19 pm

Re: Database connection so frustrating

Post by Martin Koob » Fri Oct 31, 2014 11:18 am

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
Posts: 12
Joined: Thu Oct 30, 2014 5:11 pm

Re: Database connection so frustrating

Post by LeeC » Fri Oct 31, 2014 2:42 pm

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 858
Joined: Wed Jun 24, 2009 1:17 pm
Contact:

Re: Database connection so frustrating

Post by dave.kilroy » Fri Oct 31, 2014 4:58 pm

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
"...this is not the code you are looking for..."

LeeC
Posts: 12
Joined: Thu Oct 30, 2014 5:11 pm

Re: Database connection so frustrating

Post by LeeC » Fri Oct 31, 2014 6:15 pm

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

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

Re: Database connection so frustrating

Post by bangkok » Sun Nov 02, 2014 3:21 am

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
Posts: 12
Joined: Thu Oct 30, 2014 5:11 pm

Re: Database connection so frustrating

Post by LeeC » Mon Nov 03, 2014 9:41 am

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 937
Joined: Fri Aug 15, 2008 7:15 am

Re: Database connection so frustrating

Post by bangkok » Mon Nov 03, 2014 12:00 pm

LeeC wrote: 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
Posts: 12
Joined: Thu Oct 30, 2014 5:11 pm

Re: Database connection so frustrating

Post by LeeC » Mon Nov 03, 2014 12:17 pm

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 858
Joined: Wed Jun 24, 2009 1:17 pm
Contact:

Re: Database connection so frustrating

Post by dave.kilroy » Mon Nov 03, 2014 12:25 pm

I can confirm your date.lc and test.lc scripts work fine from here...
"...this is not the code you are looking for..."

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

Re: Database connection so frustrating

Post by bangkok » Mon Nov 03, 2014 1:28 pm

LeeC wrote: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.

Post Reply