Access database using XML without SQL-XML

• Download demo project - 45.6 Kb

Introduction

I designed a program using n-tiered architecture with XML, under Windows 2000/ME. The presentation tier is the user interface which can vary according to user’s requirements. The main rule of business is laid in the business layer. And some parts of business is written using stored procedures stored in SQL or Oracle. I decided to transfer the data with XML through out the system..

How it works?

When you want to execute the stored procedure or stored functions stored in Oracle or Microsoft SQL server, send an XML segment as parameter to a COM interface. Firstly, the COM interface explains what the command is and its parameters to make ADO command object. And then execute the command. After the command is executed, translate the returned record-set and output parameters to XML. When you get the XML, you can process it as you want..

For example, in the database, there is a stored procedure nomenclature as reptq1. After it has been executed, it would return some records in a record set. You shall write the command as:

<pcommand>reptq1</pcommand>

Or, if the stored procedure reptq1 has two parameters a and b, you may write the command string in XML as :

<pcommand>reptq1<a>valuea</a><b>valueb</b></pcommand>

The interpreter program will make the command object of ADO automatically.

The following code is an example of calling the COM object.

//Make the command 
reason_str ="<pcommand>reptq1</pcommand>";
// Ask the data and return XML to be processsed
xmlstr =xmlise.GetData (reason_str);

Interface’s properties and methods

The COM interface is written in ATL. It has the following methods:

//List all columns in a specified table in a database.
HRESULT GetColumns(/*[in]*/BSTR tname,/*[out,retval]*/BSTR *out_xml);

• tname: the name of specified table input for columns to be queried
• out_xml: all column information in the table, which is in the columns information sequence.

//List all tables in specified database
HRESULT GetTables(/*[out,retval]*/ BSTR *out_xml);

• out_xml: a XML string contains all tables name in the database

//List all stored procedure in specified database
HRESULT GetProcedures(/*[out,retval]*/ BSTR *out_xml);

• out_xml: a XML string contains all names of stored procedure in database

// List all catalogs in the database
HRESULT GetCatalogs(/*[out,retval]*/ BSTR *out_xml);

// Execute the command write in XML and get the command execute results
HRESULT GetData(/*[int]*/ BSTR in_xml, /*[out,retval]*/ BSTR *out_xml);

• in_xml: a XML string can be transfer into command
• out_xml: the execution result of specified command

The following are the properties of current COM interface:

• ConnectionString: An ADO connection string of data source
• Password: The password of current connected data source
• User: A user has specified password to log on to the database

How to use it?

The following is a usage example of how to use this COM interface.

// Create a object to using COM object.
xmlise = Server.CreateObject ("DataEngine.DEng");
// Connection to old good database of SQL Server
xmlise.ConnectionString = _ 
  "PROVIDER=SQLOLEDB;DataSource=XMZY;Initial Catalog=pubs";
// Log in using  username  "SA" 
    xmlise.User  ="SA";
// the password of username is ""
    xmlise.Password ="";

// prepare the command to be executed.
    reason_str ="<pcommand>reptq1</pcommand>";

// Execute the command and get its result.
    xmlstr =xmlise.GetData (reason_str);

Conclusion

The XML is a general technology for all kinds of applications. It is very important to make light and convenient tools to process XML. It is worth!