Create a file with a stored procedure in Advantage Database

[Massimo Linossi]

Hi to all.
Is it possible, with Advantage Database, to create a txt file when a record is added inside a table, using the stored procedures ?
I'm trying to use this system because creating a file I can detect this event on another machine for
launching some other programs.
Thanks a lot

P.S. Is someone able to enter in the developer zone of Advantage Database ?

[Antonio Linares]

I just sent an email to Reinaldo who is an ADS master

[Massimo Linossi]

Hi Antonio.
Reading some ADS manuals, I saw that there is a possibility to launch a DLL with some functions inside.
How can I make a 64 bits DLL ? What software do I need ? It is possible with harbour ?
Thanks a lot.
Massimo

[Antonio Linares]

Whats the purpouse of such DLL ?

Are there examples ?

Building a 64 bits DLL is easy. The problem is to know what to put inside it

[Massimo Linossi]

Hi Antonio.
The purpose is to connect this Dll to the Stored Procedures of Advantage, so when a record is added or modified
the engine of Advantage launch the function that i inside the DLL.
I want to make a little function that receives 3 string parameters and combining these strings
creates a file in a special folder.

The only example that I found is this :

Code: Select all  Expand view

using System;
using System.Data;
using Advantage.Data.Provider;
using System.Collections;

namespace AEPDemoCS
{

  public class aep_procedures
  {
    // Declare a collection of objects that can be used to store state information
    private static Hashtable colClientInfo = new Hashtable();

    public aep_procedures()
    {
    }


    // This is the AEP startup function.  This is called the first time an Advantage
    // connection calls a procedure in this AEP module. It is called once for each
    // Advantage connection that calls a procedure in this module.
    // Do any per-connection initialization here. Use the ulConnectionID as a
    // unique identifier for each connection.
    public Int32 Startup(Int32 ulConnectionID,
                          Int64 hConnection)
    {
      StateInfo oStateInfo = new StateInfo("ConnectionHandle=" + hConnection.ToString());

      try
      {
        // Store the connection ID
        oStateInfo.ConnID = ulConnectionID;

        // Open the connection, which was passed the active ACE connection hConnection
        oStateInfo.DataConn.Open();

        // Place this client state info into our collection. Use a synclock, as there might be other
        // threads accessing colClientInfo right now
        lock (colClientInfo)
        {
          colClientInfo.Add(ulConnectionID, oStateInfo);
        }
      }
      catch (Exception e)
      {
        IDbCommand oErrCommand = oStateInfo.DataConn.CreateCommand();
        // Handle any exceptions here. Errors can be returned by placing a
        // row into the __error table.
        oErrCommand.CommandText = "INSERT INTO __error VALUES( 1, '" + e.Message + "' )";
        oErrCommand.ExecuteNonQuery();
      }

      // Result is currently reserved and not used. Always return zero.
      return 0;

    }   // Startup


    // This is the AEP shutdown function.  This is called once for each Advantage
    // connection that has called a procedure in this module, and is called when
    // the connection is terminating.
    // The prototype must be exactly as it is in the example.
    // Do your per-connection clean-up here. Use the ulConnectionID as a
    // unique identifier for each connection.
    public Int32 Shutdown(Int32 ulConnectionID,
                           Int64 hConnection)
    {
      StateInfo oStateInfo;

      // Get this clients state information before doing anything
      lock (colClientInfo)
      {
        oStateInfo = (StateInfo)(colClientInfo[ulConnectionID]);
        oStateInfo.DataConn.Close();
        // free the clients state information
        colClientInfo.Remove(ulConnectionID);
      }

      return 0;

    }   // Shutdown



public Int32 Get10Percent(Int32 ulConnectionID,
  Int32 hConnection,
  ref Int32 ulNumRowsAffected)
{
  StateInfo oStateInfo;
  AdsCommand oCommand;
  Int32  custID;
  Int32 recCount;

  lock( colClientInfo )
    oStateInfo = (StateInfo)(colClientInfo[ulConnectionID]);

  try
  {      
    oCommand = oStateInfo.DataConn.CreateCommand();
    oCommand.CommandText = "SELECT CustID FROM __input";
    custID = Convert.ToInt32(oCommand.ExecuteScalar());

    oCommand.CommandText = "SELECT Count(*) " +
                           "FROM INVOICE "+
                           "WHERE [Customer ID] = :cust;";
    oCommand.Parameters.Add("cust", System.Data.DbType.Int32).Value = custID;
    recCount = Convert.ToInt32(oCommand.ExecuteScalar());
 
    if (recCount < 10)
    {
      oCommand.CommandText = "INSERT INTO __error "+
                             "VALUES ( 2501, 'There are less than 10 " +
                             "records for " + custID.ToString();
      oCommand.ExecuteNonQuery();
      return 0;
    }

    oCommand.CommandText = "INSERT INTO __output " +
                           "SELECT TOP 10 PERCENT [Invoice No] " +
                           "FROM INVOICE WHERE [Customer ID] = " + custID.ToString();
    oCommand.ExecuteNonQuery();
  }
  catch( Exception e )
  {
    IDbCommand  oErrCommand =
      oStateInfo.DataConn.CreateCommand();
    oErrCommand.CommandText = "INSERT INTO __error "+
      "VALUES( 1, '" + e.Message + "' )";
    oErrCommand.ExecuteNonQuery();
  }

  return 0;

}   // Get10Percent



    // Do not change or remove this function. It is used internally by the Advantage server
    public Int32 GetInterfaceVersion()
    {
      return 3;
    }   // GetInterfaceVersion

  }   // aep_procedures


  public class StateInfo
  {
    public Int32 ConnID;
    public AdsConnection DataConn;

    public StateInfo(String ConnString)
    {
      DataConn = new AdsConnection(ConnString);
    }
  }


}   // AEPDemoCS

 

[Antonio Linares]

You can build the DLL from that C# (I guess) source doing this:

c:\Windows\Microsoft.NET\Framework\v3.5\csc /out:mydll.dll /target:library example.cs
c:\Windows\Microsoft.NET\Framework\v2.0.50727\regasm mydll.dll /tlb

[Massimo Linossi]

Hi.
I successfully build the dll and registered. Now the Advantage Database is seeing the dll but when
I launch the Stored Procedure I receive a message that is not finding the called function.
Really strange

[Antonio Linares]

What is the name of the called function ?

[Massimo Linossi]

The function that I want to call is Get10Percent.
Only a curiosity. Is it possible to use Harbour or Xharbour 64 ?
Thanks
massimo

[Antonio Linares]

Open the DLL with this utility:

https://bitbucket.org/fivetech/fivewin-contributions/downloads/peinfo.exe

and check if such function name appears as exported

Yes, we could use Harbour/xHarbour to build the DLL, but first lets try to get it working as it is

[Antonio Linares]

Also check if the DLL is 32 bits (my guess is that it is 32 bits)

in that case we can not use Harbour/xHarbour 64 bits

[Massimo Linossi]

You're right.
Is a 32 bits Dll and the function is not exported.

[Massimo Linossi]

Antonio,
is it possible to make a little c program to test only with a little function that makes a messagebox ?

[Antonio Linares]

Try this change in the code:

__declspec(dllexport) public Int32 Get10Percent(Int32 ulConnectionID,
Int32 hConnection,
ref Int32 ulNumRowsAffected)

[Antonio Linares]

Yes, that is possible but lets get this working first