morgantop
asked on
SQL-Server2000 and Delphi?
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.
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.
HI,
I think you better use '.udl' file (connection file).
Sincerely,
Nestorua.
I think you better use '.udl' file (connection file).
Sincerely,
Nestorua.
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Peter, how can i lock a record in Edit mode via ADO? both in access and sqlserver
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.
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.
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
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
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!