Post by kokenge on Jun 28, 2009 14:14:17 GMT -5
AAAAGGGGHHHH!
Frustrations with SQLite.!!
I post this in the hopes that it can prevent others from having the same problems I"ve had with SQLite.
SQLite has it's place for holding and looking up information.
I figured I could work around the fact that it locks the entire DB when it does a write. But that is not the case. The real problem is SQLite's inability to free them up.
At times, I've had to read the DB into memory so it would think it had 2 databases.. But for some reason that confuses SQLite and many times it still locks up the system.. You would think that when reading from the memory file and writing to the disk DB file that wouldn't be the case?
Here for example is a code snippit of a very simple routine.. It reads a trace table, and a couple other tables. It needs to INSERT records based on some aggregate informations it finds.
However no matter if you connect the DB immediately before the insert and disconnect immediately after -- it locks the DB. The only out of the situation is to kill the RB server and restart the Console.
This happens to be one of the files I tried to move to memory so it reads from one DB and writes to another. But it still locks..
Can't wait for mySQL..
Hope this helps someone...
Have a great day..
I'm going out in the back yard and eat worms..
Dan
Frustrations with SQLite.!!
I post this in the hopes that it can prevent others from having the same problems I"ve had with SQLite.
SQLite has it's place for holding and looking up information.
I figured I could work around the fact that it locks the entire DB when it does a write. But that is not the case. The real problem is SQLite's inability to free them up.
At times, I've had to read the DB into memory so it would think it had 2 databases.. But for some reason that confuses SQLite and many times it still locks up the system.. You would think that when reading from the memory file and writing to the disk DB file that wouldn't be the case?
Here for example is a code snippit of a very simple routine.. It reads a trace table, and a couple other tables. It needs to INSERT records based on some aggregate informations it finds.
However no matter if you connect the DB immediately before the insert and disconnect immediately after -- it locks the DB. The only out of the situation is to kill the RB server and restart the Console.
This happens to be one of the files I tried to move to memory so it reads from one DB and writes to another. But it still locks..
Can't wait for mySQL..
Hope this helps someone...
sqliteconnect #sql, runINVdb$ ' Connect to the DB
for tn = 1 to numWpo
thisWpoNum = val(word$(wpoList$,tn,","))
prefStorageId$ = ""
pretStorageId$ = ""
sql$ = "
SELECT
workTrace.wpoNum as wpoNum,
workTrace.fstorageId as fstorageId,
workTrace.fPreWpoNum as fPreWpo,
workTrace.fPreFromTo as fPreFT,
workTrace.tstorageId as tstorageId,
workTrace.tPreWpoNum as fPreWpo,
workTrace.tPreFromTo as fPreFT,
workTrace.tendQty as endQty,
wpoVariety.varietyNum as varietyNum,
wpoVariety.areaId as areaId,
wpoVariety.year as year,
sum(wpoVariety.pcnt) as sumPcnt,
avg(wpoVariety.pcnt) as avgPcnt,
sum(wpoVariety.qty) as sumQty,
sum(wpoVariety.glQty) as sumGlQty,
sum(wpoVariety.yield) as sumYield,
sum(wpoVariety.yield * workTrace.fmovQty * (wpoVariety.storageId = workTrace.fstorageId)) as fyq,
sum(wpoVariety.yield * workTrace.tmovQty * (wpoVariety.storageId = workTrace.tstorageId)) as tyq
FROM workTrace
LEFT JOIN wpoVariety
ON wpoVariety.wpoNum = fPreWpoNum
AND wpoVariety.fromTo = fPreFromTo
AND wpoVariety.storageId = fstorageId
OR wpoVariety.wpoNum = tPreWpoNum
AND wpoVariety.fromTo = tPreFromTo
AND wpoVariety.storageId = tstorageId
WHERE workTrace.wpoNum = ";thisWpoNum;"
GROUP BY workTrace.wpoNum,workTrace.tstorageId,
wpoVariety.varietyNum,wpoVariety.areaId,wpoVariety.year"
#sql execute(sql$)
WHILE #sql hasanswer()
#row = #sql #nextrow()
wpoNum = #row wpoNum()
year = #row year()
sumPcnt = #row sumPcnt()
avgPcnt = #row avgPcnt()
sumQty = #row sumQty()
sumGlQty = #row sumGlQty()
sumYield = #row sumYield()
fyq = #row fyq()
tyq = #row tyq()
sql1$ = "INSERT INTO wpoVariety VALUES(";_
wpoNum;",'";_
year;",";_
qty;",";_
sumGlQty;",";_
gaugeGlQty;",";_
concQty;",";_
avgPcnt;",";_
sumYield;")"
sqliteconnect #sql1, runINVdb$ ' Connect to the DB
#sql1 execute(sql1$)
#sql1 disconnect()
WEND
next tn
#sql disconnect()
wait
Have a great day..
I'm going out in the back yard and eat worms..
Dan