How to get or read a single data in SQLite database?

[HezzelQuartz]

If I have SQLite database table containing Product List and Product Price, how can I get or read a single product price from that database?

For example, look image below

What command should I use?

Can anyone please give me a little clue or guide?

I still don't have any code for this. I only have code to insert value to SQLite database based on my question yesterday.

Thank You

Edited August 30 by HezzelQuartz

[argumentum]

Let's see. You've been here since 2019. The help files gives clear SQL examples for your question. The pic. is not a Win32 GUI. Wassup ?, what are you up to ?

[HezzelQuartz]

18 minutes ago, argumentum said:

Let's see. You've been here since 2019. The help files gives clear SQL examples for your question. The pic. is not a Win32 GUI. Wassup ?, what are you up to ?

Could you please give me the name of the function in help file so that I can read by myself?

[Musashi]

1 hour ago, HezzelQuartz said:

Could you please give me the name of the function in help file so that I can read by myself?

a little clue :

$query = "SELECT Price FROM tablename WHERE Product = 'Book B';"

 

[jchd]

Posting the very same request on the SQLite mailing list won't help you with AutoIt.

Run this :

; #include <Array.au3>
#include <SQLite.au3>

_SQLite_Startup(@ScriptDir & "\" & "sqlite3.dll", False, 1)
If @error Then
    MsgBox(16, "SQLite Error", "SQLite3.dll Can't be Loaded!")
    Exit -1
EndIf

Local $sDatabase = @ScriptDir & "\" & "MyBooks.db"      ; adjust filename
Local $hDatabase = _SQLite_Open($sDatabase)

Local $row
_SQLite_QuerySingleRow($hDatabase, "select price from books where product = 'Book B';", $row)
MsgBox(0, "Price of Book B", $row[1])

_SQLite_Close($hDatabase)
_SQLite_Shutdown()

And make yourself a favor: open and use AutoIt help.

[HezzelQuartz]

On 8/30/2024 at 1:56 PM, jchd said:

Posting the very same request on the SQLite mailing list won't help you with AutoIt.

Run this :

; #include <Array.au3>
#include <SQLite.au3>

_SQLite_Startup(@ScriptDir & "\" & "sqlite3.dll", False, 1)
If @error Then
    MsgBox(16, "SQLite Error", "SQLite3.dll Can't be Loaded!")
    Exit -1
EndIf

Local $sDatabase = @ScriptDir & "\" & "MyBooks.db"      ; adjust filename
Local $hDatabase = _SQLite_Open($sDatabase)

Local $row
_SQLite_QuerySingleRow($hDatabase, "select price from books where product = 'Book B';", $row)
MsgBox(0, "Price of Book B", $row[1])

_SQLite_Close($hDatabase)
_SQLite_Shutdown()

And make yourself a favor: open and use AutoIt help.

If I have SQLite database table containing Category, Product List, and Product Price (image below),
how can I get or read a the latest product from a category from that database?

For example, I want to search my latest entry from book category so that the result become "Book E"?

I mean like this

I think if I execute this command below,
"select product from books where category = 'Book'

the result will be:
Book A
Book B
Book C
Book D
Book E

In this case, I don't know the book name and how many kind of book I have in this category
I just want to get the latest book I Input, so that the result will be "Book E"
But in this case, I don't know the book name is "Book E"

I just want the latest result from this command:
"select product from books where category = 'Book'

What SQLite command or AutoIt Function or both of them should I use to get the latest entry from that category?

Sorry If my explanation is confusing

Edited September 2 by HezzelQuartz

[HezzelQuartz]

  

I think I got the answer:

SELECT Product FROM Product_List WHERE No = (SELECT max(no) FROM Product_List where category = "Book")

then I tried to

DELETE FROM Product_List WHERE No = (SELECT max(no) FROM Product_List Where Category = "Book")

Then "No" Column become
1
2
3
4
6
7
8
9
10

How to keep "No" sorted like this below?
1
2
3
4
5
6
7
8
9

Thank You

[jchd]

The SQL language is based on set theory. If you define a set of RGB colors for instance, that means {'Red', 'Green', 'Blue'} but this set has no inherent ordering. {'Blue', 'Red', 'Green'} is the exact same set. One can sometimes define an ordering on a set but this is NOT a property of the set. So in SQL, "first" and "last" row in a table (= a set) mean nothing.

Hopefully in SQL you can use the ORDER BY clause to explicitely define an ordering for the resultset of a query. For instance, you can use:
SELECT Product FROM Product_List WHERE category = 'Book' order by no desc limit 1;
(note: 'Book' and not "Book")

Your "No" column contains an identifier, which is NOT a row sequence number. Think of an identifier as a EAN or UPC barcode label which you can't read by yourself: it uniquely identifies an item but means nothing per se.

You should take an SQL primer somewhere on the web.

[HezzelQuartz]

46 minutes ago, jchd said:

The SQL language is based on set theory. If you define a set of RGB colors for instance, that means {'Red', 'Green', 'Blue'} but this set has no inherent ordering. {'Blue', 'Red', 'Green'} is the exact same set. One can sometimes define an ordering on a set but this is NOT a property of the set. So in SQL, "first" and "last" row in a table (= a set) mean nothing.

Hopefully in SQL you can use the ORDER BY clause to explicitely define an ordering for the resultset of a query. For instance, you can use:
SELECT Product FROM Product_List WHERE category = 'Book' order by no desc limit 1;
(note: 'Book' and not "Book")

Your "No" column contains an identifier, which is NOT a row sequence number. Think of an identifier as a EAN or UPC barcode label which you can't read by yourself: it uniquely identifies an item but means nothing per se.

You should take an SQL primer somewhere on the web.

So we cannot use numbering as row sequence number?
Is there any alternative for numbering every row?

What if there is no column "no" in the table?
how to delete latest entry if "no" column is not available there?

Do you have recommended web to learn?
w3schools.com?
tutorialspoint?

[bogQ]

What if there is no column "no" in the table? Then i do not think you can determine latest input from what i see. The fist column ("no" in your case) is identifier ('unique id') that is auto increasing if after adding next record to table. While you can have sum of inputs, and if there is more than one with identical requested in returned data from query, you can not determine witch of returned records is last inserted and you can not determine whom to remove if no ID is present.

I dont think order in DB is guaranteed but i can be wrong. SQL does not guarantee it from what i can read as for mysql

Quote

MySQL by default seems to order by the record structure on disk, (which can include out-of-sequence entries due to deletions and optimisations) but it often initially fools developers into not bother using order-by clauses because the data appears to default to primary-key ordering, which is not the case!

Quote

SQLite will never provide any guarantees for what order rows are stored into, nor how they are selected into (w/o an order by clause), whether you insert them with values() or not.

 

Edited September 2 by bogQ

[HezzelQuartz]

17 minutes ago, bogQ said:

What if there is no column "no" in the table? Then i do not think you can determine latest input from what i see. The fist column ("no" in your case) is identifier ('unique id') that is auto increasing if after adding next record to table. While you can have sum of inputs, and if there is more than one with identical requested in returned data from query, you can not determine witch of returned records is last inserted and you can not determine whom to remove if no ID is present.

I dont think order in DB is guaranteed but i can be wrong. SQL does not guarantee it from what i can read as for mysql

 

So we cannot use numbering as row sequence number?
Is there any alternative for numbering every row?

[ioa747]

https://www.sqlitetutorial.net/sqlite-index/

[jchd]

As previously said a set (= SQL table) doesn't have an inherent order, even less a sequence number. Think of an SQL table as an opaque plastic bag containing all your items. Shake the bag. There is no order in the bag, but you can ask SQL to exhibit selected/all items and display them in the order you find useful, but YOU have to explicitely request the ordering method.

In this context, "last entered" row doesn't make any sense. If you insist on keeping a note of the entry sequence, add a new column to store an entry/last modify timestamp.

Row numbering is "for your eyes" given a specific query. And you CAN indeed show row sequence number in the resultset of a query by using SQL window functions.

Say you create a table:

CREATE TABLE t (id integer primary key, a char, b char);
INSERT INTO t (a, b) VALUES('a', 'one'),
                     ('b', 'four'),
                     ('a', 'two'),
                     ('c', 'five'),
                     ('d', 'ten'),
                     ('a', 'three'),
                     ('c', 'six');

Query the table (output has NO order!):
select * from t;

id    a    b
1    a    one
2    b    four
3    a    two
4    c    five
5    d    ten
6    a    three
7    c    six

Remove a row:
delete from t where b = 'ten'
select * from t;

id    a    b
1    a    one
2    b    four
3    a    two
4    c    five
6    a    three
7    c    six

Show row numbers of a query, ordered by b descending:
SELECT
       row_number() OVER win AS row_number,
       id,
       a,
       b
FROM t
WINDOW win AS (ORDER BY b desc);

row_number    id    a    b
1                         3    a    two
2                         6    a    three
3                         7    c    six
4                         1    a    one
5                         2    b    four
6                         4    c    five

Show row numbers of a query, ordered by id ascending:
SELECT
       row_number() OVER win AS row_number,
       id,
       a,
       b
FROM t
WINDOW win AS (ORDER BY id);

row_number    id    a    b
1                          1    a    one
2                          2    b    four
3                          3    a    two
4                          4    c    five
5                          6    a    three
6                          7    c    six

 

Once again, row sequence numbers are not part of any table and are useless in 99.9999% of use cases.

[jchd]

Index (indices) are of no use here and are something completely different.

[ioa747]

On 9/2/2024 at 1:27 PM, jchd said:

index (indices) are of no use here and are something completely different.

agree
more is a link with a tutorial and I chose this link because it starts with what is rowid (unfortunate choice, it's a good tutorial anyway)

 

One way to have serial numbering without holes is to rebuild the array.
as long as you don't really care about the ID being changed, and it's not related to any other table.
here I continue the example of jchd

expandcollapse popup

; https://www.autoitscript.com/forum/topic/212227-how-to-get-or-read-a-single-data-in-sqlite-database/?do=findComment&comment=1536547

#include <SQLite.au3>

_SQLite_Startup(@ScriptDir & "\sqlite3\sqlite3.dll", False, 1)
If @error Then ConsoleWrite("! SQLite3.dll Can't be Loaded!" & @CRLF)

Local $db = _SQLite_Open()
If @error Then ConsoleWrite("! Open database failed" & @CRLF)

Local $SQL = "CREATE TABLE t (id integer primary key, a char, b char);"
SQLite_Query($db, $SQL)

$SQL = "INSERT INTO t (a, b) VALUES('a', 'one'), ('b', 'four'), ('a', 'two'), ('c', 'five'), ('d', 'ten'), ('a', 'three'), ('c', 'six');"
SQLite_Query($db, $SQL)
_Show("SELECT * FROM t")

$SQL = "delete from t where b = 'ten'"
SQLite_Query($db, $SQL)
_Show("SELECT * FROM t")

$SQL = "SELECT row_number() OVER win AS row_number, id, a, b FROM t WINDOW win AS (ORDER BY b desc);"
_Show($SQL)

$SQL = "SELECT row_number() OVER win AS row_number, id, a, b FROM t WINDOW win AS (ORDER BY id);"
_Show($SQL)

$SQL = "CREATE TEMPORARY TABLE temp_table as SELECT ROW_NUMBER() OVER (ORDER BY id) AS new_id, a, b FROM t;"
SQLite_Query($db, $SQL)
$SQL = "DELETE FROM t;"
SQLite_Query($db, $SQL)
$SQL = "INSERT INTO t (id, a, b) SELECT new_id, a, b FROM temp_table;"
SQLite_Query($db, $SQL)

_Show("SELECT * FROM t")

$SQL = "DROP TABLE temp_table;"
SQLite_Query($db, $SQL)

_SQLite_Close($db)
;--------------------------------------------------------------------------------------------------------------------------------
Func _Show($SQL, $Line = @ScriptLineNumber)
    Local $aResult = SQLite_Query($db, $SQL)
    If $aResult = Null Then
        ConsoleWrite("(" & $Line & ") " & "Query failed" & @CRLF)
    Else
        ConsoleWrite("(" & $Line & ") ~~~~~~~~~~~~~~~~~~" & @CRLF)
        _SQLite_Display2DResult($aResult)
        ConsoleWrite("" & @CRLF)

    EndIf
EndFunc   ;==>_Show
;--------------------------------------------------------------------------------------------------------------------------------
Func SQLite_Query($hDB, $sQuery)
    Local $aResult, $iRows, $iColumns
    _SQLite_GetTable2D($hDB, $sQuery, $aResult, $iRows, $iColumns)
    If @error Then
        Return SetError(1, 0, False)
    Else
        Return SetError(0, UBound($aResult, 1) - 1, $aResult)
    EndIf
EndFunc   ;==>SQLite_Query

 

 

Edited September 4 by ioa747
correction _Show()

[jchd]

Another way to use integer sequential id to mimic row sequence is to use a trigger on insert and one on delete with a recursive cte to adjust the ids as wanted, but that's ugly and useless.

 

Again row sequence numbers are jus "for your eyes only" and carry zero information. From the table posted by OP in post #6, run

select * from Product_List WHERE = 'Pen';

You get ids (= No) from 6 and up, not row sequence numbers. Use the row_number() window function for that.

It's exactly like line numbers shown by some text editors: they are metadata, NOT part of the text, void of any semantic. Yet early Basic mandated unique (non sequential) numbers at the head of code lines, with the resulting spaghetti code we all remember (goto 4753).

[HezzelQuartz]

@jchd@ioa747
Thank You

I have another question, how to insert date and time into sql table?
Is it as simple as below?

insert into Book (Category, Product, Price, Date, Time) Values ('Book', 'Book E', '18000', '04/09/2024', '13:10')

Or should follow some format?
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Next question is, if there is no "no" or rowid table for sorting,
Can I short based on date and time?
Please look my example below

Thank You

[ioa747]

expandcollapse popup

#include <SQLite.au3>

_SQLite_Startup(@ScriptDir & "\sqlite3\sqlite3.dll", False, 1)
If @error Then ConsoleWrite("! SQLite3.dll Can't be Loaded!" & @CRLF)

Local $db = _SQLite_Open()
If @error Then ConsoleWrite("! Open database failed" & @CRLF)

Local $SQL = "CREATE TABLE Book (Category TEXT, Product TEXT, Price INTEGER, Date DATETIME);"
SQLite_Query($db, $SQL)
Local $aQuery

$SQL = "insert into Book (Category, Product, Price, Date) Values"
$SQL &= "('Book', 'Book D', '18000', datetime('now', '+3 hours')),"
$SQL &= "('Book', 'Book G', '21000', '2024-09-06 15:30:00'),"
$SQL &= "('Book', 'Book F', '11000', '2024-09-06 15:10:00'),"
$SQL &= "('Book', 'Book E', '15000', '2024-09-05 13:10:00');"

SQLite_Query($db, $SQL)

$SQL = "SELECT * FROM Book;"
_Show($SQL)

$SQL = "SELECT Category, Product, Price, Date FROM Book ORDER BY Date;"
_Show($SQL)

$SQL = "SELECT ROW_NUMBER() OVER (ORDER BY Date) AS No, Category, Product, Price, Date FROM Book ORDER BY Date;"
_Show($SQL)

$SQL = "SELECT ROW_NUMBER() OVER (ORDER BY Date) AS No, Category, Product, Price, DATE(Date) AS Date, TIME(Date) AS Time FROM Book ORDER BY Date;"
_Show($SQL)

_SQLite_Close($db)

;--------------------------------------------------------------------------------------------------------------------------------
Func _Show($SQL, $Line = @ScriptLineNumber)
    Local $aResult = SQLite_Query($db, $SQL)
    If $aResult = Null Then
        ConsoleWrite("(" & $Line & ") " & "Query failed" & @CRLF)
    Else
        ConsoleWrite("(" & $Line & ") ~~~~~~~~~~~~~~~~~~" & @CRLF)
        _SQLite_Display2DResult($aResult)
        ConsoleWrite("" & @CRLF)

    EndIf
EndFunc   ;==>_Show
;--------------------------------------------------------------------------------------------------------------------------------
Func SQLite_Query($hDB, $sQuery)
    Local $aResult, $iRows, $iColumns
    _SQLite_GetTable2D($hDB, $sQuery, $aResult, $iRows, $iColumns)
    If @error Then
        Return SetError(1, 0, False)
    Else
        Return SetError(0, UBound($aResult, 1) - 1, $aResult)
    EndIf
EndFunc   ;==>SQLite_Query

 

https://www.sqlite.org/lang_datefunc.html

Edit:
"INSERT INTO Book (Category, Product, Price, Date) VALUES ('Book', 'Book E', '15000', datetime('now'));"
datetime('now')) returns the current time in UTC.

If your local time is different, you’ll need to adjust for your timezone.

For example, if your local time is 3 hours ahead of UTC, you can adjust the time like this:
"INSERT INTO Book (Category, Product, Price, Date) VALUES ('Book', 'Book E', '15000', datetime('now', '+3 hours'));"

 

Edited September 4 by ioa747
Update

[Andreik]

4 hours ago, HezzelQuartz said:

Why would you save date and time in two different columns?

[HezzelQuartz]

1 hour ago, Andreik said:

Why would you save date and time in two different columns?

Just trying several possibilites and learning
-----------------------------------------------------------------------------------------------------------------
I tried to sort by date, time, and exact_time in my table below using this code.

Select Product FROM Book ORDER BY Exact_Time ASC

Sorting result is correct by using exact_time.
If I used date (format: dd/mm/yyyy), the sorting result is wrong

Should I use yyyy/mm/dd to sort the date?
Cannot use dd/mm/yyyy?