« Shell Script Nuggets: File Date Manipulation And Checking |
| Formatting PC »

Script Crazy: Windows Data Source Access With ADO

June 30, 2006 @ 7:23 pm · By gbyeow · Filed under Tech Talk


I have gone a little script crazy this week. This time around, I was playing with ActiveX Data Objects and accessing Windows data source objects using ADODB. Mind you, I don’t normally code for the Windows platform. But since there was need for it, who am I to argue. So here’s the quick and dirty for accessing these data source objects using ADODB via PHP, ASP, and Javascript. I also did it with VB and C++. While it doesn’t quite fit in with the ’script’ theme, I’ve included them in this quickie.

Requirements
In order to work with ADODB, you’ll need to have installed some necessary ActiveX components. Additionally, a webserver with PHP, and ASP support would be required. Since its a Windows environment and we’re working with Windows components, Microsoft’s Internet Information Services (IIS) would be the obvious choice. The added bonus for using IIS is that ADO support is automatically installed. However, you’re free to use any webserver that you’re familiar with that supports the scripts that you wish to use.

The VB and C++ samples require Microsoft Visual Studio to compile.

Suggested configuration:

  • Webserver: Internet Information Services (IIS) 5.0 or better
  • PHP 4.0 or better
  • Compiler (VB and C++): Microsoft Visual Studio

Tiny Steps
I was initially going to break things down in terms of language but decided against it since the concept and process for all the languages above are the same:

  1. Establish a connection with the Data Source Node (DSN)
  2. Run your queries
  3. Do something meaningful with the results (Display)

Instead, I’ll break it down by steps and give you the sample breakdown code for each of the languages for easy comparison. You can skip this section if you’re not interested in the breakdown, simply download the sample scripts and explore on your own.

Establish a connection with the Data Source Node (DSN)

Language Code
PHP $conn = new COM( "ADODB.Connection" )
  or die ( "Error cannot create ADODB.Connection" );
$conn->Provider = 'MSDASQL.1';
$conn->Open( "DSN=".$DSNstring.";User Id=dba;PASSWORD=dba" );
ASP set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="MSDASQL.1"
conn.Open DSNstring
Javascript var oConn, sConnectionString;
oConn = new ActiveXObject("ADODB.Connection");
sConnectionString = "Provider=MSDASQL.1;Data Source=" +
  DSNstring + ";User Id=DBA;Password=dba;";
oConn.Open(sConnectionString);
VB Dim oconn As ADODB.Connection
oconn = New ADODB.Connection
oconn.Open("Provider=MSDASQL.1;Data Source=" &
  TextBox1.Text & ";User Id=DBA;Password=dba;")
C++ ADODB::_ConnectionPtr connection;
HRESULT hr =
  connection.CreateInstance(__uuidof(ADODB::Connection));
if (FAILED(hr)) throw _com_error(hr);
connection->CursorLocation = ADODB::adUseServer;
connection->Open(
  L"Provider=MSDASQL.1;Data Source=SOMESOURCE;
    User Id=DBA;Password=dba;",
  L"", L"", ADODB::adConnectUnspecified);

Notice the similiarities between each language. The ADODB object is the same for all the languages (provided by “MSDASQL.1″). The only difference is in the syntax of the language itself and the methods used to establish the connection. The same is true for the rest of the functionality.

Run queries

Language Code
PHP $rs = new COM( "ADODB.Recordset" )
  or die ( "Error cannot create ADODB.Recordset" );
$rs->ActiveConnection = $conn;
$rs->Open($SQLstring);
ASP set rs=Server.CreateObject("ADODB.recordset")
rs.CursorLocation=3
rs.LockType=1
rs.CursorType=3
rs.Open SQLstring,conn
Javascript var oRs;
oRs = oConn.Execute(SQLstring);
VB Dim rs As ADODB.Recordset
rs.Open(TextBox2.Text, oconn)
C++ ADODB::_RecordsetPtr recordset;
hr = recordset.CreateInstance(__uuidof(ADODB::Recordset));
if (FAILED(hr)) throw _com_error(hr);
recordset->Open(SQLString, connection.GetInterfacePtr(),
  ADODB::adOpenStatic, ADODB::adLockReadOnly, ADODB::adCmdText);

Once a connection is established, running queries are as easy as passing the connection a query string. The result set is returned and stored in a result set structure which can be accessed and traversed.

Do something meaningful with the results (Display)

Language Code
PHP while (! $rs->EOF) {
  foreach ($rs->Fields as $x)
    echo($x->value);
  $rs->MoveNext();
}
ASP do until rs.EOF
  for each x in rs.fields
    response.write(x.value)
    next
  rs.MoveNext
loop
rs.Close
Javascript while (! oRs.EOF) {
  for (i = 0 ; i < oRs.Fields.Count ; i++)
    document.write(NullToNull(oRs.Fields(i).Value));
  oRs.MoveNext();
}
oRs.Close();
oRs = null;
VB If rs.RecordCount > 0 Then
  TextBox3.Text = rs.GetString(, rs.RecordCount,
    vbDoubleTab, vbNewLine)
  TextBox3.Text = TextBox3.Text & "Rows selected: " &
    rs.RecordCount()
End If
rs.Close()
C++ while(!recordset->ADOEOF) {
  _variant_t var =
    recordset->Fields->GetItem(L"COL1")->GetValue();
  std::cout « "Row-" « ++row_count « ": "
     « static_cast(_bstr_t(var.bstrVal)) « “\t”;
  var.Clear();
  var = recordset->Fields->GetItem(L”COL2″)->GetValue();
  std::cout « static_cast(_bstr_t(var.bstrVal)) « std::endl;
  recordset->MoveNext();
}
recordset->Close();

Traversing the result set is simply a matter of moving along the returned result with the built in function. You can move to the first, last, next, or previous record.

Putting it all together
The code snippets not making much sense? Don’t worry. It didn’t make much sense to me either when I first looked into the matter. Download the sample scripts and look through them. Seeing them as a whole may shed some light onto this whole mess.

Sample Source File Archive (6.42KB)

ASP and PHP took the least amount of effort to code up while Javascript was troublesome, especially since you have to manually parse the special HTML characters (if you don’t they appear as %# escape codes). The ASP, PHP, and Javascript samples all use the same input forms. VB forms are simple enough to create but C++ was a pain. As such, you’ll find that the C++ sample is hardcoded.

The example scripts provided here are generalised scripts that provide a simple user interface for accessing any data source on the Windows platform and running simple queries. Mind you that they are in no way comprehensive or complete. For me, it was a little something-something that I hacked out as a simple means for testing one of our third party software components. Maybe you’ll find more use for them. If you want to use the samples but can’t make heads or tails of it, or have some questions about obscure aspects that were not covered, feel free to ask.

P.S.: The user and passwords are hardcoded into the source. Easily remedied.

Related posts:
BoredWorkers.com Slowing down??
PHP Script: Spamming A Website - PHP Spambot
IBackup - Online Backup
Shell Script Nuggets: File Date Manipulation And Checking
Shell Script Nuggets: Simulating File Locks


« Shell Script Nuggets: File Date Manipulation And Checking |
| Formatting PC »

5 Comments »

  1. may Said,

    June 30, 2006 @ 10:33 pm

    1011 010110 001010101? 10110 101011 0101 !! 11101 010 000101 010010. 110001010110010101010…

    *blur blur*

  2. Simonsays Said,

    July 3, 2006 @ 2:35 pm

    Good show. I learned here that, at least for PHP, one could actually connect via ADO to a data source using a COM instance. I used to think that I needed ADODB.

    For production purposes, I would actually take the PHP part one step further and create a data connection class file. If I have PHP5 I would put the class’ connection strings and username and password as private static variables. (Of course this would be more or less impossible on a PHP4 machine… bad news for still some of us hosting on PHP4 platforms and wanting some serious OOP action :P) This class file would only be executable but not readable/writable via filesystem security mechanisms, so only fetched data objects/arrays are returned to calling scripts. Its user account would be limited to only this script.

    Heck, I’d go all the way put an OpenSSL tunnel between the script and the database, put the database on another machine and lock the darn co-location building up with chains, machine guns and columns of green kryptonite! Hahahahahaha!

    Boy, what a stressful day. I needed a laugh; doesn’t matter if I’m the only one laughing :|

  3. gbyeow Said,

    July 3, 2006 @ 2:53 pm

    erm… ADODB IS a COM object. ActiveX is COM in Microsoft lingo. Don’t ask me about the history and I won’t tell. Basically, it’s just one big mess. If I explained it, I would have to kill you.

  4. Simonsays Said,

    July 3, 2006 @ 4:03 pm

    Braggin laggin buhdaggin, you’re right! It call at least one COM instance. Thank you.

  5. Simonsays Said,

    July 4, 2006 @ 2:32 pm

    Hmmm, I wonder if there was some confusion in the previous comments after revisiting this article. The ADODB link in the 3rd post before this is actually a mature open-source PHP/Python DB abstraction layer, not the built-in MDAC COMponent found in Windows. (Hmmm, I could bring this point up as an antitrust case against Redmond by claiming that it is one of the few OSs with a bundled database access interface… Hey! That is an idea. I gotta get in line behind the EU’s lawsuit against M$ :P) It can even run on non-MS platforms; I have a copy running on a Linux server. By looking at this non-MS ADODB layer’s source code, yes, it does launch several instances of PHP’s COM class.

    Yes, there was and is a grand transition from DDE to OLE to COM/DCOM to COM+ to .NET… to .NET/WinFX… so does that mean you will reconsider killing me? :P

RSS feed for comments on this post · TrackBack URI

Leave a Comment