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/sho...50&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

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

[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.
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...