Easier way to work with a database in Delphi

IT79637
IT79637 used Ask the Experts™
on
Hi Experts,

I have written an automation server used to display scanned documents in Delphi 7.  The host system, such as an accounts payable system, interacts the server via method calls using javascript.

The server has the database code to maintain the document indexes. I want to expand the database operations to interact with the major RDBMS: MS SQL Server, Oracle and DB2. If I have left out a major DBMS, please feel free to add it to my list.

For MySQL, I'm using micorOLAPs controls.  I like the fact that I don't have to have a client side DBMS software installed.

What I don't like is that I feel the code is cumbersome to write for SQL INSERT, UPDATE, DELETE operations.  Additionally, I don't want to get trapped in the expensive upgrade cycle with several components and I don't really want to expend the energy learning several dbms component packages.  I'd rather concentrate my energies on the application!

Questions:
1.  Is there a component set in Delphi 7 that allows me to universally access the major databases?
2.  Would upgrading to the latest Delphi solve this problem?
3.  Would switching to MS VB .NET give me tools to address this issue?
4.  Cringe, Cringe, Cringe!!! What about calling a server side PHP program to do the database operations?  The code is incredibly easy to write even with transaction processing.
5.  Use stored procedures?

What would you recommend/do?

I've made the question a 500 points because there are several questions.  If you have a "sure fire" answer to question 1, then the other questions become mute.

Thanks for taking the time to go through all of this!

Below is a section of my code to illustrate my comment about the dbms controls being cumbersome...

procedure UpdateDataBase();
var    i : integer;
begin
  Form1.Edit6.Text := 'Update database.';
  i := 1;
  StartTransaction1(); { START TRANSACTION for one batch on ctl &  dtl tables and scan_index table }
 
  with Form1.mySQLDirectQuery1 do begin  { SELECT values from batch_scan_dtl }
    try
      dbAction := 'SELECT batch_scan_dtl & loop through. ';
      strSQL   := 'SELECT * FROM batch_scan_dtl WHERE batch_no = ' +
        AnsiQuotedStr(ProcessingBatch, oneQ) + ' ORDER BY doc_id;';
      //ShowMessage('SELECT from batch_scan_dtl: '+ strSQL);
      SQL.Clear;
      SQL.Add(strSQL);
      Open();
      //iCount := RecordCount;
      //ShowMessage('Record Count: ' + IntToStr(iCount));
      First();
      while not Eof do begin  {inner loop to process documents in a batch_scan_dtl }
        dbAction       := 'LOOP THROUGH batch_scn_dtl table. ';
        batchno        := AnsiQuotedStr(arBatchDtl[i,1], oneQ);    // batch_no
        docid          := AnsiQuotedStr(arBatchDtl[i,2], oneQ);    // doc_id
        docid_seq      := AnsiQuotedStr(arBatchDtl[i,3], oneQ);    // doc_id_seq
        scandt         := AnsiQuotedStr(arBatchDtl[i,4], oneQ);    // scan_dt
        companycd      := AnsiQuotedStr(arBatchDtl[i,5], oneQ);    // company_cd
        invtype        := AnsiQuotedStr(arBatchDtl[i,6], oneQ);    // inv_type
        priority       := AnsiQuotedStr(arBatchDtl[i,7], oneQ);    // priority
        doctype        := AnsiQuotedStr(arBatchDtl[i,8], oneQ);    // doc_type
        docclass       := AnsiQuotedStr(arBatchDtl[i,9], oneQ);    // doc_class
        docsecurity    := AnsiQuotedStr(arBatchDtl[i,10], oneQ);   // doc_security
        scanloc        := AnsiQuotedStr(arBatchDtl[i,11], oneQ);   // scan_loc
        appl           := AnsiQuotedStr(arBatchDtl[i,12], oneQ);   // application
        batchname      := AnsiQuotedStr(arBatchDtl[i,13], oneQ);   // batch_name
        optdiskno      := AnsiQuotedStr(arBatchDtl[i,14], oneQ);   // optical_disk_no
        ocr_vendor_id  := AnsiQuotedStr(arBatchDtl[i,16], oneQ);   // Vendor No
        ocr_invoice_no := AnsiQuotedStr(arBatchDtl[i,17], oneQ);   // Invoice No
        ocr_invoice_dt := AnsiQuotedStr(arBatchDtl[i,18], oneQ);   // Invoice Date
        ocr_po         := AnsiQuotedStr(arBatchDtl[i,19], oneQ);   // Purchase Order
        ocr_subtotal   := AnsiQuotedStr(arBatchDtl[i,20], oneQ);   // SubTotal
        ocr_tax        := AnsiQuotedStr(arBatchDtl[i,21], oneQ);   // Tax
        ocr_shipping   := AnsiQuotedStr(arBatchDtl[i,22], oneQ);   // Shipping
        ocr_deposit    := AnsiQuotedStr(arBatchDtl[i,23], oneQ);   // Deposit
        ocr_total      := AnsiQuotedStr(arBatchDtl[i,24], oneQ);   // Deposit
        ocr_bal_due    := AnsiQuotedStr(arBatchDtl[i,25], oneQ);   // Balance Due
        ocr_duedt      := AnsiQuotedStr(arBatchDtl[i,26], oneQ);   // Due Date
        InsertIntoScanIndex();
        Inc(i);
        Form1.MySQLDirectQuery1.Next();
      end;
      Form1.MySQLDirectQuery1.Close();
 
      DeleteBatchScanCTL();
      DeleteBatchScanDTL();
      CommitBatch();
      Form1.MySQLDirectQuery1.Close();
      except on e:EDatabaseError do begin
        UserLogin := GetUserFromWindows; ComputerName := GetComputerNetName;
        bResult := WriteErrorLog('LOOP through batch_scn_dtl table');
        ShowMessage(dbAction + 'Exception: DatabaseError: ' + 'Message: ' + e.Message);
        Exit;
      end;
    end;
  end;
end;
 
procedure InsertIntoScanIndex();
begin
    Form1.Edit6.Text := 'Insert into scan_index table.';
  with Form1.mySQLDatabase1 do begin
    try
      sID :=  AnsiQuotedStr('', oneQ);
      strSQL := 'INSERT INTO scan_index VALUES (' +
         sID         + C1 +
         docid       + C1 +
         docid_seq   + C1 +
         scandt      + C1 +
         companycd   + C1 +
         invtype     + C1 +
         priority    + C1 +
         doctype     + C1 +
         docclass    + C1 +
         docsecurity + C1 +
         scanloc     + C1 +
         appl        + C1 +
         batchname   + C1 +
         AnsiQuotedStr('N', oneQ) + C1 +   // busy
         optdiskno   + C1 +
         AnsiQuotedStr('z000000', oneQ) + C1 + //user_id
         AnsiQuotedStr('0000-00-00 00:00:00', oneQ) + C1 +
         batchno         + C1 +
         ocr_vendor_id   + C1 +
         ocr_invoice_no  + C1 +
         ocr_invoice_dt  + C1 +
         ocr_po          + C1 +
         ocr_subtotal    + C1 +
         ocr_tax         + C1 +
         ocr_shipping    + C1 +
         ocr_deposit     + C1 +
         ocr_total       + C1 +
         ocr_bal_due     + C1 +
         ocr_duedt       + ');';
      //ShowMessage(strSQL);
      Execute(strSQL);
      except on e:EmySQLDatabaseError do begin
        UserLogin := GetUserFromWindows; ComputerName := GetComputerNetName;
        bResult := WriteErrorLog('INSERT INTO scan_index failed');
        ShowMessage(dbAction +   'Exception: DatabaseError: ' +  'Message: ' + e.Message);
        Exit;
      end;
    end;
  end;
end;
 
procedure DeleteBatchScanCTL();
begin
Form1.Edit6.Text := 'Extracting OCR information.';
with Form1.mySQLDatabase1 do
  begin
    try
      dbAction := 'Delete from batch scan control. Batch no: ' + ProcessingBatch + '. ';
      strSQL := 'DELETE FROM batch_scan_ctl WHERE batch_no = ' +
        AnsiQuotedStr(ProcessingBatch, oneQ) + ';' ;
      //ShowMessage(strSQL);
      Execute(strSQL);
      except on e:EDatabaseError do begin
        UserLogin := GetUserFromWindows; ComputerName := GetComputerNetName;
        bResult := WriteErrorLog('Delete from batch scan control. Batch no: ' + ProcessingBatch + '. ');
        ShowMessage(dbAction +   'Exception: DatabaseError: ' +  'Message: ' + e.Message);
        Exit;
      end;
    end;
  end;
end;
 
procedure DeleteBatchScanDTL();
begin
 Form1.Edit6.Text := 'Delete batch detail information.';
with Form1.mySQLDatabase1 do
  begin
    try
      dbAction := 'Delete from batch scan detail. Batch no:  ' + ProcessingBatch + '.  ';
      strSQL := 'DELETE FROM batch_scan_dtl WHERE batch_no = ' +
        AnsiQuotedStr(ProcessingBatch, oneQ) + ';';
      //ShowMessage(strSQL);
      Execute(strSQL);
      except on e:EDatabaseError do begin
        UserLogin := GetUserFromWindows; ComputerName := GetComputerNetName;
        bResult := WriteErrorLog('Delete from batch scan detail. Batch no:  ' + ProcessingBatch + '.  ');
        ShowMessage(dbAction +   'Exception: DatabaseError: ' +  'Message: ' + e.Message);
        Exit;
      end;
    end;
  end;
end;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Hi.

I haven't taken the time to properly digest everything in your question - perhaps you could simplify it - I'm one of those that would rather your question be 2,000 words long but everything is explained in a logical, linear fashion, as opposed to cramming everything into 100 words and hoping everyone will digest it.

Anyways,
here's my 2c:
With a component set like ADO, all you need to do is provide the driver and the connection string and your programming should remain the same. But alas there are always intracacies to each RDBMS, for example some require you to auto-increment fields, others have slightly different implementations of the SQL standard, etc.
What you could end up doing is dealing with this behaviour in an OOP fashion, i.e.
When connecting to a database, create a function/procedure like this poor coding:

function DatabaseConnect(DatabaseType: string) ;
begin
  if DataBaseType = 'ORACLE' then
    ..  //connect like THIS
  if DataBaseType = 'ACCESS'' then
    .. //connect like THIS
end;

and do functions to handle Connecting, Opening, Inserting, Disconnecting, etc.

Then your calling code will be something like this
DatabaseConnect('Oracle');
DatabaseOpen('Oracle');

You get what I'm saying?
Commented:
Note: Okay so my function didn't return anything, but I think you get what I'm saying.

I do know that there are database components out there that do claim to handle all these databases.
You could probably find them with a bit of googling, but a recommendation from an Expert would be nice,
let's see what others say.

I hope this helped
Questions:
1.  Is there a component set in Delphi 7 that allows me to universally access the major databases?
Answer:
The TADO components are pertty much universal with regard to the major DBMS.
2.  Would upgrading to the latest Delphi solve this problem?
Answer:
Only in the sense that you would have the latest version of things.  As for the TADO components, they were pretty complete in D7.

3.  Would switching to MS VB .NET give me tools to address this issue?
Answer:
IMHO, no but it would increase your learning curve and, if you are any good at Delphi, your frustration level. ;-)

4.  Cringe, Cringe, Cringe!!! What about calling a server side PHP program to do the database operations?  The code is incredibly easy to write even with transaction processing.
Answer:
Do you have the Zend encoding software?  If not, how do you plan to keep your PHP secure?

5.  Use stored procedures?
Answer: Well, that measn that you have to have a Stored Proc ready for whichever DBMS you are going to be accessing.  SP's are not universal and have to be created on the DBMS.  There are also quirks as to how SP's are created and access in each DBMS.

What would you recommend/do?
Answer:
I would consider upgrading to D2009, just so that you have better support and the latest and greatest set of components.  Then I would work with the TADO components.  I would also make sure that I used a DataModule to handle the TADOConnection and TADOQuery components and the functions/procedures that actually set them up and executed them.  
If I were truly going to prepare for accessing any DBMS, I would then set up a SQL Server 2005 (or 2008) Express database in which I placed the connection strings and queries in tables with a key that let me select them based upon whcih DBMS I was going to be accessing.  I would provide the user with the ability to select the target DBMS and then my app would load the connection string(s) and SQL for the queries based upon that selection.
You could probably also store the code to create some stored procedures in that SSExpress database so that you could go that route.  I really prefer the use of SP's whenever possible becuase they can increase the security level of accessing the database.  However, as I mentioned, there are some issues with trying to build SP's for every possible DBMS.
Contrary to popular opinion, the connection strings and SQL for the queries varies from DBMS to DBMS, especially if you want the SQL to do anything that involves the "extensions" of each DBMS.
Oh, yeah, you missed a really cool DBMS: Teradata.  

Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Geert GOracle dba
Top Expert 2009
Commented:
If you have a "sure fire" answer to question 1
these are working on it:
http://www.devart.com/unidac/

unfortunately
designing a app which works on any database is: A BIG MYTH !!!

sorry to destroy the dream, unfortunately it's true

i have experience with a dozen database types
frankly the better the database, the more i go for it
i'm now on Oracle, i don't think there is anything much better at the moment

2.  Would upgrading to the latest Delphi solve this problem?
nope

3.  Would switching to MS VB .NET give me tools to address this issue?
this is a joke yeah ? nope

4.  Cringe, Cringe, Cringe!!! What about calling a server side PHP program to do the database operations?  The code is incredibly easy to write even with transaction processing.
mysql and php is the best more or less free internet solution

5.  Use stored procedures?
by all means (less trafic over the network)

looking at your code, whatever for are you using forms within procedural code ?
you should change it the other way round
define your procedures within the forms, or don't use forms (like a service application)
you can create queries without the need of forms


 
Top Expert 2010
Commented:
basically,different databases use different SQL so you can't have 'one-for-all'
application.I prefer MS SQL Server because it sticks to the original  ANSI/ISO SQL standard (well,almost...).

Author

Commented:
Unidac sounds very interesting.  I hope it is performs exactly as it says.  This would save a tremondeous amount of coding and testing for each change to the db routines.

Upgrading to the latest delphi is also a good idea to get the latest technology it offers.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial