How to connect to local MySQL

[dave_probertGA6e24]

Hi Michael,

Glad to hear the LC code worked (I assume you meant it worked from the iPhone).

The data returned should be all of the content of the table 'Table1'

Code: Select all

put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData

That would put each column of data separated by a 'tab' and with each row separated by a 'return'. My guess is that either the field is not big enough to show all the data (multiple lines) or it's using the tab char to put it into columns in the field.

Try changing the:

Code: Select all

put tData into field "Data"

to

Code: Select all

put tData into msg

and then look in the message box.

Cheers,
Dave

[ECNU4271]

Sadly, the codes only work on Mac.

It doesn't work on iOS simulator or iPhone.

I guess that's probably because "Applications/MAMP/tmp/mysql/mysql.sock" is being used.
iPhone doesn't know what it is?

[ECNU4271]

The fun thing is that when I change the "localhost:8888" into "192.168.0.7" in the LiveCode in my Mac. It doesn't work!

[ECNU4271]

The error msg is : Host '192.168.0.7' is not allowed to connect to this MySQL server

WHY?

[ECNU4271]

Great, I know why.

I didn't tick MySQL in the standalone settings when I first run the app on iP

[dave_probertGA6e24]

Hi Michael,

Yeah, I suspected that might be the case. Looks like you will either have to install the Livecode server or use PHP (or another server language).

Basically with accessing the server you are basically going to 'print' the results of a database call (from whatever language) to the output (a browser window is a direct view onto it). When using a call from Livecode on the iphone the browser is not part of the equation and the output is picked up directly by the caller (ie the iphone Livecode app).

On the iphone you simply do a:

Code: Select all

put URL "http://192.168.1.2/hithere.php" into tData

and the server that has the 'hithere.php' file will run it and send the output back to the livecode.

You could try it with the current hithere.php and a couple of lines of code on the iphone:

Code: Select all

put URL "http://192.168.1.2/hithere.php" into tData
put tData into field "serveroutput"

(obviously have a field called "serveroutput")

If you try the Livecode Server route then it would be much the same, but using Livecode in place of the PHP code from my tutorial example.

I might have a play with installing LC server and converting the code to LC - but not just now!!

Anyway, it's 3:20 am here in Thailand and I need to head to bed.

Good luck with whatever you do.

Cheers,
Dave

PS.
MySQl has to be configured to allow certain ip addresses to access it directly. Somewhere there is a place where you tell it to accept requests from '%' - which covers all access.

[ECNU4271]

Thanks Dave, good night!

I guess I'd just run the iOS simulator on my MAC instead of showing my iPhone for demon.
The simulator accepts localhost

One more question if you'll find this message.

How to get only one specific data from one specific column from one specific table from the database?
I tried :

Code: Select all

put"Account" into tTableName
put "SELECT * FROM" & tTableName & "WHERE Name=Admin" into tSQL

The "Name" here is one of my column name, "Admin" is the data

Why this sentence doesn't work?

Michael

[dave_probertGA6e24]

Hi Michael,

There are a couple of things that are not too good with this code - one of them could simply be a mistyping error.

Code: Select all

put "SELECT * FROM" & tTableName & "WHERE Name=Admin" into tSQL

I note the lack of spaces after the 'FROM' and before the 'WHERE' - this would result in the string containing '...FROMAccountWHERE...' - which is unrecognised by mySQL
The other is that you really should put some quotes around the value part of the last bit: eg. Name='Admin' You can use single quotes quite happily, or (`) back-ticks (usually on the key to the left of '1' on your keyboard).

So the probable better code would be:

Code: Select all

put "SELECT * FROM " & tTableName & " WHERE Name='Admin' " into tSQL

I always find it useful to put a copy of any constructed SQL into the messagebox so I can check it when there is an error.
So adding the line:

Code: Select all

put tSQL & cr after msg

just after you create tSQL would help with the missing spaces problem.

I hope that helps a bit.

Cheers,
Dave

[Simon]

Sorry to be a PTA but:

My app will be used only by me actually

Doesn't that mean just use the built in sqlite?
Though getting mySQL to run locally is cool.

Simon

[dave_probertGA6e24]

Hi Simon,

I think it's more of a learning exercise anyway

If the exercise is just to learn how to talk to a database then I would definitely agree that a simple SQLite DB is the way to go.

Local SQLite (running on the iphone) would not give access for other users to common/shared data - which is why a central server route has potentially more use.

But, to be honest, the actual RDBMS that is used makes no difference really. If the Database is going to run on a server then it comes down to having some language on the server that can communicate the data results via the network. This could be one of many possibilities - some of which are:

iPhone Server
====== === ======
Livecode <-> Apache/MySQL/PHP
Livecode <-> Apache/MySQL/Livecode Server
Livecode <-> Apache/SQLite/PHP
Livecode <-> Apache/SQLite/Livecode Server
Livecode <-> Tomcat/Oracle/Java
and so many other options...

Cheers,
Dave

[ECNU4271]

Hi ,Dave!

Thanks for replying!

I now know where the mistakes are. I've changed and tried in LC.
However, the result that came out is still the whole line of the data in database(for example, Admin 123456 1 0 etc.)

What if I only need one specific data from one specific column from one specific table from the database?

The same question will occur that to send the content of one field into one specific place of the database.

Could you please tell me?

That should hold me for a while to change my app.

Michael

[Klaus]

Hi Michael,

this is more a SQL than a Livecode problem

Work through this little tutorial, which also got me started with SQL:
http://www.w3schools.com/sql/default.asp


Best

Klaus

[ECNU4271]

Hi Michael,

this is more a SQL than a Livecode problem

Work through this little tutorial, which also got me started with SQL:
http://www.w3schools.com/sql/default.asp


Best

Klaus

Hi Klaus,

I now know how to set the content of a field into a specific place in the database.
I still have no clue about get a specific data from the database into a field, even after viewing the SQL tutorial...

Which specific section should I look into to solve the problem?

Thanks!
Michael

[Klaus]

Hi Michael,

well, check the "Select" section!

If you supply the name of the database field instead of the asterisk *, you will get what you want!
The asterisk is a placeholder for ALL database fields in a db record!

Example to get ONLY the database field named ID from your table:
...
put"Account" into tTableName
put "SELECT ID FROM" & tTableName & "WHERE Name=Admin" into tSQL
...

Best

Klaus

[ECNU4271]

Hi, Klaus!

Here's the code for insert into database.

Code: Select all

on mouseUp
   //if field "SignUp_Password" = field "SignUp_RePassword" 
   //else answer error "The passwords you entered are not the same."  
global gConnectionID
    if gConnectionID is not a number then
           answer error "Please connect to the database first."
           exit to top
    end if

put "Account" into tTableName
put "Name, Password, No, Ratings" into tFields
put field "SignUp_Name" into tName
put field "SignUp_Password" into tPassword
put field "SignUp_Membership" into tNo
put field "SignUp_Ratings" into tRatings

    put "INSERT INTO "  & tTableName & " (" & tFields & ") VALUES (:1, :2, :3)" into tSQL
    revExecuteSQL gConnectionID, tSQL, "tName", "tPassword", "tNo", "tRatings"

    if the result is a number then
           answer info "Sign Up successfully!"
else
   answer error "There was a problem adding the record to the database:" & cr & the result
end if
//go to card id 1002 of stack "Student account"
end mouseUp

When I run it, the error is "MySQL server has gone away".....

What's wrong?

********NO NEED, I fixed it already. See next page.
Michael