Advice on Accessing the database

[faccorp]

I am writing a client using OLE DB and VC++ and it will access an SQL database. The database has 6-8 tables in it, some stored procedures, views, etc.

Is it best for the client to open all the tables when the app starts and then close them at the end, or should the app be written so that the table is only opened when it needs data, and then closed immediately after it is finished displaying, updating, or deleting.

There will be multiple users on the database all connecting using the same client app.

One of the tables is a list of transactions (it's a business accounting package) where I want to give the users the ability to MoveNext, MovePrev through the record set. It would seem that this table should always be open.


Could someone comment on different approaches here.

Thanks,

 

[nigelrivett]

>> Is it best for the client to open all the tables when the app starts
no - underlined.

>> should the app be written so that the table is only opened when it needs data, and then closed immediately after it is finished displaying, updating, or deleting

Better.

Don't give the app access to tables at all - access via stored procs. It will make things faster, easier to test, more maintanable and flexible.

Here is a VB dal - you might want to rewrite in C++.

http://www.nigelrivett.net/VB6DataAccessLayer.html

It closes the connection after every access to ensure no locks are held and relies on connection pooling. For a client server just make te connection persistent.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[faccorp]

Nigel,

My app uses a virtual list to display large amounts of data from some of the tables.

It essentially lets the user look through a table that may have 100's of thousands records (but the client app only reads the number of records required to fill the screen).

In order to do this, I need to have the table open for at least read-only mode. How could this be done using stored procedures?

Currently I do use stored procedures and triggers to compute aging balances on the customer table.

Thanks

 

[nigelrivett]

Just return one page of data from the sp - return the next page when requested.

Otherwise what happens if someone tries to change the table while it is being displayed by another client.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[faccorp]

So how does stored procedures fix this problem?

Either way a second client might be looking at stale data - right?

In another project, I have an application that writes to an MS Access database. Everytime a table is changed by a client, the client increments an integer in another table. Every 5 seconds, each client reads this integer and compares it to the previous reading. If the value changed, then the client requery's the table.

Is this a valid approach?

I'm real curious about the sp method. I always felt that my method of updating and checking an integer was a brute force method of validating new data from another user. There must be a better way.

 

[aalmeida]

You can fix the problem of data changing behind the scenes with the use of locks.
And yes on any circunstance never allow direct access to tables allways use either views or stored procedures, for extreme cases use stored procedures that access data from vies.

AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[faccorp]

I will look into 'locks'. I'm not clear how this will solve the problem but I'll see.

Do you mean that I shouldn't read data directly from a table? Why? Whats the difference?

I am using a 'SELECT * from tablename' type of command in my client (using OLE DB) What's the danger?

Thanks,

 

[nigelrivett]

Means you can't change the database structure without changing the client.
Also sholdn't use select * - just get the columns you need. One of the biggest problems with efficiency is caused by not getting the correct resultset.

Add a timestamp column to the table
Get a page of data from an SP including a timestamp for each row.
If the client makes any changes then call the update SP passing back the timestamp. If the timestamp is not correct then refuse the change informing the client that someone else has changed the data.

You have the choice between offering the client a snapshot of the data - in which case you could build the resultset and let the client page through or allowing the client to view data that has changed between page reads - in this case the resultset will be a single page of data but you have to decide what to do about rows being added and moving the page window.
In any case if you try to lock the table it will prevent updates taking place so unless the table is static it's not a good choice.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[faccorp]

Actually I can change the structure.

I use a manual accessor for the data. MS provided a sample application called DBViewer. I modified it to allow the user to select the columns they wanted to view. Then I added support for a virtual list.


How would you do a virtual list with a stored procedure? When you say 'Get a page from a sp' would the stored procedure return a 'temp' table with only x number of records? where x is the viewable items on the client screen?

 

[aalmeida]

A few considerations on this:
SELECT * from MyTbl is the kind of query that will be stoped by the DBA in case they need to run special reports and all he needs is to limit the cost of the querys, also is the kind of query that has a tendency to hang and not return anything because it loks it self in concurrent querys to the table (just for fun imagine a table with 20 millon records and 5 people query it at the same time), also how do you index and optimize the server for this kinds of querys? without optmization querys on large amounts of data will quickly become your worst night mare and make the server unmanageble.

there are also other advantage of not accessing the tables direcly.

1st) The use of SP reduce the risk of sql injections and other Atacks to your SQL server because it gives you more control on what query it will run.

2nd) On SQL 2K you can combine the SP with indexed views to gain extraordinary performance on extracion of large amounts of data because now you can combine a Table clustered index with a Non-clustered indexed view.

Loks are allways present and sooner or latter you will have to deal with it, and you need to be able to control what kinds of lok is aprpriate to every situation.


AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[faccorp]

Ok, everyones help has been greatly appreciated.

So let's say my db has 10 tables.

Then do I need 4 Stored Procedures for each table? One that Updates/Deletes/Inserts? I also need a SP to get specific row data to populate a form.

Seems like a lot. I'll need about 40 stored procedures. Is this the best way to go. My Client will only access data or modify data via a View or Stored Procedure.

Advice?

Thanks.

 

[aalmeida]

It all depends on the amount of processing you might need for each table. Your work might be the same if you use a total of 4 SPs or 40. As a general rule I consider the following on my developement:

- Every SP does one simple operation (select, insert, update, delete)
- how easy will be to safe keep data integrity?
- how easy will be to process the data set?
- how intense error catching you will need?

as the answer to this question goes from easy to hard the more SP I need

AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"