Exporting to XML

This months tech-tip is about exporting Advantage data to an XML file. It describes an Advantage Extended Procedure (AEP) which will export the table to a specified file. Fortunately both Visual Studio and Delphi have libraries which provide this functionality.

The example AEP only contains one function called TableToXML. I want to expand on the example a bit and add a second function called StmtToXML which will accept a generic SQL statement and export the resultset. The mechanism is the same since we will once again use the DataSet (VS) to write the XML file.

Since we are allowing the user to input their own SQL statement we must consider the possibility of an SQL Injection Attack. This is where someone sends in a string which may run multiple statements. To thwart this possibility we simply have to check for a ";" and reject any statement which includes one.

   1: // Check for SQL injection

   2: if (sStatement.Contains(";"))

   3: {

   4:     IDbCommand oErrCommand = oStateInfo.DataConn.CreateCommand();

   5:     oErrCommand.CommandText = "INSERT INTO __error VALUES( 1001, 

   6:                               'SQL scripts are invalid for this function' )";

   7:     oErrCommand.ExecuteNonQuery();

   8: }

Next we need to make sure that the SQL statement will return rows. Therefore only a SELECT or an EXECUTE PROCEDURE statement will be allowed. Data Manipulation Language (DML) statements such as INSERT, UPDATE, DELETE should be rejected by the AEP. So our code will do a simple check of the statement for the proper syntax. If the statement begins with SELECT or EXECUTE PROCEDURE then run the statement and export the results. Otherwise return an error.

   1: // Ensure the SQL statement is a SELECT or EXECUTE PROCEDURE statement

   2: if (sStatement.Substring(0, 6).ToUpper() == "SELECT" ||

   3:     sStatement.Substring(0, 17).ToUpper() == "EXECUTE PROCEDURE")

   4: {

   5:     // Run specified SQL statement

   6:     oAdapter = new Advantage.Data.Provider.AdsDataAdapter(sStatement, oStateInfo.DataConn);

   7:     oAdapter.Fill(oDataSet);

   8:  

   9:     // Export the result to XML

  10:     oDataSet.WriteXml(sDestination.Trim() + "\\" + sFileName.Trim() + ".xml", XmlWriteMode.WriteSchema);

  11: }

  12: else

  13: {

  14:     IDbCommand oErrCommand = oStateInfo.DataConn.CreateCommand();

  15:     oErrCommand.CommandText = "INSERT INTO __error VALUES( 1002, 'SQL statements must start with SELECT or EXECUTE PROCEDURE' )";

  16:     oErrCommand.ExecuteNonQuery();

  17: }

Now that the code is finished the Stored Procedure needs to be added to the dictionary. StmtToXML takes three input paramters; statement, filepath and XMLfilename. A screenshot of the Stored Procedure configuration is below.

The last step is to test our new Stored Procedure. Use an EXECUTE PROCEDURE call to invoke the new procedure.

EXECUTE PROCEDURE sp_StmtToXML( 'SELECT * FROM Table', 'C:\XML', 'Test1' )

The Visual Studio Source code for this Stored Procedure can be downloaded from the Advantage DevZone in Code Central. The project is named "Exporting  Advantage Data as XML" and is filed under C#/VB.NET Stored Procedures. Version 1.1 includes updated Visual Studio code, the Delphi sample has not been updated.