Click here to Skip to main content
Click here to Skip to main content

Access database using XML without SQL-XML

By , 25 Apr 2002
Rate this:
Please Sign up or sign in to vote.

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!

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Johnson Zhou
Software Developer (Senior)
China China
I'm write program from 1990. My research field is CAG,CAD and Image processing. I select C/C++, ASP, Java, XML as my usaully developing tools. Occasional , write code in Delphi and VB. I'm using Visual C++ from 1996. If you have anything unclear, e-mail to :zhou_cn123@sina.com Software Engineering and CAD is my mainly research program.
 
You also can reach me on msn: zhoujohnson@hotmail.com

Comments and Discussions

 
GeneralMy vote of 1 Pinmembersantidltp28-Oct-10 16:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140421.2 | Last Updated 26 Apr 2002
Article Copyright 2002 by Johnson Zhou
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid