-
Welcome to B4X forum!
B4X is a set of simple and powerful cross platform RAD tools:
- B4A (free) - Android development
- B4J (free) - Desktop and Server development
- B4i - iOS development
- B4R (free) - Arduino, ESP8266 and ESP32 development
Android Question Update SQLite Version
- Thread starter janitra
- Start date
- Similar Threads Similar Threads
As far as I have read before, there is no way. It depends on Android (Google) updated their SDK or API version.
Last edited:
Upvote
0
Or you want Row Count?
Oh you mean ROW_NUMBER().
I think there are workarounds in SO or the Internet.
Last edited:
Upvote
0
@janitra
You don't say what you are trying to do? The unique table field 'rowid' (integer, autoincrement) has existed for as long as SQLite but is not automatically retrieved in a query by asking for all fields *. Having identified the record it is very convenient for updating a record by sole reference to the rowid. For example, I want to update a single record where 'PartNumber' = 1290375 and 'Description' = 'Top Plate'
I can retrieve MOST fields (*) in a record with:
Note that this query does NOT return the variable rowid even though you asked for ALL fields with the asterisk *.
To retrieve the rowid in the query above you must call it explicitly:
Where you want to work only with a single record it is useful to reference it with rowid which remains constant until the record is deleted when it completely disappears from the numerical sequence. So having determined the record rowid and set it against a local variable.... say MyRowid you can address as a single record without having to craft a longer WHERE sequence.
You don't say what you are trying to do? The unique table field 'rowid' (integer, autoincrement) has existed for as long as SQLite but is not automatically retrieved in a query by asking for all fields *. Having identified the record it is very convenient for updating a record by sole reference to the rowid. For example, I want to update a single record where 'PartNumber' = 1290375 and 'Description' = 'Top Plate'
I can retrieve MOST fields (*) in a record with:
Query:
SELECT * FROM MyTable WHERE PartNumber = 1290375 AND Description = 'Top Plate'Note that this query does NOT return the variable rowid even though you asked for ALL fields with the asterisk *.
To retrieve the rowid in the query above you must call it explicitly:
Query:
SELECT *, rowid FROM MyTable WHERE PartNumber = 1290375 AND Description = 'Top Plate'Where you want to work only with a single record it is useful to reference it with rowid which remains constant until the record is deleted when it completely disappears from the numerical sequence. So having determined the record rowid and set it against a local variable.... say MyRowid you can address as a single record without having to craft a longer WHERE sequence.
Query:
UPDATE MyTable SET Description = 'Bottom Plate' WHERE rowid = MyRowid
Last edited:
Upvote
0
The OP's question is not about rowid. It is about Row_Number Function which was introduced in SQLite version 3.25 in 2018. The devisce's SQLite version is what dictates what version of SQLite you have. Unless, you have a way to upgrade your Phone/Tablet to a newer one where the SQLite is 25 or over, you will not be able to use that function. B4A SQL library passes the commands to the SQLite version of the device only. It does not control it.
Upvote
0
Hi @aeric
Thanks for your continuous support.
Yea, I'll try to do some workaround on monday.
Hi @RichardN
I want to get the last transaction from a group in a selected date.
That's why I think it will be convinient if I can use Row_Number() function on SQLite.
And I read that it come with SQLite v. 3.25 and I read more and stumble on a B4J post to update a SQLite version, and I thought maybe.... we can do same thing in B4A.
After some searching, I cannot found a thread and decide to ask.
And apparently we cannot do it in B4A.
So I'll try to do some work around on Monday as aeric suggested.
Hi @Mahares
I see.
I have my suspiscion that I need to update my phone OS.
Because I already change my target SDK to 30 and update SDK Platforms to 30 to no avail.
I'll test this in the near future.
Thanks a lot.
Hi @RB Smissaert
I see.
This is new thing for me.
I'll try to research it.
Thank you for your knowledge.
Upvote
0
@janitra
To determine the last record added is easy, it will be the greatest rowid. To determine the last records modified in a group is rather more complicated and will involve you adding a date/time field and keeping it up to date every time an UPDATE or INSERT is executed.
Last Record Added:
SELECT MAX(rowid) FROM MyTable
Records modified [In a group]:
SELECT * FROM MyTable [WHERE RecordModifiedDate BETWEEN '20230101224554' AND '20230102224554'] ORDER BY RecordModifiedDate
Last edited:
Upvote
0
Hi janitra,
I think you should explain better what you are trying to do.
If I guess right, let say you have 100 records in your table, you want to select Top 10 last records which are added to the table sorted by date so you may Order By Date_Added Desc. You want to list these records from 1 to 10 where 1st row is the last added records up to 10 is the 10th latest record.
After you get this result, you want to display them in a table or json.
What I am thinking is why not use a while or for loop to add the row number?
I think you should explain better what you are trying to do.
If I guess right, let say you have 100 records in your table, you want to select Top 10 last records which are added to the table sorted by date so you may Order By Date_Added Desc. You want to list these records from 1 to 10 where 1st row is the last added records up to 10 is the 10th latest record.
After you get this result, you want to display them in a table or json.
What I am thinking is why not use a while or for loop to add the row number?
Upvote
0
Alex...
The OP is looking for the last X records added or modified. Your last_insert_rowid() function only returns the same single value as MAX(rowid)
Upvote
0
Believe or not but I've seen the situation when MAX(rowid) and last_insert_rowid() had different results. MAX(rowid) returned 150 and you exepcted that the new record will be 151 but last_insert_rowid() returned 35 and one the next insert after it returned 151. Why? Because some records were deleted and 35 were among them.
Upvote
0
Hi @Erel
Thanks for the information.
I'll see in the future if I can update my phone OS.
Hi @aeric , @Alex_197 , @RichardN
Sorry, I should be more specific.
Lets say I have table with 3 column with 4 rows of data.
SQLite:
PurchaseDate ItemCode Price
1 June 2023 08:04:05 Apple 5000
1 June 2023 08:10:32 Apple 5100
1 June 2023 08:03:17 Orange 3400
1 June 2023 09:00:03 Orange 3300NB: The table is just an example and the content is just for easy reading on forum purposes.
That's why, I'm thinking it's easier to use Row_Number()
I already solve this problem by using
SQLite:
SELECT * FROM Purchase P
WHERE PurchaseDate = (SELECT PurchaseDate FROM Purchase
WHERE strftime('%d-%m-%Y', PurchaseDate) = strftime('%d-%m-%Y', P.PurchaseDate) AND
ItemCode = P.ItemCode ORDER BY PurchaseDate DESC LIMIT 1)
Upvote
0
@janitra
If you rethink your database design and store the Purchase date/time in a more useful format it becomes very much easier. Try using yyyymmddhhmmss for PurchaseDate so your first entry becomes '20230601080405'. The query becomes much simpler and sort DESC and LIMIT 1 clauses return a only single value of the latest price on the day. When you need to bring the date/time into a user interface simply write a string function to unjumble it.
You might also bear in mind that an SQLite TRIGGER is the smartest way of populating a Date/Time field when it comes to creating or updating a row.
Read about it here.......................TRIGGERs in SQLite
@Alex_197
I tried a couple of different databases and I can't seem to get last_insert_rowid() to return anything but zero. ????
Your Data.....:
PurchaseDate ItemCode Price
1 June 2023 08:04:05 Apple 5000
1 June 2023 08:10:32 Apple 5100
1 June 2023 08:03:17 Orange 3400
1 June 2023 09:00:03 Orange 3300If you rethink your database design and store the Purchase date/time in a more useful format it becomes very much easier. Try using yyyymmddhhmmss for PurchaseDate so your first entry becomes '20230601080405'. The query becomes much simpler and sort DESC and LIMIT 1 clauses return a only single value of the latest price on the day. When you need to bring the date/time into a user interface simply write a string function to unjumble it.
For the Apples:
SELECT Price From Purchases WHERE PurchaseDate LIKE '20230601%' AND ItemCode = 'Apple' ORDER BY PurchaseDate DESC LIMIT 1
For the Oranges:
SELECT Price From Purchases WHERE PurchaseDate LIKE '20230601%' AND ItemCode = 'Orange' ORDER BY PurchaseDate DESC LIMIT 1You might also bear in mind that an SQLite TRIGGER is the smartest way of populating a Date/Time field when it comes to creating or updating a row.
Read about it here.......................TRIGGERs in SQLite
@Alex_197
I tried a couple of different databases and I can't seem to get last_insert_rowid() to return anything but zero. ????
Last edited:
Upvote
0
last_insert_rowid() only works after you inserted record(s) and are still in the same database connection. If you exit and run the database again, you will get 0.
I also highly recommend using a parameterized query especially when you are working with sensitive data that involve dates.
Upvote
0
Thanks @Mahares that's a new one on me.... you learn something every day
It also makes last_insert_rowid() useless in the solution that @janitra is seeking as it will revert to zero on every database discconnect.I agree entirely. Many of the misunderstandings relating to B4X arise from new users writing code that is unreadable. If they have got this deep into SQLite functions, they probably already understand the basics of wrapping SQL commands in B4X code...... so I keep it as simple as possible.
Upvote
0
You are still not providing good information for members to help you. The date format you supplied in your example table does not work the way you have it with strFtime function. Also, your query is too complex and above all not correct at all. I am still puzzled by how you say you solved it with the code you entered.
Notice, that when you confuse members with your posts, the thread loses its essential pupose and you start getting answers alll over the place.
Upvote
0
