[code]
IF blnResult = True then
// add to database
dim dr as new DatabaseRecord
dim d as New date
'dr.Column(“dteCreated”) = d.SQLDateTime
dr.olumn(“dteCreated”) = “2015-01-01”
dbDatabase.InsertRecord (“syncLog”,dr)
'dbDatabase.Commit
'dbDatabase.Close
System.DebugLog "sync db update = " + d.SQLDateTime
else
MsgBox "DB error " + dbDatabase.ErrorMessage
System.DebugLog “failed to update DB”
end if
This is the code, in the end of a thread.
Is there something obviously wrong with this code!? Because each time I run the software the program freeze when reaching this point.
I’m not sure the problem is all here, but I’m pretty sure, since everything else seem to work as expected.
There are loads of database activities in the thread and they work. I see no (obvious) reason why this update will not work!
There is no error message. The SW just freeze…" (not answering)" is the most annoying s-it with Windows… Bl.
Well, the obvious thing is the msgbox. If blnResult = false it will spawn a ThreadAccessingUIException.
Does the Thread have it’s own database connection?
No where do I see you checking to see if the database has an error. That should be done immediately after the InsertRecord command. The way you’ve wrote it, it looks like this happens after another db transaction so it’s hard to tell.
Thank you!
I wasn’t aware that code that’s not in use could cause a problem. Of-course it can!
Still the same freezing result… The Log says “Database connecting fine”
Hmm… I’m not sure… Maybe the best is to simply remove it all and solve it in later in some other way.
When things get out of control, it’s sometimes what I do. I need to move on!
[code]
IF blnResult = True then
// add to database
dim dr as new DatabaseRecord
dim d as New date
dr.Column(“dteCreated”) = d.SQLDateTime
dbDatabase.InsertRecord (“syncLog”,dr)
IF dbDatabase.Error THEN
'MsgBox dbDatabase.ErrorMessage
System.DebugLog "ERROR!!! Database error " + dbDatabase.ErrorMessage
ELSE
System.DebugLog "Database connectiong fine! "
END IF
'dbDatabase.Commit
'dbDatabase.Close
System.DebugLog "sync db update = " + d.SQLDateTime
else
'MsgBox "DB error " + dbDatabase.ErrorMessage
System.DebugLog “failed to update DB”
end if
[quote=156619:@Norman Palardy]Is dbDatabase a unique connection to the database for this thread ?
Or a shared one used in the thread & elsewhere ?[/quote]
It’s used earlier and as far as I can tell, it’s working.
This also freeze:
dim d as New date
sql = "INSERT INTO [syncLog] (dteCreated) VALUES ('" + d.SQLDateTime + "')"
System.DebugLog sql
' Log says: INSERT INTO [syncLog] (dteCreated) VALUES ('2015-01-03 14:13:13')
dbDatabase.SQLExecute(sql)
Try to copy & paste your SQL Statement into a database browser like Navicat and check if it runs.
You do not tell us what kind of database on which platform you’re using. SQLlite for instance locks your tables when data is written. This leads to reduced performance when accessing from different threads. When using MySQL or MSSQL or ODBC connections I often ran into problems with Database Cursors and lack of open ports for TCP connections or personal firewall issues on Windows.
It works if I’m not closing the database in the end.
I don’t like recordSets and I shall re-write all code using SQL commands instead.
I really wasn’t aware of the complete SQL access… however, now I know!
Also, try to collect all DB communication to fewer places, not spread out within the application.
Can someone please in short describe for me why I need to check for errors all the time!?
I mean, if I open the DB connection on the top, then do something for 20-30 lines of code, then close the DB connection.
I mean, if the DB open on top, then, what can happen a few lines down the page!?
Also, if things do not open, there are errors, then what will happen!? It’s really not the end of the world…?
I’ve gotten the impression that many people open the DB when the application open and then close it when the app close.
However, with such procedure I can understand that ANYTHING can happen and ANYTHING actually will happen!
Well I suggest to encapsulate your DB Tables in Objects. For instance if you use a Table named “Contacts” with Familyname and Phone as fields create an Object with “Contact”, .Familyname and .Phone as Properties and .Get .Save Methods doing all the DB Stuff for this Object.
I suggest not to do all stuff with SQL Executes for two reasons: Fist in cases you need to switch your DBMS (e.g. from Sqllte to mySQL) you have to rewrite all your code. Same with added Fields or changed Data-Model in general. Second your code is pretty open for code injection. You have to check every single byte before pasting it in your SQL queries and every string you use. This will make your Apps quite complex. As best practise use prepared statements instead and direct SQL Executes only when not avoidable.
Take a look on “Introduction to Xojo” by Brad Rhine (the blue one in iBook store) it is pretty good.
Every variable being set to a new value could hold an unwanted value if one doesn’t test prior or immediately after the assignment - especially if the new value comes from “outside” of the program (database, file, TCP socket, keyboard, mouse, etc.). It’s a part of defensive programming.
I’m surprised that you’re closing the database connection at the end of this routine since you’re not opening it. Could is be that it fails on the second call?
I’m opening the database earlier in that code, as well as updating other stuff that works. That’s why I find it … strange that the pasted code didn’t work.
Well. Let’s move on:
What’s wrong in this line? Why doesn’t TOP 1 work!?
sql = "SELECT TOP 1 intID, dteCreated FROM [syncLog] ORDER BY dteCreated DESC"
It’s something for Appleusers, right!?
One day I may be one in the crowd!
I get the picture.
However, I’m creating an analytic tool. All data is collected by each user and saved in the database.
The database itself is not safe or encrypted. At this point, I see no reason…
I’m aware of SQL injection but what is the chance or the risk of such procedure in a self made analytic tool where the user can open the database in any other software!?
Why would I protect the database from the user when the user add all data by hand him/herself!
If people want to hack, I truly believe there are other much more interesting projects to dig in to!
However, I have now named the Modules names such as Area51, 2600, jolt and similar so the people interested in the secrets know where to look! (Discussed in a near-by thread.)
