Guide to connect to an online MySQL database

[bule]

...all the things I tried, and the solution was so easy and obvious...

This is a guide which will (try to) explain how to connect to, and manipulate, an online MySQL database from your APMS application.

Here's what you need to do it yourself:

APMS 6 (or 5 Pro)
lua50.dll (get it here (http://luabinaries.luaforge.net/)) (not lua 5.1, but 5.0)
lua mysql binaries (get them here (http://luaforge.net/frs/?group_id=12)) (download only binares you need; tested with mysql5)


Place the following files from the downloaded stuff in your project's AutoPlay\Scripts folder:

luasql\mysql.dll (This is a subfolder luasql)
compat-5.1.lua
libmySQL.dll
lua50.dll


Put the following code in your Global functions:
-- Load compat for Lua 5.1 package proposal compatibility
require ("compat-5.1.lua");
package.cpath = _SourceFolder.."\\AutoPlay\\Scripts\\?.dll;"
-- Set the working folder before loading the library
-- so that mySQL can find all appropriate DLLs
local strOldWorkingFolder = Folder.GetCurrent();
Folder.SetCurrent(_SourceFolder.."\\AutoPlay\\Scripts");
require"luasql.mysql"
Folder.SetCurrent(strOldWorkingFolder);

Use this code to initiate a connection with the server and the database:
(add your server details where appropiate)
env = assert (luasql.mysql())
con = assert (env:connect("database_name","username","password","server_address","port"));


Use this code to fetch data from, in this example, table test to a ListBox1 (our table has two columns: id and ime):
ListBox.DeleteItem("ListBox1", -1);
cur = assert (con:execute"SELECT id, ime FROM test")
row = cur:fetch ({}, "a")
while row do
ListBox.AddItem("ListBox1", row.ime.."", row.id.."");
row = cur:fetch (row, "a")
end
cur:close();
This "a" in the code above means to return alphanumerically indexed table.

The same way I used SELECT in the upper example, you can use INSERT, UPDATE and all the other SQL commands supported by MySQL database. Additional info on usage and syntax can be found here: http://luaforge.net/projects/luasql

I would like to send a big thanks for helping me with this to:
- azmanar (pointing me to luaSQL)
- Brett (his guide about LuaSockets, and other things he coded for us)
- LuaSQL developers (obvious for what)
- rest of Lua and APMS commuity (for being so cool)

[bule]

Please note that your MySQL server must be set to allow connections to the database from the computers with the range of IP addresses your application will be running on, in order for your application to sucessfully connect.

[azmanar]

Bule,

CONGRATS !!!!!!!!!!!!!!!

[bule]

This is really some hot stuff... For example, we can now set up a central server with MySQL database and have multiple instances of our APMS application on different computers and all of them can connect to and work with the same database!

By the way, default port for connection to a MySQL server is 3306, but you probably already know that...

[Corey]

Superb! :yes

[Brett]

Good job, Bule. It is a rewarding feeling to get all of those pieces to work together, isn't it? I love that kind of stuff. It warms my heart to see AMS60 users go "outside the box" to create these kinds of solutions. The best part of this is that you did all of that with no system dependencies on the clients and I would guess around 2-3 MB of files. Try doing that with .NET! I am glad to see the potential power of Lua and the great Lua community tapped like this.

[bule]

It is a rewarding feeling to get all of those pieces to work together, isn't it?
I sure is :) :yes

However, there is some work beyond my capabilities (for now) that could be done with this to make things more smooth; for example, if a target server is offline or unrechable, the luasql returns an error and script processing (lines we wrote in APMS after the env:connect command) stops immediately. This represents a problem, because we can not take countermeasures when something like this occurs, to inform user the way we want, or change variables or object states, or whatever we see fit. I guess that for the next APMS incarnation, IndigoRose development could use the sources of LuaSQL libraries and incorporeate them into APMS, so that they return errors like all the other commands in APMS do (and we could then get them with Application.GetLastError(); command). This just my opinion, not a request or anything. I would just like to hear what does heads in IR think about this proposal ;)

[Intrigued]

bule, nice!

;)

[craigedmonds]

Anyone have a SQL Server 2000 version of this?

[bule]

Anyone have a SQL Server 2000 version of this?
I doubt... for now, I think that only MySQL and PostgreSQL drivers of LuaSQL support remote connection.


By the way, I just realised that you don't have to use assert command in the upper example! You can, for example, issue a connection with this:
env = luasql.mysql();
con = env:connect("database_name","username","password","server_address","port");
This way, if something goes bad, you can check the con variable whether it is nil, and if, do something in that case! This is a great solution for the issue I commented earlier here (http://www.indigorose.com/forums/showpost.php?p=74608&postcount=7).

[bule]

This way, if something goes bad, you can check the con variable whether it is nil, and if, do something in that case! This is a great solution for the issue I commented earlier here (http://www.indigorose.com/forums/showpost.php?p=74608&postcount=7).

Not only that, but check this out:

con,err = env:connect("test_database","test","test","my_server","3306");

That's right! Two returning variables! If there is no error, err variable will be nil, and if there is, err will contain an error message string!!! This rules, kicks and ownes!

P.S: Actually, con is not a variable but an object...but they are all references afterall...

[bule]

However, there is some work beyond my capabilities (for now) that could be done with this to make things more smooth; for example, if a target server is offline or unrechable, the luasql returns an error and script processing (lines we wrote in APMS after the env:connect command) stops immediately.

...as I said the key words in the quote above.... (for now)... well actually, for then, cause I found a solution just a few seconds ago!

Is there a way I could edit the upper guide to remove assert commands and add an additional returning err variable? Moderators?

[azmanar]

...as I said the key words in the quote above.... (for now)... well actually, for then, cause I found a solution just a few seconds ago!

Is there a way I could edit the upper guide to remove assert commands and add an additional returning err variable? Moderators?

Since it is Lua environment, then why not apply Lua here as well;

con,err = env:connect("test_database","test","test","my_server","3306");

if err ~= nil then
Label.SetText ("Label_Error", err );
end

[bule]

I think you misunderstood me azmanar; I know how to change the code;
I want to change the guide I wrote, but I can not edit my post!

;)

[azmanar]

I think you misunderstood me azmanar; I know how to change the code;
I want to change the guide I wrote, but I can not edit my post!

;)

Ahhh.....sorry. Misunderstood.

Put another guide in here...within this thread.

[bule]

It ain't cool that way! It'll confuse most users... I'd really like to edit my first post!

[Intrigued]

It ain't cool that way! It'll confuse most users... I'd really like to edit my first post!

I agree with this, I like that part about the PureBasic forums. You can go back at a later time (date!) and adjust your first post, thus keeping down the "waste" and confusion.

:yes

[Corey]

This system works best from an admin perspective. I don't have to time to moderate threads for spam, inappropriate content, etc. which may be everchanging in that way. If you want to rewrite your guide simply do so in a new thread and add a request for me to delete this one. I'm always happy to do stuff like that. There are no plans to change the current post editing system... :yes

[Dermot]

That is awesome, well done Bule. :yes That opens up some interesting things you can do with AMS.

craigedmonds, You should be able to use Luacom and ADO to access an SQL Server database. I did it with an Access database so it is not much different to connect to SQL Server. Mind you I have not tried it, but it should work.

[bule]

Here's a better code to initiate a connection:
(again, add your server details where appropiate)

env,err = luasql.mysql())
if err then
Dialog.Message("Error", err);
else
con,err = env:connect("database_name","username","password","server_address","port"));
if err then
Dialog.Message("Error", err);
end
end

[Worm]

Way to go Bule!! That rocks!

[AGRO]

I would love to see a sample on this. This is just great!! :yes :yes :yes :yes :yes

[Intrigued]

Just bule'tiful, this shorted code be!

;)

[Dermot]

Hi Bule

I wanted to try out your code but I get the attached error when I preview. I have all the files you listed. Is there meant to be a file named "luasql.mysql" ?

Are there different versions of compat-5.1.lua ?

Thanks

[bule]

This is the exact file structure in Scripts folder:

luasql \ mysql.dll (This is a subfolder luasql)
compat-5.1.lua
libmySQL.dll
lua50.dll


There is no file named luasql.mysql.

[Tek]

Excellent work bule. This can really come in handy.

I got your code working, but I want to use variables instead. Here is my example:


cur = assert (con:execute"SELECT username, grp FROM users")


I want to be able to specify the 'username', 'grp' and 'users' through input boxes. I have tried to use:


sMySQLQuery = "con:execute\"SELECT "..sUsername..", "..sColumn.." FROM "..sTable.."\"";
cur = assert (sMySQLQuery);


but it doesn't work when it gets to the line:


row = cur:fetch ({}, "a")


Any ideas? Thanks for your effort!

[bule]

con:execute can not ba a string. It is a command.

[Tek]

Hi bule. Thanks for the reply.

I understand that con:execute is a command, but I am putting together an example where users can specify the information to connect to and query a MySQL database. I want to try and use input boxes to specify the commands sent to the MySQL server.

See the screenshot below.

[bule]

Try this:

sMySQLQuery = "SELECT "..sUsername..", "..sColumn.." FROM "..sTable;
cur = assert (con:execute sMySQLQuery);

[Tek]

Hmm no go. I get an error function arguments expected near 'sMySQLQuery'

[bobbie]

Like to try this but no MySql database?
Get a free one
http://www.freesql.org/
One more
http://www.freemysql.a1whs.com/

[bule]

Hmm no go. I get an error function arguments expected near 'sMySQLQuery'

Maybe this will work...

sMySQLQuery = "SELECT "..sUsername..", "..sColumn.." FROM "..sTable;
cur = con:execute""..sMySQLQuery;

[Tek]

Hmm still no. The second line now gives the error attempt to concatenate a nil value :(

[bule]

How about this:

sMySQLQuery = "SELECT "..sUsername..", "..sColumn.." FROM "..sTable;
cur = con:execute(sMySQLQuery);

[Tek]

It doesn't fail on this line anymore:


cur = con:execute(sMySQLQuery);


But now it fails on the next line:


row = cur:fetch ({}, "a")


With attempt to index global 'cur' (a nil value)

Which is sort of the same problem I had in my initial post. :(

[bule]

Not quite the same since the cur:execute is now processed.

Try this to see what's going on:

cur,err = con:execute(sMySQLQuery);
Debug.ShowWindow();
Debug.Print(err)

[Tek]

Ok here's the code I got so far:


sDatabase = Input.GetText("InputDatabase");
nPort = Input.GetText("InputPort");
sHostname = Input.GetText("InputHostname");
sUsername = Input.GetText("InputUsername");
sPassword = Input.GetText("InputPassword");
sTable = Input.GetText("InputTable");
sColumn1 = Input.GetText("InputColumn1");
sColumn2 = Input.GetText("InputColumn2");

env,err = luasql.mysql()
if err then
Dialog.Message("Error", err);
else
con,err = env:connect(sDatabase,sUsername,sPassword,sHostnam e,nPort);
if err then
Dialog.Message("Error", err);
end
end

ListBox.DeleteItem("ListBox1", -1);
sMySQLQuery = "SELECT "..sColumn1..", "..sColumn2.." FROM "..sTable;
cur,err = con:execute(sMySQLQuery);
row = cur:fetch ({}, "a")
while row do
ListBox.AddItem("ListBox1", row.sColumn1, row.sColumn2);
row = cur:fetch (row, "a")
end
cur:close();


The part where it fails now is:


ListBox.AddItem("ListBox1", row.sColumn1, row.sColumn2);


Now I know that's wrong. The error I get is argument 2 must be of type string so I know I can't use 'row.sColumn1' or 'row.sColumn2'. I need to make that into a string first. I don't know much about using the LuaSQL library so I would have to look that up. Hmmm....

Getting closer though! :)

[bule]

Sorry for the delay... Try this:

ListBox.AddItem("ListBox1", row.sColumn1.."", row.sColumn2.."");


Or this to see what's going on:

Debug.Show()
Debug.Print(type(row.sColumn1))
Debug.Print(type(row.sColumn2))

[Tek]

Hi Bule,

I already had debug on. Here is the message I get for the ListBox.AddItem line:

Attempt to concatenate field 'sColumn1' (a nil value)

[bule]

I have no idea.

[Tek]

Darn. Well when I have time I guess I'll see what I can come up with.

I appreciate your efforts. I'm sure using variables with this will certainly come in handy for a lot of AMS users, if we can just figure it out! :)

[lnd]

the ar a big list of file to download i dont no the name of the file?
can sambady fut a link to the files?

[azmanar]

Darn. Well when I have time I guess I'll see what I can come up with.

I appreciate your efforts. I'm sure using variables with this will certainly come in handy for a lot of AMS users, if we can just figure it out! :)

Tek,

I'm not so sure if this would work in your case. But the concept I'm showing might be useful.

if your table name is MyUsers
-------------
sColumn1 = "userid";
sColumn2 = "username";

ListBox.DeleteItem("ListBox1", -1);

sQuery = "SELECT "..sColumn1..", "..sColumn2.." FROM "..sTable;
tMyUsers = SQLite.QueryToTable(db,sQuery);

if tMyUsers then
for i=1,tMyUsers.Rows do
ListBox.AddItem("ListBox1", tMyUsers.Data[i][\""..sColumn1.."\"], tMyUsers.Data[i][\""..sColumn2.."\"]);
end
end
----------------

Whenever I need to fetch a row of data from a loop until it ends, somehow the SQL column name must be enclosed with double apostrophies like "userid" or "username" or etc. You're using variables, still they need to be enclosed.

In your case, I assume you populate a table called 'a' by using this code :
row = cur:fetch ({}, "a").

So maybe you could do this:

for i=1, a.Rows do
ListBox.AddItem("ListBox1", a.Data[i][\""..sColumn1.."\"], a.Data[i][\""..sColumn2.."\"]);
end

[Tek]

Hi azmanar,

Correct me if I'm wrong, but from Bule's original post it looks like the 'a' corresponds to the mode in which the data will be retrieved from the table.

From the LuaSQL home page at http://www.keplerproject.org/luasql/manual.html#cursor_object :

cur:fetch([table[,modestring]])
Retrieves the next row of results.
If fetch is called without parameters, the results will be returned to the caller directly. If fetch is called with a table, the results will be copied into the table and this table will be returned (for convenience). In this case, an optional mode parameter can be used. It is just a string indicating how the result table should be made. The mode string can contain:

"n"
the resulting table will have numerical indices (default)
"a"
the resulting table will have alphanumerical indices


The numerical indices are the positions of the fields in the select statement; the alphanumerical indices are the names of the fields.
The optional table parameter is a table that should be used to store the next row. This allows the use of a unique table for many fetches which can improve the overall performance.
There is no guarantee about the types of the results, they can be converted to adequate Lua types by the driver or not. In the current implementation, the PostgreSQL and MySQL drivers returns all values as strings while the ODBC and Oracle drivers converts them to Lua types.
Returns: data, as above, or nil if there are no more rows. Note that this method could return nil as a valid result.

[azmanar]

Tek,

Thanks for the good info.

In order to stop EMPTY FIELD ERROR PROMPTS, I would trap empty data fields with ,

if sColumn1 =="" or sColumn1==" " then
sColumn = " - "
end

[Tek]

azmanar,

The problem doesn't seem to be related to empty fields in the database. I can verify that the fields contain data. The problem lies in using a variable to get the row information. This is where I'm stuck now.

[bule]

I think it's the limitation of the language itself.

You see, in my example, row.id and row.ime, bolded pards are the actual names of the columns in the query result. You're trying to put a string after the row. and that is where your error occurs. Since we are dealing with a true objects here, I think that you can not use that approach here.

[azmanar]

Hi,

So if the row is the table name, I suggest this way:


if row then
for i=1, row.Rows do
ListBox.AddItem("ListBox1", row.Data[i][\""..sColumn1.."\"], row.Data[i][\""..sColumn2.."\"]);
end
end

Try it.

[bule]

I doubt that Azmanar. That is SQLite's QueryToTable syntax, and .Data indice is created by the command itself. Ordinary tables do not have .Data indice by default.

However, Tek, you might try this, I think that it should work:

sDatabase = Input.GetText("InputDatabase");
nPort = Input.GetText("InputPort");
sHostname = Input.GetText("InputHostname");
sUsername = Input.GetText("InputUsername");
sPassword = Input.GetText("InputPassword");
sTable = Input.GetText("InputTable");
sColumn1 = Input.GetText("InputColumn1");
sColumn2 = Input.GetText("InputColumn2");

env,err = luasql.mysql()
if err then
Dialog.Message("Error", err);
else
con,err = env:connect(sDatabase,sUsername,sPassword,sHostnam e,nPort);
if err then
Dialog.Message("Error", err);
end
end

ListBox.DeleteItem("ListBox1", -1);
sMySQLQuery = "SELECT "..sColumn1..", "..sColumn2.." FROM "..sTable;
cur,err = con:execute(sMySQLQuery);
row = cur:fetch ({}, "n")
while row do
ListBox.AddItem("ListBox1", tostring(row[1]), tostring(row[2]));
row = cur:fetch (row, "n")
end
cur:close();

[lnd]

i dont find the files

APMS 6 (or 5 Pro)
lua50.dll (get it here) (not lua 5.1, but 5.0)
lua mysql binaries (get them here) (download only binares you need; tested with mysql5)

Place the following files from the downloaded stuff in your project's AutoPlay\Scripts folder:
luasql\mysql.dll (This is a subfolder luasql)
compat-5.1.lua
libmySQL.dll
lua50.dll

i need a link to the files. i dont no what link to download, my ftp server is a linux with mysql (4.0.25-standard).

[Tek]

Ahh yes! Bule got it! It works! :)

But, how does it work? What is tostring?


ListBox.AddItem("ListBox1", tostring(row[1]), tostring(row[2]));

[Tek]

Sorry to reply to my own message. This is what I found. Now I realize it means to-string. :)

http://www.lua.org/pil/13.3.html

[lnd]

i dont find the lua50.dll and compat-5.1.lua in the list

[Tek]

Lua Binaries - http://luaforge.net/frs/download.php/788/lua5_0r2_Win32_bin.tar.gz

LuaSQL - http://luaforge.net/frs/download.php/901/luasql-2.0.1-mysql40-win32.zip

Compat-5.1 - http://luaforge.net/frs/download.php/1411/compat-5.1r5.zip

These are the ones I used.

[lnd]

(EDIT) i find all thenk you.

[lnd]

can i connect whit this code to microsoft sql server 2000?

[bule]

No, you can not.

[evosonic]

This is excellent...

[lnd]

if it's work someone can upload example and shar it?

[bule]

A working example can not be possible since it would involve releasing private data into public (username, password).

You can simply follow the guide yourself;
Here are all the files you need:

[Tek]

If you take the files Bule just posted and place them in your 'AutoPlay\Scripts' folder, you can use the attached project as an example of using variables to query a MySQL database.

[Dez]

bule,
Thankx for takin time to post that code, nice.

Tek,
That example is tight!...thank you.

Dez

[lnd]

whit WinMySQLadmin 1.4 the database is start but i gut a error "Error connecting to database/ MYSQL: access denied for user: 'dany@127.0.0.1' (Using password: YES).
my user in the mysql is - dany
my password is - 1947ufo1

i need help.

---the error is in line 2---

env = assert (luasql.mysql())
con = assert (env:connect("test","dany","1947ufo1","127.0.0.1","3306"));

ListBox.DeleteItem("ListBox1", -1);
cur = assert (con:execute"SELECT id, ime FROM test")
row = cur:fetch ({}, "a")
while row do
ListBox.AddItem("ListBox1", row.ime.."", row.id.."");
row = cur:fetch (row, "a")
end
cur:close();

[lnd]

http://beklilut.com/data/test.jpg

i put the user address port passwork and its not connect way?

[lnd]

i connect to the database whit the exemple but i guting a error on guting a data from the server.
the code (exemple is working?)
the error is in row.ime

[bule]

Please provide more details on your database and table's setup.

[lnd]

database name is "mysql" and table is "help_topic"

[bule]

the error is in row.ime

Do you have a column named ime in your table?

[lnd]

no i dont have

[bule]

Hence the error.

[lnd]

--the program
http://www.beklilut.com/data/program-pic.jpg
-- the mysql server database
http://www.beklilut.com/data/mysql-pic.jpg

[bule]

OK, let us use some common sense here:

cur = assert (con:execute"SELECT id, ime FROM test")

In the code above it is obvious that you seek two columns from the table test, column id and column ime.
Also, it's obvious that you need to have those columns in your table.

Those same columns are referenced, for example, like this when needed:

ListBox.AddItem("ListBox1", row.ime.."", row.id.."");

(Assuming you call row = cur:fetch (row, "a") for each row of data.)

P.S: The .."" addition is to make sure that Listbox recieves string values.

[lnd]

--fEnableDebug(); -- Uncomment this line to debug the query to the database

sDatabase = Input.GetText("InputDatabase"); -- Get the database name from the input field 'InputDatabase'
nPort = Input.GetText("InputPort"); -- Get the port number from the input field 'InputPort'
sHostname = Input.GetText("InputHostname"); -- Get the database hostname from the input field 'InputHostname'
sUsername = Input.GetText("InputUsername"); -- Get the database username from the input field 'InputUsername'
sPassword = Input.GetText("InputPassword"); -- Get the database password from the input field 'InputPassword'
sTable = Input.GetText("InputTable"); -- Get the table name to query from the input field 'InputTable'
sField1 = Input.GetText("InputField1"); -- Get the first field name to query in the table defined in 'InputTable'
--sField2 = Input.GetText("InputField2"); -- Get the second field name to query in the table defined in 'InputTable'
nSelected = ComboBox.GetSelected("ComboBox1"); -- Get the selected entry in the combobox
sField2 = ComboBox.GetItemData("ComboBox1", nSelected); -- Get the item data from the selected entry in the combobox

-- Connect to the database using the information gathered from the input fields
env,err = luasql.mysql()
if err then
-- If there is an error accessing LuaSQL, display a dialog box with the error
Dialog.Message("Error", err);
else
-- Connect to the database using the values input
con,err = env:connect(sDatabase,sUsername,sPassword,sHostnam e,nPort);
if err then
-- If there is an error connecting to the database, display a dialog box with the error
Dialog.Message("Error", err);
end
end

-- Delete all items in the listbox 'Field1Data'
ListBox.DeleteItem("Field1Data", -1);
-- Perform the MySQL query based on the values 'sField1', 'sField2' and the table 'sTable'
--sMySQLQuery = "SELECT "..sField1..", ld2."..sFie." FROM "..sTable.." ORDER BY "..sField2.." DESC";
sMySQLQuery = "SELECT * FROM id";
cur,err = con:execute(sMySQLQuery);
row = cur:fetch ({}, "n")
while row do
-- For each entry in 'sField1', get the information stored in the variable 'sField2'
ListBox.AddItem("Field1Data", tostring(row[1]), tostring(row[2]));
row = cur:fetch (row, "n")
end
-- Close the database connection
cur:close();

---------------------------------------------------------
error in
sMySQLQuery = "SELECT "..sField1..", ld2."..sFie." FROM "..sTable.." ORDER BY "..sField2.." DESC";
--sMySQLQuery = "SELECT * FROM id";
--cur,err = con:execute(sMySQLQuery);
--and error in this line
row = cur:fetch ({}, "n")
----------------------------------------------------------

[bule]

I noticed that LuaSQL was updated to version 2.
In my example, I used version 1.

As I can see, a great new feature with the LuaSQL 2.0.2
is the numrows method added to MySQL driver. Nice!

More info: http://www.keplerproject.org/luasql/

[hiddenhole]

Error Line 33: attempt to index global `cur' (a nil value)

I assume this means it thinks the table iam connecting to has no data ??

Iam using the code attachment from the top of this page, the project file

(<--lua noob)

[bule]

No, I think you've got a typo somewhere.

Scroll down the first page to see how to get an error codes, if any occure.

[SupperBin]

Dose any know how to post data into the MySql database,
thanks SupperBin

[bule]

The same way you do all the queries.

[Jonas DK]

This is absolutely fantastic.

big thumbs up...

Here I am getting headaces to solve this problem and the answer is right here in front of me...

. o O (Allways do effective search of forum before headace :-))


cheers,
Jonas

[pironan]

Im getting to the point where I want to say "To h*ll with Lua!" :eek:

This thread is byfar the best yet in regards to connecting to MySQL with AMS, but I tried to download the objects as per Mules post, but the version Mule says to get is no longer there.

I'm sure I cannot be the only person here who has issues with this? am I?

I am no programmer, all i can find is things that I have t compile to get to work, is there not an easier way around this..

Perhaps IR in any updated release could actually ADD MySQL Functionality in the same way they did with SQLite as that works great.

[bule]

We can help you if you provide your code.
Of course, do not post sensitive information such as database password.

[pironan]

First of all my apologies for getting your name wrong, I c now its bule and not Mule..

I would love to post code examples, but until I can find the correct software there is no code to share :)

However is someone is will to post the software needed (via attachment) rather than links it would be great.

Peter

[Dermot]

Here is a basic working example that includes all the files you need. Just enter your database info in the On click event code of the Connect button.

[Intrigued]

Thanks Dermot for the example. I mirrored it over at www.amsuser.com:

http://www.amsuser.com/ams/examples/MySQL_Example-AMS6-Dermot.apz

[Jonas DK]

Hi,

been playing around with this... but got stuck.

I have a project that works with SQLite and I wont to migrate it to MySQL

I got the connection worked out but when It comes to the query part Im lost.

The point is that I want to query the database and have the data stored in a table with the same structure as the SQLite queryToTable

two reasons for this:

1. I only have to change a few functions in my APP to migrate it.

2. This Connecting to MySQL is very advanced and to most users it is much easier to learn to control the SQLite plugin in AMS, So creating a function that stores the query to the MySQL database in the same way as the SQLite QueryToTable command does would make this much more user friendly.

I am sure that IR will consider making a MySQL plugin like the SQLite plugin in the future, but for now it could be nice to have this work around.

Any surgestions on how to solve this.

So fare I only have the beginning of the function:

function QueryDB(sQuery)
cur,err = con:execute(sQuery);
if not err then
DBTable = cur:fetch ({}, "a")

end


feel free to fill in the blanks

Cheers,
Jonas

[lnd]

how can i make a new sql database and new tables from ams6???

[RizlaUK]

how can i make a new sql database and new tables from ams6???

look here:
http://www.indigorose.com/forums/showthread.php?t=19965

[lnd]

i dont find how to open a new database or how to create a new tables.

[Jonas DK]

i dont find how to open a new database or how to create a new tables.

Depending on your server you wont be able to create a new database but you can easaly create new tables just use the proper SQL command to CREATE TABLE

a hint to learn the SQL language is to do a quick search on google for SQL tutorials or just learn SQL

using the fungtion MySQLQuery ("Query String"); in the example will let you issue any valid MySQL query to the database.

cheers,
Jonas

[Teqskater]

I've managed to Insert data into a database. and get info from a database with something i found on this forums:

DBconnect("TOD", "Root", "")

ListBox.DeleteItem("ListBox1", -1);
cur = assert (con:execute"SELECT FirstName, LastName FROM Username")
row = cur:fetch ({}, "a")
while row do
result = Dialog.Message("Notice", "Your message here.", MB_OK, MB_ICONINFORMATION, MB_DEFBUTTON1);

ListBox.AddItem("ListBox1", row.FirstName.." "..row.LastName.."", "");
row = cur:fetch (row, "a")
end
cur:close();

But my question is. how do you get data from for example record 1 only? and put it then into a input box? can anybode help me with this?

[Teqskater]

got it now. but one question. i know that you can upload for example an image to a BLOB field. how to do that in AMS?. and how to return the image. or isnt it possible? once i know this i post a very usefull example that uses most important mysql queries

[cs200x]

I try to do what it said and keep getting this message on run time.

Modile `luasql.mysql` not found

I am using ams 7.0 and I did place all the files like it said to do, what is it that i am doing wrong?