Sql etiquette or protocol?

Smee · Oct 29, 2011

I have a database and a selection of modules which all want to access the database.

What is the correct or best method of doing so?

Should I ;

a) Define the database and cursors and initialise in the Main Module

B4X:

Dim SQL As SQL
Dim Cursor1 As Cursor

Sub Activity_Create(FirstTime As Boolean)

If FirstTime=True Then
       SQL.Initialize(File.DirInternal, "database.db", True)
end if

then in each activity refer to the database initialised in Main

B4X:

Main.Cursor1 = Main.SQL.ExecQuery("SELECT Customer FROM Customers")

OR

b)
Initialise the database in each activity by the following process

close the database when calling another acttivity, re-open it in the next activity, manipulate data / user requests etc

close the database

exit the activity
go back to Main and initialise the database again for use there again

All comments suggestions etc are welcome

Joe

Erel · Oct 30, 2011

I think that the best option is to create a code module that handles all access to the database.

devlei · Oct 30, 2011

I think that the best option is to create a code module that handles all access to the database.

Would that be similar to the DBUtils module?
If so, would that not slow things down when one has a series of database calls to make?

Erel · Oct 31, 2011

DBUtils is also a code module. However DBUtils purpose is a bit different. It helps with common tasks.

Moving the methods to a code module doesn't have any effect on performance.

Smee · Oct 31, 2011

Thanks for the suggestion, Looks like a code re-write coming up

devlei · Oct 31, 2011

Joe, it looks like I am sitting with a similar situation to you.

I access my database from every Activity Module, and, unless I am missing something obvious, don't see what advantage it would be to have a seperate code module to handle accessing the database.

I started off by using your option b (initialising with every module), but found it confusing when one module is called from within another one. Last week I changed over to your option a (initialising in the Main Module), but haven't tested it to see the results.

Did you experience problems using option a, that you feel a necessary to change?

Dave

JaunLukePicard · Oct 31, 2011

The benifits of a Code Module

If I am not mistaken, the benifits to a Code Module are that you only write the routines once, then call them as needed from throughout the application. Think of it as a DAL (Data Access Layer).

:sign0163:

Smee · Nov 1, 2011

devlei said:
Did you experience problems using option a, that you feel a necessary to change?

hI dAVE,

No i have not had any problems. It was just that i am unsure re memory usage etc. I have actually used a combination of both methods to see if it causes a crash or something but everthing seems to work ok.

In reality it seems that there is no difference to using code like Main.cursor1 or putting it in a module called db and saying db.cursor1.

Although it is probably more memory efficient to dim a cursor in each sub routine which is what i will eventually end up doing i think.

One thing about coding and learning at the same time is it tends to create messy code at times.

At least in my case anyway

Cheers

Sql etiquette or protocol?

Smee

Well-Known Member

Erel

B4X founder

devlei

Active Member

Erel

B4X founder

Smee

Well-Known Member

devlei

Active Member

JaunLukePicard

Member

Smee

Well-Known Member

Similar Threads

Sql etiquette or protocol?

Well-Known Member

B4X founder

Active Member

B4X founder

Well-Known Member

Active Member

Member

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency