creating dynamic tables inside tables...

[Jonas DK]

Hi,

Sorry to start a new thread like this, based on the fact that I allready dosted this message in another thread, but the other thread is on a nother topic about DBs (you can view it here http://www.indigorose.com/forums/showpost.php?p=100550&postcount=7)

The thing is that all I really need is some help with a function to create tables, some knowlegde of the way SQLite generates tables on QueryToTable could be usefull but not a importent the important bit is to create loops inside loops.

I'm not that good a the loop thing, dont know if its because I dont think logicaly about it but my last attempt on this caused my SQL server to go baserk because I had by accident created a loop that not only looped the loop inside it, but the inside loop looped a query to the database and you couldent stop it, because when you stoped one loop the other loop wouldt just loop it and start all over.

By the time I figured this out, my MySQL server was allready so messed up with all the looping queries that I had to take it down and restart it.

So what can we learn from this, be carfull what you wish for :eek:

The fact remaining, if someone is good with tables inside tables and creating them with loops inside loops then take a look ad the other thread and help solve this problem.

I'm actually trying to create a function that will use the connection to a MySQL database and return the output in the same manner as the SQLite plugin so that every one can use connections to MySQL servers with out huge programming skill all they have to do is use the help files to lern a little about the SQLite plugin and wupti, you can use a MySQL connection aswell.

This wouldt be a wonderful workaround untill IR integrates MySQL database connections in the same manner as the SQLite plugin.

cheers,
Jonas

[bule]

Something like this might work... I haven't tested it at all, however...

function MySQL.QueryToTable(con, query)
--define variables:
local Rows=0;
local Columns=0;
local ColumnNames={};
local Data={};
local cur;
local err;
--execute query:
cur,err=con:execute(query);
if err and err>0 then
--sql query error handling here--
else
--fetch the rows:
row = cur:fetch ({}, "a");
while row do
--increase Rows variable
Rows=Rows+1;
--generate variable Columns and table ColumnNames
if Rows==1 then
for index in row do
Columns=Columns+1;
ColumnNames[Columns]=index;
end
end
--generate table
Data[Rows]={};
for index,item in row do
Data[Rows][index]=item;
end
row = cur:fetch (row, "a")
end
end
cur:close();
return {Rows,Columns,ColumnNames,Data};
end

Contributions are more than welcome!

[bule]

It would be a good idea to change:

cur:close();

into this

if cur then cur:close(); end

How come that there are no comments yet?

[Jonas DK]

Hi bule

It looks good, been out working so havent had time to test.

I'll test it this morning and get back.

thourght you were doing your exams?

[Jonas DK]

First, Thanks for taking the time Bule, even with your exams and every thing.
I hope they goes fine.

secound, it looks right

did some minor changes

---- **** MySQL Query To Table Function **** ----
function MySQLQueryToTable(query) --A function name cant contain a . If it does I keep getting an error on trying to index MySQL. I also removed the con from the arguments as Im not sure what it does and couldent figure it out.
--define variables:
local Rows=0;
local Columns=0;
local ColumnNames={};
local Data={};
local cur;
local err;
--execute query:
cur,err=con:execute(query);
if err then
Dialog.Message("Error", err);
else
--fetch the rows:
row = cur:fetch ({}, "a");
while row do
--increase Rows variable
Rows=Rows+1;
--generate variable Columns and table ColumnNames
if Rows==1 then
for index in row do
Columns=Columns+1;
ColumnNames[Columns]=index;
end
end
--generate table
Data[Rows]={};
for index,item in row do
Data[Rows][index]=item;
end
row = cur:fetch (row, "a")
end
end
cur:close();
return {Rows,Columns,ColumnNames,Data}; -- Not sure about this one? what is returned? all thise tables or just one table?
end


Lets say I use a table in the database that contains postalcodes and city names.

The Table is called tPostnr and the colums are called ID, Postnr & City

if to follow the SQLite AMS queryToTable is would return a table that looks like this:


--Call the function with the name of the returned table
tbReturn = MySQLQueryToTable("SELECT * FROM tPostnr");

-- Then If I want to display the first set of Data in a dialog I would do
Dialog.Message("Returned Data", tbReturn.Data[1]["ID"].." "..tbReturn.Data[1]["Postnr"].." "..tbReturn.Data[1]["City"], MB_OK, MB_ICONINFORMATION);

-- or if I wanted the fifth line i'd do
Dialog.Message("Returned Data", tbReturn.Data[5]["ID"].." "..tbReturn.Data[5]["Postnr"].." "..tbReturn.Data[5]["City"], MB_OK, MB_ICONINFORMATION);



So I'm lost in translation here.

--generate table
Data[Rows]={};
for index,item in row do
Data[Rows][index]=item;
end
...

return {Rows,Columns,ColumnNames,Data};


Shouldent it return the table Data so that the Return value should just be
return Data

If I understand the Return code of a function correctly it should then return the table Data to the table I used to call the function with, in this case it should return the table Data to the table tbReturn so that you can access the table via tbReturn.Data

I might have completely misunderstod something along the way

I'll keep experimenting with this, If anyone have any good Ideas then by all means, feel free to post.

Cheers,
Jonas -The humble student...

[bule]

thought you were doing your exams?

Well I am, but I had to get some rest... and what is
better way to rest than to do some coding in Lua?

[bule]

Shouldent it return the table Data so that the Return value should just be
return Data

If I understand the Return code of a function correctly it should then return the table Data to the table I used to call the function with, in this case it should return the table Data to the table tbReturn so that you can access the table via tbReturn.Data

Well, SQLite returns a table with four indices as well: Rows,Columns,ColumnNames,Data
This is just one table with two variables and two tables inside.


You would call this function like this:
return=MySQL.QueryToTable(mycon, "SELECT name, surname FROM users");

If the query returns two rows of data, your return would look like this:
return.Rows=2
return.Columns=2
return.ColumnNames={"name","surname"}
return.Data={{name="Joe",surname="Doe"}, {name="Mike",surname="Boa"}}

[bule]

I was sceptic wite one thing however.

When you have table and you assign another variable with this table;
it will not copy the table but point the new variable to existing data.
What will happen with this table when we exit the function... I am
uncertain of.

[bule]

If I understand the Return code of a function correctly it should then return the table Data to the table I used to call the function with, in this case it should return the table Data to the table tbReturn so that you can access the table via tbReturn.Data

Actually that is a sugar in Lua.
You would ussualy have to access your data like this, even with SQlite:
return["Data"][1]["name"]

But with Lua's sugar, it's enough to do it like this:
return.Data[1].name

Maybe this is what is confusing you.

[bule]

Here is a picture I made for you :)

http://veleri.hr/~dbukvic/stuff/sqlquerycubes.gif

[Jonas DK]

again I can't thank you anof for the time spent on this.

a question, the con argument in the function call, does it do anything? I can't see it used in the function so I just removed it and it seems to be working for that part sicne I dont get any errors on the function, only when I try to display info from the returned table, but that might just be me.

I'll have a look at it and see what I can come up with.

[bule]

a question, the con argument in the function call, does it do anything? I can't see it used in the function so I just removed it and it seems to be working...

Yes, but what if you have several databases open?
I think you are having a tough time dealing with the variable scoping (http://lua-users.org/wiki/LuaScoping).
Perhapse Lua global variables are to be blamed for that.
In my function I used con. I should have used some other variable to prevent confusion.

[bule]

Here is an update:

function MySQL.QueryToTable(luasql_connector, mysql_query)
--define variables:
local Rows=0;
local Columns=0;
local ColumnNames={};
local Data={};
local luasql_cursor;
local query_error;
--execute query:
luasql_cursor,query_error=luasql_connector:execute (mysql_query);
if query_error and query_error>0 then
--sql query error handling here--
elseif luasql_cursor then
--fetch the rows:
local luasql_row = luasql_cursor:fetch ({}, "a");
while luasql_row do
--increase Rows variable
Rows=Rows+1;
--generate variable Columns and table ColumnNames
if Rows==1 then
for index in luasql_row do
Columns=Columns+1;
ColumnNames[Columns]=index;
end
end
--generate table
Data[Rows]={};
for index,item in luasql_row do
Data[Rows][index]=item;
end
luasql_row = luasql_cursor:fetch (luasql_row, "a")
end
end
if luasql_cursor then luasql_cursor:close(); end
--return table with four indices as a result:
return {Rows,Columns,ColumnNames,Data};
end

You would call this function like this:
return = MySQL.QueryToTable(your_connector, your_query);


Notice: Since the variables luasql_connector and mysql_query are defined as a function arguments,
they are automaticly local to the function.... well, at least they should have been.

[Jonas DK]

In the on show of the first page in the project I do this:
tbData = MySQLQueryToTable("SELECT * FROM tPostnr");
Dialog.Message("test", tbData.Data[1].City, MB_OK, MB_ICONINFORMATION);

If I have understod it right the the dialog message should show the content of the first City name from the database table.

But when I run the app I get an error on the dialog saing "attempt to index field 'Data'(a nil value) "

[bule]

This is what I was afraid of. Since the table Data is local to the function, it is not returned properly (it's a table issue). I am not sure what to do here... The function should be debugged anyways... but I do not have free time for that just now.

For a test, try removing local from the variable definition...

[Dermot]

For a test, try removing local from the variable definition...
That's what I would suggest.

[bule]

While that is a dirty workaround, just try it to see if it will work.

[Jonas DK]

nope dident work.

This is what I got so fare.

I have one function to make the database connection

-- **** MySQL Connect to Database **** --
function DBconnect(sDatabase,sUsername,sPassword)
-- Initiate the Lua MySQL extension
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
con,err = env:connect(sDatabase,sUsername,sPassword,"******.dk","3306");
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
end


And then the headache function to query the database and simulate the return of SQLite.QueryToTable:

---- **** MySQL Query To Table Function **** ----
function MySQLQueryToTable(query)
--define variables:
Rows=0;
Columns=0;
ColumnNames={};
Data={};
--execute query:
cur,err=con:execute(query);
if err then
Dialog.Message("Error", err);
else
tNames = cur:getcolnames();
--fetch the rows:
row = cur:fetch ({}, "a");
while row do
--increase Rows variable
Rows=Rows+1;
--generate variable Columns and table ColumnNames
if Rows==1 then
for index in row do
Columns=Columns+1;
ColumnNames[Columns]=index;
end
end
--generate table
Data[Rows]={};
for index,item in row do
Data[Rows][index]=item;
end
row = cur:fetch (row, "a")
end
end
cur:close();
return {Rows,Columns,ColumnNames,Data};
end

I added
tNames = cur:getcolnames();
to the original function to be able to test that something is actually being returned from the database. It creates a numeric table of the columnnames, this call only works if you use the nevest version of the luaSQL.

To get this all to work I call the connection function on the startup tab.

then on the "on show" tab of the first page I have this:

--Call the query function
tbData = MySQLQueryToTable("SELECT * FROM tPostnr");
--Concatenate the table with the columnames and display them in a dialog
result = Table.Concat(tNames, ";", 1, TABLE_ALL);
Dialog.Message("test ColNames", result, MB_OK, MB_ICONINFORMATION);
--Try to display some data from the actual query
Dialog.Message("test", tbData.Data[1].City, MB_OK, MB_ICONINFORMATION);

I then build and run and get this:

The first dialog shows the names of the columns so we know we are getting the data.

the secound dialog gives the error that Data is nil

I've attached screenshots.

I have removed all Local from the query function
as you can see in the function above. all so I added some error handeling and renamed the function from MySQL.QueryToTable to MySQLQueryToTable

[bule]

I think we'll have to wait for IR to tell us how to return a table and whether it is possible at all...

[bule]

I think I got it...Try it like this:

return {Rows=Rows,Columns=Columns,ColumnNames=ColumnNames ,Data=Data};

Also return the local where you define the variables!

I just tested the following code and it works:
function tabla()
local data={1,2,3}
return {data=data}
end

bule=tabla()
Debug.ShowWindow(true);
for i,j in bule.data do
Debug.Print(i.." "..j.."\r\n");
end

[bule]

function MySQL.QueryToTable(luasql_connector, mysql_query)
--define variables:
local Rows=0;
local Columns=0;
local ColumnNames={};
local Data={};
local luasql_cursor;
local query_error;
--execute query:
luasql_cursor,query_error=luasql_connector:execute (mysql_query);
if query_error and query_error>0 then
Dialog.Message("Error", err);
elseif luasql_cursor then
--fetch the rows:
local luasql_row = luasql_cursor:fetch ({}, "a");
while luasql_row do
--increase Rows variable
Rows=Rows+1;
--generate variable Columns and table ColumnNames
if Rows==1 then
for index in luasql_row do
Columns=Columns+1;
ColumnNames[Columns]=index;
end
end
--generate table
Data[Rows]={};
for index,item in luasql_row do
Data[Rows][index]=item;
end
luasql_row = luasql_cursor:fetch (luasql_row, "a")
end
end
if luasql_cursor then luasql_cursor:close(); end
--return table with four indices as a result:
return {Rows=Rows,Columns=Columns,ColumnNames=ColumnNames ,Data=Data};
end

[bule]

You would call this function like this:

return=MySQL.QueryToTable(con, "SELECT * FROM users ORDER BY name;")

[TJ_Tigger]

function MySQL.QueryToTable(luasql_connector, mysql_query)
--define variables:
local tbReturn = {};
tbReturn.Rows=0;
tbReturn.Columns=0;
tbReturn.ColumnNames={};
tbReturn.Data={};
local luasql_cursor;
local query_error;
--execute query:
luasql_cursor,query_error=luasql_connector:execute (mysql_query);
if query_error and query_error>0 then
Dialog.Message("Error", err);
elseif luasql_cursor then
--fetch the rows:
local luasql_row = luasql_cursor:fetch ({}, "a");
while luasql_row do
--increase Rows variable
tbReturn.Rows=tbReturn.Rows+1;
--generate variable Columns and table ColumnNames
if tbReturn.Rows==1 then
for index in luasql_row do
tbReturn.Columns=tbReturnColumns+1;
tbReturn.ColumnNames[tbReturn.Columns]=index;
end
end
--generate table
tbReturn.Data[tbReturn.Rows]={};
for index,item in luasql_row do
tbReturn.Data[tbReturn.Rows][index]=item;
end
luasql_row = luasql_cursor:fetch (luasql_row, "a")
end
end
if luasql_cursor then luasql_cursor:close(); end
--return table with four indices as a result:
return tbReturn;
end

You could try this where you define a table to be returned and then make Rows and Data part of the table from the beginning. Haven't tried the code however.

Tigg

[Jonas DK]

Bule:eek: , you ROCK! :yes

It works, like a charm.

I tired putting the code into my SQLite project and moving my SQLite database onto the MySQL server and all I have to change is the calls to the database all the other code works, as data is now passed in the same manner as with the SQLite.

Hope you dident use to much time on this and dident get to study for your exams. best of luck with them.

Im going to put togehter an exampel for the exampel forum.

[bule]

Cool... i think I'll find this function useful myself.

It's my last exam on the colleague, after this only my dissertation is to-do...
(which will be made using APMS, I hope).