help... stuck on database connection

[Jonas DK]

Using Bules thread about the MySQL connection

I've got the connection going but having trouble on the fetching of data.
Might just be me who misunderstod something.

I created this function:

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

If I understand it right I am creating a table called DBTable
The query result is then passed with cur:fetch to this table as alphanumerical tables with the column name as the Table name.

If I do this:

QueryDB("SELECT * FROM tPostnr");
Dialog.Message("test", DBTable.City, MB_OK, MB_ICONINFORMATION);

I get a dialog that sais "Scanning" witch is the first entry in the column City in the database.

Now I thourght that I would get a table called DBTable containing 2 tables as there are 2 columns in the Database table.

So I couldt call the tables DBTable.Zipcode and DBTable.City
But I think cur:fetch only returns the first line of the query, is this correct? and in that case, does any one know how to get it to return all lines found.

if the function I set

DBTable.nrows = cur:numrows();
--This is new for luaSQL it is not implementet in the files in bules post

If I call a dialog to display DBTables it tells me "1475" witch is the correct number of zipcodes in the database.

So I dont know where to start.

The connection works as I can get it to show some of the content of the database and I can get it to show me the number of rows in the query I send but I can't get it to display a list of cities.

Tried using concat to concatenate the table DBTable.City that just gives an error

testresult = Table.Concat(DBTable.City, ";", 1, DBTable.nrows);

gives:
Argument 1 must be of type table

Any ideas?

Have anyone used the MySQL lua socket with success?

Cheers,
Jonas

[RizlaUK]

well i dont know about mysql i havent used it with ams yet, but maybe its because you are refering to a row in the table and not the table its self

testresult = Table.Concat(DBTable.City, ";", 1, DBTable.nrows);


this is just a pure stab in the dark, but try

DBTable = QueryDB("SELECT City FROM DBTable");

testresult = Table.Concat(DBTable, ";", 1, DBTable.nrows);

or something along them lines

[Jonas DK]

well i dont know about mysql i havent used it with ams yet, but maybe its because you are refering to a row in the table and not the table its self

testresult = Table.Concat(DBTable.City, ";", 1, DBTable.nrows);


this is just a pure stab in the dark, but try

DBTable = QueryDB("SELECT City FROM DBTable");

testresult = Table.Concat(DBTable, ";", 1, DBTable.nrows);

or something along them lines

(addition) If I try to concatenate DBTable I just get an empty string
Hi Rizla,

That is just the thing. I'm referring to a table inside a table (I think)
Problem being that I'm not sure since I can get the first line of data but not the rest. From what I understand from the error on table concat I am as you say trying to concatenate a string in the table and not the table.

but since DBTable.City is actually refering to the table City inside the table DBTable I'm not sure what it means.

Again my main problem being that I'm not sure if City actually is a table inside the table DBTable but the problem is that DBTable.nrows counts the correct number of rows in the database I'm lost in translation...

Does all this make any sence? . o O (a bit confusing)

hopefully someone can shead some light on this with the expertise of knowing what exactly the lua.MySQL cur:fecth command returns. it is only loosly descripted at the luaSQL website
or you just have to be a skilled programmer to know what they mean :-)

but thanks for the input

[bule]

You will not get all the rows in one fetch; only one row. Hence the while loop.

row = cur:fetch ({}, "a")
while row do
  ListBox.AddItem("ListBox1", row.ime.."", row.id.."");
  row = cur:fetch (row, "a")
end

[Jonas DK]

thanks Bule,

thourght it might be something like that.

As you are the expert on this field, I have a thourht..(that looks wrong..)

Dont know if you have tried the new version of the luaSQL
maybe you have some pros and cons about that.

Maybe you can help get the results into a table that simulates the SQLite queryToTable

Since I'm not that big on how the LuaSQL table that is returned with the cur:fetch is looking like it's hard to see how to change it.

But am I correct in assuming this:
(assuming I have a Table in the database and I made a query to select * from table. I have to rows in the table called Row1 and Row2)

calling cur:fetch (tbMyTable, "a") will result in an array that
holds the first line of information returned from the querry and I can access this information using tbMyTable.Row1 and tbMyTable.Row2.

The while loop of nothing else then the cur:fetch command is called will just replace the data in tbMyTable.Row1 and tbMyTable.Row2 with the next line of data returned from the query?

Am I getting it this fare?

So if I want to create a table that looks like the SQLite table i need to put the following in the loop:

TableReturn.data[n]() = tbMytable.Row1 AND tbMytable.Row2

I know I'm missing something here because i need to first extract the table Row1 and Row2 and then insert the tables into the table TableReturn.data[n]

(n is the number line in the query, this could be controled using cur:numrows())

Problem is how to construct the table I figured out this much, It is not a good idea to test the loop using trial and error when the query contains 1500 lines
:-)

[bule]

It would be best to create a function that would emulate SQLite.QueryToTable functionality for the LuaSQL. I mught do that, but not any time soon since I just took three weeks holidays at my work in order to pass the last exam on my colleague. The exam where you have to learn everything by heart... i hate that!

[Jonas DK]

....The exam where you have to learn everything by heart... i hate that!

Good luck with that. I hate exams.


Maybe someone is good with tables and thinking logical about it.

I got this far:

The ending table should look something like this:

tbReturn.Data[n]["columnName"]

I created this function:

-- The function with two arguments sQuery being the SQL query and tbReturn being the name of the returned table
function SelectQueryDB(sQuery, tbReturn)

-- Execute the MySQL statement
    cur,err = con:execute(sQuery);
if not err then

-- This is new to LuaSQL version 2.0.2 I returns a table called tbReturn.nRows that contains the number of lines in the Returned query
tbReturn.nRows = cur:numrows(); 

-- this returns the first line in the query to the table tbReturn.RowName
tbReturn = cur:fetch({}, "a");

-- This returns the numeric table called tNames that contains the column names in the cur:fetch statement ( like name, phone, etc.)
tNames = cur:getcolnames();

With this I think we have all the information we need to create the end table
tbReturn.Data[n]["col"]

n being the number row in cur:fetch
col is the column name from tNames

To get the end table we have to use some loops and this is where I'm stuck.

cur:fetch only returns one line of the query at a time so that has to be part of a loop that runs til it gets to tbReturn.nRows

But we all so have to know how many columns are in the query and there names to get them to the end table and that is where tNames comes in.

Using table.count we can get the number of rows in the query to know how many times we need to do a loop to get all the rows into the end table before we run cur:fetch again.

We also have to use tbRetun.nRows to add an entry to the numerical table Data inside the tbReturn table

so this is it:

We create the table tbReturn and add the table Data inside tbReturn. so we get tbReturn.Data

Then we get the first line in the returned query adding it to the table data so we now have tbReturn.Data[1]

Now we have to get all the column names and add them and there content to the table giving os tbReturn.Data[1]["col1"] and tbReturn.Data[1]["col2"] etc.

We then start over by adding one to the Data table so we get tbReturn.Data[2]

Then we run curfetch again and again we add the column names and data to the table (the column names are tha same as before but inside a loop i dont think it matters) so we know have tbReturn.Data[2]["col1"] etc.

We then have to keep repeating thise steps til we get to the end of the query and breaks equal to tbReturn.nRow

Its a lot of data to consider so if some one has a logical aproatch to this then by all means experiment.

If it helps I can setup a test database and a test app so you can try it live.

cheers,
Jonas

[bule]

Link to a solution:

Function:
http://www.indigorose.com/forums/showpost.php?p=100815

How to call it:
http://www.indigorose.com/forums/showpost.php?p=100824

[Jonas DK]

The getting data works as a charm, but when you try to insert data I get in trouble.

It worked perfect the first time I tried but the secound time I got this error:
"LuaSQL: Error executing query. MySQL: Duplicate entry '0' for key '1'"

I have this function:

function MySQLQuery(query)
DBconnect("DB_Name", "DB_User", "DB_Password");
	cur,err=con:execute(query);
	if err then
		Dialog.Message("Error", err);
	else
		Dialog.Message("Cursor Object", cur);
	end
	if cur then cur:close();
	end
	if con then con:close()
	end
end

I think It hase something to do with the ID field in the database that is set to primary key, but it seems a huge load of work to first call the database to get the last entry and add the ID with one. It must be posible to get it to autoincrement the key but how.

cheers,
Jonas

[Jonas DK]

hmm...

It's allways a good Idea to remember to set Auto Increment in MySQL database on the primary key, if you dont specify a value. doh!

Sorry about that.

cheers,
Jonas

[Jonas DK]

New problem.

When executing this function I get the following error:
"attempt to index global 'cur' (a number value)"

function MySQLQuery(query)
DBconnect("jocit_sb", "jocit_sb", "develop");
	cur,err=con:execute(query);
	if err then
		Dialog.Message("Error", err);
	else
--		Dialog.Message("Cursor Object", cur);
	 if cur then cur:close();
	 end
	con:close()
	end
	
end

The error comes on the line trying to close cur
I have tried to use

cur:close() and if cur then cur:close();

dont now that the meaning of the (a number value) part is. cur is a number it is actually the number of changes made in the database...

hmm... I'll try some more light reading at LuaSQL

[bule]

Well, in your case the cursor is a number and not an object, so there is no reason to close it. Try it like this:

if cur then 
	if typeof(cur)=="number" or typeof(cur)=="string" then
		cur=nil;
	else 
		cur:close();
	end
end