SQLite column order problem

Posted by Jedhi on Wed 23 Jan 2013 05:04 PM — 16 posts, 69,973 views.

Jedhi Wed 23 Jan 2013 05:04 PM #0

Hello,

i have a table with following columns (in this order)

keyword, uid, area

but when i pull from database, they are in different order

area, keyword, uid

seems, that they are "pulled out" by alphabetical order.

the query i am using:

SELECT keyword, uid, area FROM mytable

can i fix this problem? the solution at the moment is to sort them

local destinations = db:rows(query)

local sorted = {}
for k, v in ipairs(destinations) do
table.insert(sorted, { v["keyword"], v["uid"], v["area"] })
end

Nick Gammon Australia Forum Administrator Wed 23 Jan 2013 08:27 PM #1

What order do you want them in? Use the ORDER clause. eg.


 SELECT keyword, uid, area FROM mytable ORDER BY area

Any column name can be used for the order. And you can sort them descending, eg.


 SELECT keyword, uid, area FROM mytable ORDER BY uid DESC

Jedhi Thu 24 Jan 2013 01:14 AM #2

sorry, my question was confusing.

i actually want to change the order of the fields in a fetched row.

i want the field order in a row be in the order i am selecting them. not sure if it is possible.

Nick Gammon Australia Forum Administrator Thu 24 Jan 2013 06:26 AM #3

You want nrows, I think. That gives you named rows.


for t in db:nrows(query) do  -- for each row
  print (t.keyword)
  print (t.uid)
  print (t.area)
end -- for each row

Jedhi Thu 24 Jan 2013 07:13 AM #4

thanx, i did just that myself. wondered if there is a SQLite query parameters for that.

though i have a question on nrows. what is optimal way to get only first row? currently i'm breaking for loop (db:close as well)

and is it somehow possible to validate the query before nrows and do some robbust error handling? i php+mysql i can do mysql_query() first...

Bast Thu 24 Jan 2013 02:58 PM #5

add a "limit 1" to the end of your SQL SELECT query to just get the first row that matches.

To add to Nick's example


 SELECT keyword, uid, area FROM mytable ORDER BY area LIMIT 1

To validate, you can use a prepare to check to make sure the SQL is valid.

See
http://www.gammon.com.au/scripts/doc.php?lua=db:prepare
or
http://www.gammon.com.au/scripts/doc.php?function=DatabasePrepare

Bast

Jedhi Thu 24 Jan 2013 03:22 PM #6

thanx Bast, stupid me.

i had problem with this db file locking, so now i'm establishing db connection upon each request and close it right after. what should be the correct use?

and i have a db class like

db = {}

db:connection establishes the connection

db:rows returns all rows

db:row one row

db:exec performs a query and so on ....

i am establishing and closing the connection in each method. is there a alternative or is this absolutely not nessesary

or could i open the connection and then close it at "the bottom of the page" lol

Nick Gammon Australia Forum Administrator Thu 24 Jan 2013 09:21 PM #7

Instead of using the iterator you can use prepare / step / finalize. The general idea is described here:

http://gammon.com.au/db

That is using the inbuilt functions, but lsqlite (the Lua interface) seems to have that as well.

See: http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki

In particular you would want to do a prepare:


db:prepare(sql)

Then a step to read the first row:


stmt:step()

Then a finalize to discard the statement:


stmt:finalize()

Using this method you can stop after you have read one row.

The idea of using LIMIT should work too, I'm not sure which is more efficient.

Quote:

i am establishing and closing the connection in each method. is there a alternative or is this absolutely not nessesary

You certainly shouldn't need to do this, I never do.

Jedhi Fri 25 Jan 2013 02:25 AM #8

how about managing the connection? whould i disconnect at all? will it disconnect it self? maybe my problems were in the breaking the loop ...

i have some questions regarding the OOP in lua as well. are there any constructors and destructors?

Nick Gammon Australia Forum Administrator Fri 25 Jan 2013 02:46 AM #9

You shouldn't break out of the "rows" loop. That leaves the statement open. Use the LIMIT clause and don't break out, or use the other method I described.

Please post other Lua questions in a new thread, preferably under the Lua part of the forum.

Jedhi Fri 25 Jan 2013 02:58 AM #10

still i do not get this disconnecting part.

should i use db:close() at all? if all loops ara finished correctly, will connection to database disconnect bt it self?

Nick Gammon Australia Forum Administrator Fri 25 Jan 2013 05:19 AM #11

You don't normally need to close the database. When the program finishes part of the cleanup process would close the database.

Normally you would open the database before the first access, and close it after the last access. You could, if you liked, put the db:close() into a function called when the world is closed, with a script callback.

Fiendish USA Global Moderator Fri 25 Jan 2013 06:22 AM #12

Quote:
You shouldn't break out of the "rows" loop.

Is this danger well documented? I recall having to correct this mistake for other people in their plugins in the past.

Nick Gammon Australia Forum Administrator Fri 25 Jan 2013 08:36 AM #13

http://www.gammon.com.au/scripts/doc.php?lua=db:nrows

Quote:

Note: You should let the iterator finish (that is, get to the end of the selected rows) otherwise the database remains locked. So, do not "break" out of the for loop.

Jedhi Fri 25 Jan 2013 04:33 PM #14


local row	
local stmt = db:prepare (query)
	
if (stmt ~= nil) then
  if (stmt:step() == 100) then
    row = stmt:get_named_values() 
  end
		
  stmt:finalize()
else
  -- sql error
end

this is what i game up with and it works. am i doing the way it is supposed to?

Nick Gammon Australia Forum Administrator Fri 25 Jan 2013 09:39 PM #15

Looks good to me. You don't need the brackets for the "if" in Lua, but they won't do any harm.

Example:


local row	
local stmt = db:prepare (query)
	
if stmt then
  if stmt:step() == sqlite3.ROW then
    row = stmt:get_named_values() 

    -- process row

  end -- if got row
		
  stmt:finalize()
else
  -- sql error
end -- if got statement