Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Tue Oct 24, 2017 12:59 am
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 3 posts ] 
Author Message
 Post subject: Couple of database questions
PostPosted: Thu May 02, 2013 5:58 am 
Offline

Joined: Thu May 10, 2007 3:23 pm
Posts: 9
Ok, so after a very long time away Im getting back into Realbasic on my mac.

First question is about opening a database in code. I've tried to narrow it down from the many examples I have found. The code I currently have is this :

dim db as new REALSQLDatabase
db.databaseFile = getFolderItem( "Availability.rsd" )

If db.databaseFile.exists then
If db.connect then
MsgBox "Found database, connected ok"
Else
MsgBox "Found database, but unable to connect"
End
Else
MsgBox "Database not found"
End


So, is the code above considered acceptable ? It certainly works here in the way I expect it to and if I rename the database file or change the filename in the code it also reacts as expected. Eventually I will remove the MsgBox's and add more appropriate code to display the results somewhere in my main window.

The next question is regarding an actual database table I want to create, but is equally appropriate for any table I believe.

The system I am working on first presents the user with a username & password box. Once credentials are entered they will eventually be checked against a table containing that information. Another action from this is to enable me to log certain actions carried out by that user, a kind of audit trail of activity.

The audit trail table will be quite simple; it will contain four fields which represent the following:

Username
Date
Time
Occurence

Having read about the way tables are created, a field called 'rowid' is added internally at creation time. The sqlite3 website suggests that the contents of this field can not be guaranteed if it is created that way, and that the better solution would be to add a field called rowid, where its contents would then be guaranteed.

So, is it better to add 'rowid' myself, and if so what parameters need to be set for it to work correctly ?

At the moment Im thinking PRIMARY KEY, MANDATORY, INDEXED and AUTOINCREMENT with a type of INTEGER, although something lingering in my mind thinks by setting PRIMARY KEY it is automatically indexed anyway, but Im not sure. Are these correct or too much ?

Finally, the Username mentioned above, Im thinking the best way is to insert that into a small global array, so that whenever an occurence takes place that needs to be logged, the username can be grabbed from the array and added to the record that is created in the audit table. Would that be a reasonable way to do this ?

Im kind of just finding my feet again with this stuff so please be gentle :wink:


Top
 Profile  
Reply with quote  
 Post subject: Re: Couple of database questions
PostPosted: Thu May 02, 2013 12:50 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
WonkeyDonkey999 wrote:
So, is the code above considered acceptable ?

The structure of the code is correct. The only possible issue would be accessing a database inside the application's folder. That may or may not be allowed by the OS. You might consider installing the database elsewhere, such as SpecialFolder.ApplicationData.

Quote:
So, is it better to add 'rowid' myself, and if so what parameters need to be set for it to work correctly ?

At the moment Im thinking PRIMARY KEY, MANDATORY, INDEXED and AUTOINCREMENT with a type of INTEGER, although something lingering in my mind thinks by setting PRIMARY KEY it is automatically indexed anyway, but Im not sure. Are these correct or too much ?

It is better to add the column yourself. "rowid AUTOINCREMENT PRIMARY KEY" is sufficient.

Quote:
Finally, the Username mentioned above, Im thinking the best way is to insert that into a small global array, so that whenever an occurence takes place that needs to be logged, the username can be grabbed from the array and added to the record that is created in the audit table. Would that be a reasonable way to do this ?

I don't think an array is necessary. Use a globally accessible string property.

Welcome back, and happy coding.


Top
 Profile  
Reply with quote  
 Post subject: Re: Couple of database questions
PostPosted: Thu May 02, 2013 5:12 pm 
Offline

Joined: Thu May 10, 2007 3:23 pm
Posts: 9
Many thanks Tim, I'm slowly getting the hang of errm, bits of it :lol:


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 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