Register forum user name Search FAQ

Gammon Forum

Notice: Any messages purporting to come from this site telling you that your password has expired, or that you need to verify your details, confirm your email, resolve issues, making threats, or asking for money, are spam. We do not email users with any such messages. If you have lost your password you can obtain a new one by using the password reset link.

Due to spam on this forum, all posts now need moderator approval.

 Entire forum ➜ MUSHclient ➜ General ➜ SQLite column order problem
SQLite column order problem

It is now over 60 days since the last post. This thread is closed.     Refresh page

Pages: 1 2  

Posted by Jedhi   (37 posts)  Bio
Date Wed 23 Jan 2013 05:04 PM (UTC)

Amended on Wed 23 Jan 2013 05:38 PM (UTC) by Jedhi

Message
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
Top
Posted by Nick Gammon   Australia  (23,133 posts)  Bio   Forum Administrator
Date Reply #1 on Wed 23 Jan 2013 08:27 PM (UTC)
Message
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

- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top
Posted by Jedhi   (37 posts)  Bio
Date Reply #2 on Thu 24 Jan 2013 01:14 AM (UTC)
Message
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.
Top
Posted by Nick Gammon   Australia  (23,133 posts)  Bio   Forum Administrator
Date Reply #3 on Thu 24 Jan 2013 06:26 AM (UTC)
Message
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
- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top
Posted by Jedhi   (37 posts)  Bio
Date Reply #4 on Thu 24 Jan 2013 07:13 AM (UTC)

Amended on Thu 24 Jan 2013 08:02 AM (UTC) by Jedhi

Message
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...
Top
Posted by Bast   (78 posts)  Bio
Date Reply #5 on Thu 24 Jan 2013 02:58 PM (UTC)

Amended on Fri 25 Jan 2013 03:23 PM (UTC) by Bast

Message
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
Bast

Scripts: http://github.com/endavis
Top
Posted by Jedhi   (37 posts)  Bio
Date Reply #6 on Thu 24 Jan 2013 03:22 PM (UTC)

Amended on Thu 24 Jan 2013 03:42 PM (UTC) by Jedhi

Message
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
Top
Posted by Nick Gammon   Australia  (23,133 posts)  Bio   Forum Administrator
Date Reply #7 on Thu 24 Jan 2013 09:21 PM (UTC)
Message
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.
- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top
Posted by Jedhi   (37 posts)  Bio
Date Reply #8 on Fri 25 Jan 2013 02:25 AM (UTC)

Amended on Fri 25 Jan 2013 02:27 AM (UTC) by Jedhi

Message
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?
Top
Posted by Nick Gammon   Australia  (23,133 posts)  Bio   Forum Administrator
Date Reply #9 on Fri 25 Jan 2013 02:46 AM (UTC)
Message
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.
- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top
Posted by Jedhi   (37 posts)  Bio
Date Reply #10 on Fri 25 Jan 2013 02:58 AM (UTC)
Message
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?
Top
Posted by Nick Gammon   Australia  (23,133 posts)  Bio   Forum Administrator
Date Reply #11 on Fri 25 Jan 2013 05:19 AM (UTC)
Message
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.
- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top
Posted by Fiendish   USA  (2,534 posts)  Bio   Global Moderator
Date Reply #12 on Fri 25 Jan 2013 06:22 AM (UTC)

Amended on Fri 25 Jan 2013 06:23 AM (UTC) by Fiendish

Message
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.
https://github.com/fiendish/aardwolfclientpackage
Top
Posted by Nick Gammon   Australia  (23,133 posts)  Bio   Forum Administrator
Date Reply #13 on Fri 25 Jan 2013 08:36 AM (UTC)
Message
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.

- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top
Posted by Jedhi   (37 posts)  Bio
Date Reply #14 on Fri 25 Jan 2013 04:33 PM (UTC)

Amended on Fri 25 Jan 2013 04:34 PM (UTC) by Jedhi

Message 

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?
Top

The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).

To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.

49,890 views.

This is page 1, subject is 2 pages long: 1 2  [Next page]

It is now over 60 days since the last post. This thread is closed.     Refresh page

Go to topic:           Search the forum

[Go to top] top

Information and images on this site are licensed under the Creative Commons Attribution 3.0 Australia License unless stated otherwise.

Home