Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Sat Sep 21, 2019 4:11 am
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: Operation cannot be completed because the database is closed
PostPosted: Sat Jan 19, 2013 10:22 pm 
Offline

Joined: Sun Nov 23, 2008 12:27 am
Posts: 404
I am in the process of re-writing one of my file scanning console apps in which I decided to try something a bit different.

For the most part, the console app will index folders and its contents to a rb database in which will allow the end user to perform certain operations on the indexed files.

In the following code:

dim dbRec as new DatabaseRecord
dim dcm as new dicomClass
dim rs as RecordSet, fileID, objectCount as Integer, fileObject, activeObject as FolderItem

while app.copyBotDB.Connect = true

rs = app.copyBotDB.SQLSelect("select b.file_id, a.folder_path || '\' || b.file_name [file_path] from folders a join files b on b.folder_id = a.folder_id where dicom_flg = 1 and is_dicom_processed = 0 limit 500")

if rs.RecordCount = 0 then exit ' if file list is empty then exit.
rs.MoveFirst

do
fileID = rs.Field("file_id").IntegerValue
fileObject = GetFolderItem(rs.Field("file_path").StringValue)

if dcm.checkDicom(fileObject) = true then ' if file object is dicom
dbRec.IntegerColumn("file_id") = fileID
dbRec.Column("patient_id") = dcm.read("patientID")
dbRec.Column("patient_name") = dcm.read("patientName")
dbRec.Column("dob") = dcm.read("dob")
dbRec.Column("gender") = dcm.read("gender")
dbRec.Column("accession_number") = dcm.read("accessionNumber")
dbRec.Column("study_date") = dcm.read("studyDate")
dbRec.Column("study_time") = dcm.read("studyTime")
dbRec.Column("modality") = dcm.read("modality")
dbRec.Column("instance_number") = dcm.read("instanceNumber")
dbRec.Column("institution_name") = dcm.read("institutionName")
dbRec.Column("manufacture") = dcm.read("manufacture")
dbRec.Column("manufacture_model") = dcm.read("manufactureModel")
dbRec.Column("sop_instance_uid") = dcm.read("sopInstanceUID")
dbRec.Column("series_instance_uid") = dcm.read("seriesInstanceUID")
dbRec.Column("study_instance_uid") = dcm.read("studyInstanceUID")
app.copyBotDB.InsertRecord "dicoms", dbRec

end if

if app.copyBotDB.Error then' Commit changes to db
print "ERROR: " + app.copyBotDB.ErrorMessage

else
print "indexing - " + str(fileID) + " - " + fileObject.AbsolutePath
app.copyBotDB.SQLExecute(("update files set is_dicom_processed = 1 where file_id = " + str(fileID)))
app.copyBotDB.Commit

end if

rs.MoveNext
loop until rs.EOF

wend


I get mix results with data being inserted in its designation table. Either the data is inserted in or it outputs an error code of: Operation cannot be completed because the database is closed.

any suggestions as to why the db connection closes? Also, I noticed that it sometimes does not loop through all the recordsets.

thank you in advance.


Top
 Profile  
Reply with quote  
 Post subject: Re: Operation cannot be completed because the database is cl
PostPosted: Sun Jan 20, 2013 12:04 am 
Offline
User avatar

Joined: Thu Mar 01, 2007 2:02 pm
Posts: 237
Location: Sunny (generally!) Southern California
What you are doing is
Quote:
while app.copyBotDB.Connect = true

[...stuff deleted...]

wend


and that can cause problems because you are opening new connections each time through the loop.

A better way is to do something like

if app.copyBotDB.Connect = true then
'do all your stuff

' close the database when you are done with it.
end if


- Dale

_________________
-----
Real Studio 2012r1 on Windows 7 (64 bit)
-----
It has been said that politics is the second oldest profession.
I have learned that it bears a striking resemblance to the first.
- Ronald Reagan


Top
 Profile  
Reply with quote  
 Post subject: Re: Operation cannot be completed because the database is cl
PostPosted: Sun Jan 20, 2013 9:23 am 
Offline

Joined: Sun Nov 23, 2008 12:27 am
Posts: 404
Thank you Dale.

I think I found the main culprit. I was running this code in a thread in which the app closed before it had a chance to finished.

But in light of your feedback below, with regards to my While...Wend statement. if I have a table that may have a few million rows, instead of loading the entire table to memory, my thought process was to process 500 records at a time without creating a new connection each time I re-load the recordset.

Is there a better way for me to reconstruct my code to ensure that only one connection is maintained and variables are reused properly to ensure the app's memory maintains and does not grow out of control?

thank you again.


Top
 Profile  
Reply with quote  
 Post subject: Re: Operation cannot be completed because the database is cl
PostPosted: Sun Jan 20, 2013 2:29 pm 
Offline
User avatar

Joined: Thu Mar 01, 2007 2:02 pm
Posts: 237
Location: Sunny (generally!) Southern California
Sure. What I generally do is open the connection in the App.Open event and have the db variable global. (Be sure to close it in the App.close.) Once you open a connection you can read in as many records as you want using a loop like you have. Something like this might work...

' recordnumber is a global that keeps track of the last record read.
for i = recordnumber to recordnumber + 500
'do your stuff, and don't forget error checking
next i
recordnumber = i


Probably not the best way but it should work...unless someone alters the record count in the database.

I'm sure that some of the database gurus here can give better suggestions.

- Dale

_________________
-----
Real Studio 2012r1 on Windows 7 (64 bit)
-----
It has been said that politics is the second oldest profession.
I have learned that it bears a striking resemblance to the first.
- Ronald Reagan


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group