BDE -> MS_SQL refreshing tables

Hello,
I am using
 Borland C++ -> BDE -> SQL links -> MS_SQL

When opeing a table using DbiOpenTable, should I then leave the table open and keep doing operations. If I do this and the table is changed the changes are not updated on my client. They are updated when I close the table and reopen it though.

What I want to do is be able to update information and add new records, then next time I do a request the updated information is there.

I have just started out using this setup and I hope the poblem is obvious. Any suggestion would me much appreciated.

Thanks for your time.
Eban.
ebanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cymbolicCommented:
I work primarily in VB with SQL server, but it is typical behaviour for an SQL resultset not to show added records.  This is because when you initially get your result set, you are working with a copy of it only on your client, and until you get another copy, you only have what you started with.  Inserting new rows will not insert them also into the rows you have on your client machine.  You generally have to reselect(requery) to get the changed information.  In VB you can have what is called a dynaset cursor keyset.  In this instance, if something is changed on the server that you have in your resultset, you do get the changes, but that is because the server sends down a keyset, and doesn't actually "Fetch" the data you asked for until you position on that row.  You should look in your language for ways to set up dynamic cursors using keysets, or for a refresh method to requery your server.  Gnerally, in our environment, using table based  methods is more limiting than using queries and resultsets (or recordsets).  This is because we get more options to control the type of access and recordset returned, and can specify read only sets to improve performance and reduce locking in multiuser situations.  Check of you have alternatives to the tabel method for accessing.  You may have some wiggle room there.

Also, you need to know that SQL server will escalate your page locks to complete table locks under some selection criteria, more likely if you are using table methods.  This will cause you multiuser problems.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ebanAuthor Commented:
I need a more BDE specific answer. When using BDE to access MSSQL, how should I go about getting the result sets. As far as I can tell you have to call a series of DBI functions, i.e.
(In this order of calling)
DbiQAlloc
DbiQPrepare
DbiQSetParams
DbiQExec
DbiQInstantiateAnswer

Is this the right way to go about using BDE to get the result sets, or is there another way?

Thanks Eban.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.