Mega Code Archive

ADO without ADOExpress

Title: ADO without ADOExpress Question: In transitioning from VB, I missed the functionality of ADO. So, I set about to use ADO from Delphi, but discovered that Borland offers it only in ADOExpress. So, I learned how to use ADO with the "raw" COM objects. Here's a quick guide. Answer: If you have never used ADO, you're missing out. Not to say that ADO is the best solution out there, but it certainly is simple and convenient. If you don't have ADOExpress, you might be worried that using ADO is either hard or even impossible. Nothing can be further from the truth. This is a discussion of my exploration of ADO, coming from a Visual Basic background. I am by no means an expert on the subject and I offer this article to help others who are struggling to understand how to leverage this important technology in their application. I use these items in my webclasses to facilitate data transfer and allow use of COM technologies for MTS support. To start, you will need to import the ADO type library. Import the "Microsoft ActiveX Data Objects..." library. You may see a few in your list. I suggest the 2.1 revision. There is a "... Recordset" library. This is useful for programs that will use a recordset, but don't need all of the other functionality provided by ADO. Then, you need a connection. A connection is an object that tells ADO how to reach your datasource. This will be your big limiting factor. Not everything works with ADO. If you are using an older ODBC driver, you probably will not be able to reach your datasource through ADO. If you are using SQL or Oracle, you can have faster access than through ODBC. It all depends on what you are working with. You can declare a connection like the following: VAR objConnection : _Connection; BEGIN objConnection := coConnection.Create; objConnection.Open( , , , ); Here is probably the hardest part of using ADO. However, if you want to do this easily, you can simply create a DSN in your ODBC manager and then set your connection string = "DSN=MyDSNName". However, that will force your application to run through the ODBC layer, which lies on top of the OLE-DB layer. For more direct access, here is a sample connect string to a SQL Server 7 backend. "Provider=SQLOLEDB;Connect Timeout=7;SERVER=MYSERVER;UID=MYUSER;PWD=MYPASS;DATABASE=MYDB" This runs through the OLE-DB layer which will make your application slightly faster. If you supply UID and PWD, then you don't really need to supply and , but I generally do for completeness. Here is where we come to the first annoyance of using ADO in Delphi. The type library imports the Options parameter as Integer. However, the value for "unspecified" for the Options is $FFFFFFFF. This generates a compiler warning in Delphi of "constant expression violates bounds" if you use the enumerated constant. So, I throw -1 in there manually, since that's what it wants anyway. Then, you have a choice. You can drive your results with a command object, or a recordset. I personally skip the command object, as it tends to be more trouble than it's worth. However, if you're performing SQL that doesn't return a recordset, you're going to need to use it. VAR objCommand : _Command; vntRecordsAffects : OLEVARIANT; BEGIN objCommand := coCommand.Create; objCommand.CommandType := adCmdText; objCommand.CommandText := ''; objCommand.Execute( vntRecordsAffects, EmptyParam, -1 ); ... I think the only thing that needs explanation is the last line. RecordsAffected will tell you how many records were touched by your query. The usefulness of this is dependant on your query. EmptyParam is a value defined by Delphi. It is used to go in as a parameter you don't want to supply. In VB you simply leave off the parameter. What happens in the background is that VB creates a variant with a type of vtError and a value of DISP_E_PARAMNOTFOUND. Delphi requires you to supply every parameter, so you can use EmptyParam as you would in VB by ignoring the parameter. The real meat here, though, is the recordset. VAR objRecordset : _Recordset; BEGIN objRecordset := coRecordset.Create; objRecordset.CursorLocation := adUseClient; objRecordset.Open( '', objConnection, adOpenForwardOnly, adLockPessimistic, -1 ); CursorLocation is another enumerated type. Basically, adUseClient forces the database cursor to be used in the client space. In the case of my web-apps, this would be IIS. Why do we use this? Good question. I don't have the solution as to why you would use one mode over another. It all depends heavily on your application. I showed adUseClient as it's a safe value to use for beginners. The Open method requires your SQL command as the first parameter, a Connection object as the second parameter (make sure you have .Open-ed it!) and some more options. CursorType : adOpenForwardOnly. Much like as it sounds. The results of this query may only be iterated through in one direction. This makes the resulting query faster. Good option to use unless you have a specific reason to jump around in the result set. LockType : adLockPessimistic. Again, very dependent on your application. adLockPessimistic is a nice safe value to use that will cause minimum damage to your DB if you mess up. *8) Now, how is this all useful to you? Well, these recordset objects can be passed around COM objects. I have created all sorts of ActiveX librarys containing COM objects that do the work of querying the database. They just return me a recordset. (To get an interface able to return recordsets, just go to your interface, go to the Uses tab, right click, select show all interfaces, then add the ADO type library.) This can be incredibly helpful in creating re-usable components for your applications. They have the added bonus of working in any language that understands COM. TIP - If you are going to use these objects in a web-class, BE SURE to set the threading model to "Both". Otherwise nothing will work properly. Well, I will write more on how to use ADO for beginners if the demand arises. It's all straightforward and works rather well. I look forward to questions and comments.