APMS and online MYSQL

[bule]

Hi, is there a way to create an application in APMS
that would connect to an online MYSQL database?

[azmanar]

Hi, is there a way to create an application in APMS
that would connect to an online MYSQL database?

Bule,

You might want to have a look at this :
http://www.keplerproject.org/luasql/index.html

[bule]

The link is very promising; I wonder what do folks at IndigoRose
think about this as a plugin...

EDIT: I think I got it:

-- load driver
require"luasql.postgres"
-- create environment object
env = assert (luasql.postgres())
-- connect to data source
con = assert (env:connect("luasql-test"))
-- reset our table
res = con:execute"DROP TABLE people"
res = assert (con:execute[[
  CREATE TABLE people(
    name  varchar(50),
    email varchar(50)
  )
]])
-- add a few elements
list = {
  { name="Jose das Couves", email="jose@couves.com", },
  { name="Manoel Joaquim", email="manoel.joaquim@cafundo.com", },
  { name="Maria das Dores", email="maria@dores.com", },
}
for i, p in pairs (list) do
  res = assert (con:execute(string.format([[
    INSERT INTO people
    VALUES ('%s', '%s')]], p.name, p.email)
  ))
end
-- retrieve a cursor
cur = assert (con:execute"SELECT name, email from people")
-- print all rows, the rows will be indexed by field names
row = cur:fetch ({}, "a")
while row do
  print(string.format("Name: %s, E-mail: %s", row.name, row.email))
  -- reusing the table of results
  row = cur:fetch (row, "a")
end
-- close everything
cur:close()
con:close()
env:close()

[bule]

Well, I can't figure out how to put this at run in APMS. :(

It's obvious that this will require some modifications to act
more like ussual plugins, for example SQLite or DataGrid.

[Brett]

Keep in mind that the LuaSQL stuff is part of the Keppler project which is really to make Lua run as a Web scripting language like PHP or ASP. I very much doubt that you are going to be able to directly run queries on a remote Web database in that manner.

The best way to handle this is to make scripts on your server in PHP or ASP or whatever. Then use HTTP.Submit to send infor to the script, have the script get the DB info and then return the data back to the AMS APP.

[bule]

Yes Brett, I thought about that, however, this keeps me thinking about this:

env:connect(sourcename[,username[,password[,hostname[,port]]]])
In the MySQL driver, this method has two other optional parameters that indicate the hostname and port to connect.

[azmanar]

Well, I can't figure out how to put this at run in APMS. :(

It's obvious that this will require some modifications to act
more like ussual plugins, for example SQLite or DataGrid.

Bule,

Couldnt look into AMS-MySQL yet, eventhough it is very very very interesting. Got contracted for some presentation jobs - bread and butter. Must finish them before coming week.

I have an initial assumption though. It must load first to gain functionalities. So maybe we can File.Run it from AUTOPLAY Docs as we start the AMS-MySQL app and check whether it is in service.

-- On Show first line could look like this
require (_SourceFolder.."\\AutoPlay\\Docs\\luasql.MySQL");

--definitely you can't trespass a db server
userName = "grasshopper";
userPass = "munchin";
myDB = "FoodList";

--connect to server
connex = env:connect(myDB,userName,userPass,localhost,3306) ;

assert ( connex: blah blah blah create/insert/edit/modify/view Table );

connex:close();
--end of assumption

Please take a look at what Corey shared with us : PHP interpretor. http://www.indigorose.com/temp_web/PHPparse.apz .
If it can go there in the DOCS, I bet LuaSQL driver can, as well.

[azmanar]

Keep in mind that the LuaSQL stuff is part of the Keppler project which is really to make Lua run as a Web scripting language like PHP or ASP. I very much doubt that you are going to be able to directly run queries on a remote Web database in that manner.

The best way to handle this is to make scripts on your server in PHP or ASP or whatever. Then use HTTP.Submit to send infor to the script, have the script get the DB info and then return the data back to the AMS APP.

Hmmm... this means we need 2 sets of codes. 1 in the server and another in AMS. Quite a work to do. So I'll stick to the good old AMS-SQLite and PHP-MySQL.

This means, if I really want to work with mySQL, I'll point my AMS WebObjects URL to that php page in the server. Saves development time.

Thanks Brett.

[bule]

...but where I do get luasql.MySQL?

[azmanar]

...but where I do get luasql.MySQL?

from the kepler link ...download binaries for Windows or package files for other os.

[bule]

Well I tried all the links, but I either get C source code, or already compiled dll files.

And if I open provided workspace in Visual C 6 and try to compile dll
without any modification what so ever, I get a lot of errors...

[Brett]

The compiled DLLs should work. Read this article I wrote about using the LuaSockets module. AMS60 should be able to handle most generic Lua DLL modules.

[bule]

Tried that, however, it seems that LuaSocket has changed it's file structure a lot...

[bule]

I did it!!!

After two days of head crashing, I finally did it! It works!

I created an app that connects to an online MySQL database,
fetches data and fill it in the listbox!!!

And it works so smooth!!!!

Details will follow in a new thread, if you folks don't mind.

[bule]

Here is the GUIDE!