Solved

SQL-Server2000 and Delphi?

Posted on 2003-11-15
8
595 Views
Last Modified: 2010-04-05
I am using Delphi ADO components to connect to local access DB. I also use TDBGrids and other standart Data Controls...
How can modify my application to connect to SQL-server? My SQL-Server DB is allready up.

Anyone ever made this with delphi in a SQL multi-user env?
I use D5. Any special insites with this issue?

Any help will be great.
0
Comment
Question by:morgantop
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 3

Expert Comment

by:Peter_
ID: 9758507
My first advice would be to simply change the connectionstring and run the application from within the delphi environment. Put plenty of breakpoints at suitable places, such as before opening a query, and spot if/when thing start to go wrong. This will give you some idea.

The SQL accepted by SQL server is rather simular to MS Access, but some statement may need to be rewritten.

Apart from that, most things should run. Try correcting these errors if any, just to make things running again.

When they do, it is time to look over the design. Even though most things done using access also works using SQL-Server in the same way, there are many situations when it can be done much more effectively.

If you have complex sql queries, try moving as many of these as possible to stored procedures on the server. These always execute faster. If you make massive use of TADOTable components, try replacing these with TADOQuery or stored procedures instead.

Especially important if using filters. Filtered tables can always be rewritten to running much more effectively using Queries or stored procedures.

If your application need to support having multible users, you need to also look into using transactions and transaction handling. But this is not needed just for changing the database. You can still make use of the better preformance of SQL-server even with a single user application. I think the best feature is the possibillity of doing regualar backups in SQL-Server and things being less likely to crash if handling great amounts of data.

Good luck!
0
 
LVL 4

Expert Comment

by:nestorua
ID: 9758955
HI,
I think you better use '.udl' file (connection file).
Sincerely,
Nestorua.
0
 
LVL 17

Expert Comment

by:geobul
ID: 9762911
Hi,

1. I usually set CursorLocation property of ADO components to clUseServer especially for large datasets.

2. As Peter_ already said some Access SQL statements may not work on MSSQL because of different SQLs used.

3. If your app using Access DB wasn't multiuser then you have to redesign your DB tables, algorithms and GUI (perhaps) in an appropriate way (this is not that simple).

4. And I will repeat Peter_ again but use ADOqueries instead of ADOtables.

Regards, Geo
0
 
LVL 1

Author Comment

by:morgantop
ID: 9768293
thanks Peter_  and geobul,

I think you see my problem clearlly.
geobul, regarding your point 1, if i'll use clUseServer will i be able to use TDBGrids? what CANT i do normally using clUseServer?
(it supports only ForwardOnly cursor)

Im not worried about points 2+4 becouse i know i will need to change queries a bit, and i almoast always use only TASOQueries, and TADODataSet.

What i really need to know is how/what to redesign my DB tables or GUI. this is my major problem, becouse of lack of expirience... I need to get the major pointers about how my GUI and DB should look or NOT look (can i use delphi's Grids, DBControls, how to load and edit the dataset etc...)

I want to note that i use transactions in every insert,edit,delete blocks.

I will inc points larglly for a good explenation. please help me, as i never wrote delphi db app with SQL server.
thanks a milion.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 3

Accepted Solution

by:
Peter_ earned 500 total points
ID: 9771601
You need to consider some things if your application is to support multiple users:

How many simultanious users of your application would you expect having at any one time? What rights do they need?

Will all these users need to have what seams to be full access to all data at the same time? If not, then what limitations can you apply? This is what is usually called a "locking strategy" as to prevent "lost of updates" and "dirty reads". Greater limitations means easier and faster design of your application, but it also means less freedom for the users working with it. A user might have to wait while other users finnish up their work.

If your application is to serve 100:s of users and they all need access to just about everything, most of the time (like in a worldwide airline booking system), then this is an extremely complicated task. You would likely have to design some kind of "3-tire" application with a client program and a server program which handles sending and recieving data from the different clients to the SQL-Server while making sure there are no collitions.

Most of the time thankfully, things are not that complicated. In most applications, only one client at a time needs to have write access to all or a rather large part of the data at any one time. This can be controlled in a number of ways:

You can design your application in such a way that on logon (startup) the user must choose if to enter the application with "update rights" or "read only". When one user has been granted update rights, no other user can get it until this user logs off. Your application will adapt to this mode such that for a "read only" user, all update functions are disabled. This is an easy solution, much like single user environment, but it is usually not accepted by the users.

In most cases, instead of having "read only" access to the entire application you will have to listen to the users, which is the largest amount of data they can accept for "read only access" at any one time? The answer will matter to you database design as well as your GUI.

The delphi db components themselves implement some degree of locking under the hood in the sense that only one user at the time can edit one record of data. One record of data in a dbgrid for example can be put together from many different database tables though if using queries. This is one thing one has to consider if using the components. Sometimes a rather large amount of data can be locked for other users when one record is being edited. A different db table design might then work better.

I personally prefer not to use the delphi db components very often when an application can have multiple users, but rather I create my own object model. If the database model is complex (with many linked tables) or there are more then just a few users, then the components dont offer enough controll to work properly in my opinion.  Each object will then hold their value and a boolean "dirty" that indicates if it has been updated or not. I then create a "container object" that holds objects of the same type (like an "Order object" that holds a TObjectList of OrderRow objects internaly).

I link the objects to the "data property" of a listview or simular, when the user updates the text I also update my object. This way the user can update all the "order rows" in an order, then push a button "save" that executes a procedure that starts a transaction which checks each object if it has been updated and then call a stored procedure that handles the update for those who has been. This reduces network traffic.

To make sure only one user at the time can edit each order (which is my locking stategy in this case), I open up a query which ask for an orderid with update look each time the orderform is opened. If the request fails then instead I prompt a message that this order is busy or I open up the order form as "Read only". When the orderform is closed I close the query as to unlock. Here is an example from one of my applications:

function TdmData.LockOrder(Order: TOrder): Boolean;
begin
  if ( not Assigned(fADOqryLock) ) then
   fADOqryLock:=TADOQuery.Create(Self);
  try
   fADOqryLock.Connection := adocOrderDB;
   fADOqryLock.CursorLocation := clUseServer;
   fADOqryLock.CommandTimeout := 7;
   fADOqryLock.SQL.Clear;
   fADOqryLock.SQL.Add('SELECT Order_ID FROM _Order WITH (UPDLOCK, READPAST) ');
   fADOqryLock.SQL.Add('WHERE Order_ID=:Order_ID ');
   fADOqryLock.Parameters.ParamByName('Order_ID').Value := Order.ID;
   fADOqryLock.Open;
   if (NOT fADOqryLock.EOF) then
   begin
    Result := True;
   end
   else
    Result := False;
   except
    Result := False;
  end;
end;

procedure TdmData.UnlockOrder;
begin
 if (Assigned(fADOqryLock)) then
  fADOqryLock.Close;
end;

The other limitation here was that each user could only view one order at the time, but this was ok in this case. Maybe in another case you have to lock each "order row" or perhaps "all orders" if only one person does those at any one time.

To learn more about locks, browse the SQL-Server help files. There is alot to read there.

Good luck!
0
 
LVL 1

Author Comment

by:morgantop
ID: 9837859
Peter, how can i lock a record in Edit mode via ADO? both in access and sqlserver
0
 
LVL 3

Expert Comment

by:Peter_
ID: 9838426
Access database don't support locks in the same way SQL-Server does. You can't lock individual records in the same way as with SQL-server. Have never tried doing a multi-user application using access because of this, but my knowledge there is limited.

In Sql-server SQL, you use

WITH (UPDLOCK, READPAST) ' or any other access restriction (locking hint)you like. Check the SQL-Server help files or MSDN, they show which ones you can choose from and what they do.

When using the DB components, some locks are issued automatically when put in edit mode. There are some parameters you can set on the componets as to which. If you are using SQL-server, you can check which locks are issued in a certain situation by calling the stored procedure sp_lock. You may use the SQL-server Query Analyser for this.
0
 
LVL 17

Expert Comment

by:geobul
ID: 9838478
Hi,

IMHO your GUI should not depend on record locking explicitly. I use select query for retrieving some data and another update/insert/delete query (or sequence of queries) in one transaction for saving the changes. I also don't use db controls at all.

Regards, Geo
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now