|
The SQLite documentation says that SQLite is thread safe for reading but not for writing. Specifically, they say you can have multiple threads reading from the DB, but only one thread should write at one time.
Do you think this means that I should also avoid two threads accessing the DB at one time if one of the threads is reading and the other is writing?
IOW, does reading need to be serialized as well?
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
It's "rereads" that are (usually) the problem. If one is writing, and another reading, one for one makes little difference; if the record aren't related. On the other hand, if you're not writing "complete" records at one time, then they have to be "locked".
For "rereads" you have to check for "dirty" (e.g. timestamp changes). "Screen data has been updated".
With related records, you may need need to lock a table or the entire DB (for a very short time).
All calls should be async to avoid UI waits.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
Well that seems related to the issue that I'm encountering. Occasionally I'll receive a "Access denied - Database locked" error message, and I can't understand it because all of my writes are synchronized around a single mutex. So I thought maybe I can't read either when a thread is writing to the DB.
It's an INSERT statement that's receiving the Database locked message, so the only other thing I can think of is that there's something wrong with my threading code and it's actually attempting two writes at once.
The difficult we do right away...
...the impossible takes slightly longer.
modified 10-Nov-23 21:46pm.
|
|
|
|
|
An insert implies an index update; or a "split" somewhere. A simple append might be different.
In effect, the "internal" state becomes dirty. Because it is "lite".
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
Richard Andrew x64 wrote: Do you think this means that I should also avoid two threads accessing the DB at one time if one of the threads is reading and the other is writing?
Yes.
SQLite CVSTrac[^]
|
|
|
|
|
I'm pulling my hair with this one:
(There's a unique index on the [DOMAIN] field.
INSERT INTO DNSLOG ([DATETIME], [DOMAIN], [BLOCKED]) VALUES (133436157350078746, 'optimizationguide-pa.googleapis.com.', 0)
ON CONFLICT (DOMAIN) DO
UPDATE DNSLOG
SET HITCOUNT = HITCOUNT + 1
WHERE DOMAIN = 'optimizationguide-pa.googleapis.com.' The error is clearly in the ON CONFLICT clause because when I run only the INSERT clause, it does what it's supposed to do. And the UPDATE statement works when I run it by itself.
The actual error message is "SQL Logic error near "DNSLOG" syntax error".
Any idea what the syntax error could be?
****SOLUTION****
It's not supposed to have the table name in the "DO UPDATE" statement. IOW:
INSERT INTO DNSLOG ([DATETIME], [DOMAIN], [BLOCKED]) VALUES (133436157350078746, 'optimizationguide-pa.googleapis.com.', 0)
ON CONFLICT (DOMAIN) DO
UPDATE SET HITCOUNT = HITCOUNT + 1
WHERE DOMAIN = 'optimizationguide-pa.googleapis.com.'
The difficult we do right away...
...the impossible takes slightly longer.
modified 5-Nov-23 17:15pm.
|
|
|
|
|
|
|
I would like to have a blank database already written. The list is for monthly entry and exit. Thank you all
|
|
|
|
|
Here is a simple table for SQLite:
DROP TABLE IF EXISTS Times;
CREATE TABLE Times (
Name TEXT,
TimeOn TEXT,
TimeOff TEXT,
);
|
|
|
|
|
Unfortunately using a database is not simple. A post here is unlikely to be able to provide all of the information.
There are tutorials that will explain how to set up a database, then create a schema and finally create a table in that schema. I suspect that is what you want.
You would need to first determine what database you are going to use.
|
|
|
|
|
Entry-Exit System
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
Here is the link I posted to your QA question that was reported then deleted.
Data Models[^] - lots of free, preprepared database schemas.
|
|
|
|
|
I am pretty new using MS SQL. I perform a query over some SQL server table; it returns a record set; but I need to read the last record just to get the value of a certain column.
This is my procedure:
$connectionInfo = array( "Database"=>$myDB, "UID"=>$myUser , "PWD"=>$myPass);
$conn = sqlsrv_connect( $myServer, $connectionInfo);
$query = "SELECT * FROM( SELECT col0, col1, col2, col3 FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 LEFT JOIN t3 ON t1.col2 = t3.col2) AS t ORDER BY t.col0; ";
$rec_set = sqlsrv_query($connection, $query, array(), array("Scrollable" => 'buffered'));
if( $rec_set === false ) die( print_r( sqlsrv_errors(), true));
$connectionInfo = array( "Database"=>$myDB, UID"=>$myUser , "PWD"=>$myPass);
$conn = sqlsrv_connect( $myServer, connectionInfo);
$query = "SELECT * FROM( SELECT col0, col1, col2, col3 FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 LEFT JOIN t3 ON t1.col2 = t3.col2) AS t ORDER BY t.col0; ";
$rec_set = sqlsrv_query($connection, $query, array(), array("Scrollable" => 'buffered'));
if( $rec_set === false ) die( print_r( sqlsrv_errors(), true));
$row_count = sqlsrv_num_rows($rec_set);
if ($row_count === false) die( print_r( sqlsrv_errors(), true));
$reg = sqlsrv_fetch_array( $rec_set, SQLSRV_FETCH_ASSOC, 0);
$first = $reg['column_name'];
$reg = sqlsrv_fetch_array( $rec_set, SQLSRV_FETCH_ASSOC, $row_count - 1);
$last = $reg['column_name'];
It is clear that the sqlsrv_fetch_array DOES NOT work like that. Does anyone know how to achieve my goal, that is, getting last and first records from that record set without generating another SELECT?
Thank you in advance.
|
|
|
|
|
Have you tried:
SELECT TOP 1 COL1 FROM TABLE ORDER BY COL1 and then
SELECT TOP 1 COL1 FROM TABLE ORDER BY COL1 DESC
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
No. The problem here is that I have worked with mysql/postresql and I can retrieve from a recordset any record I want depending on its position in the recordset. So when you have: $var = pg_fetch_result($recordset, $k, 'col'); you are retrieving the 'col' value in the k-th record from that recordset (in PostgreSQL). You can do the same in MySQL. I want to achieve the same in sql server... I have noticed this parameter en the fetch of sql: SQLSRV_SCROLL_LAST in the row parameter of the sqlsrv_fetch_array(resource $stmt, int $fetchType = ?, int $row = ?, int $offset = ?). But for some reason I can not make it run.
|
|
|
|
|
Add a "row number"?
ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
prior action plan to prevent sql database corruption
DISTINCT alternatives in sql query
conversion of query scan to seek
|
|
|
|
|
You should try writing in complete sentences if you want an answer to your question(s).
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
His first post was like this one, 3 years ago, and he still hasn't learned how to completely express ideas in complete sentences.
|
|
|
|
|
Kar_Malay wrote: database corruption
The term "corruption" applies to the data that is stored by the database server and has nothing to do with valid/possible structuring of tables. Which your other two points would seem to suggest.
Preventing inconsistent data, orphans, etc is not something that anyone is going to cover in an online post. Buy yourself a database book or several (beginning and advanced) and read them before you start designing anything.
|
|
|
|
|
yes, that's true
got good result by reconstructing database, eliminating unnecessary open connections, creating missing index, converting scan to seek, minimizing scan re-looping, distinct elimination / avoidant etc.
thanks
|
|
|
|
|
I have 5 tables in my db, I used one form to saved into 4 of them at once. I want the 5th one to be used as a master where I can store the foreign key and be used to display to a table in the frontend.
How do I make all of them add one ID?
How can I display a table row as a column in the frontend?
|
|
|
|
|
|
How depends on the database.
But typically an identity column. The table would have something like the following which is for SQL Server. Other databases all have something similar. Mongodb uses a 'object id' (typically.)
{code}
something_id INT IDENTITY(1,1) PRIMARY KEY,
{code}
That is how you create the column. But populating it in the other tables is more complicated.
1. You must first insert into the main table to create the id.
2. You must then get that id. That almost always involves using a specific form of the insert (first part) which returns the id at the same time as the insert occurs.
3. You then use that return value to create (insert) into the other tables. Obviously you will need to add a column to hold that new value.
You can google for examples of using the above.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
