Page 1 of 2
Create a file with a stored procedure in Advantage Database
Posted:
Mon Jan 16, 2017 6:18 pm
by 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 ?
Re: Create a file with a stored procedure in Advantage Database
Posted:
Thu Jan 19, 2017 8:27 am
by Antonio Linares
I just sent an email to Reinaldo who is an ADS master
Re: Create a file with a stored procedure in Advantage Database
Posted:
Thu Jan 19, 2017 8:36 am
by 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
Re: Create a file with a stored procedure in Advantage Database
Posted:
Thu Jan 19, 2017 9:46 am
by 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
Re: Create a file with a stored procedure in Advantage Database
Posted:
Thu Jan 19, 2017 10:07 am
by 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
Re: Create a file with a stored procedure in Advantage Database
Posted:
Thu Jan 19, 2017 10:40 am
by 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
Re: Create a file with a stored procedure in Advantage Database
Posted:
Thu Jan 19, 2017 11:48 am
by 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
Re: Create a file with a stored procedure in Advantage Database
Posted:
Thu Jan 19, 2017 4:09 pm
by Antonio Linares
What is the name of the called function ?
Re: Create a file with a stored procedure in Advantage Database
Posted:
Thu Jan 19, 2017 5:20 pm
by 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
Re: Create a file with a stored procedure in Advantage Database
Posted:
Thu Jan 19, 2017 5:26 pm
by Antonio Linares
Open the DLL with this utility:
https://bitbucket.org/fivetech/fivewin-contributions/downloads/peinfo.exeand 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
Re: Create a file with a stored procedure in Advantage Database
Posted:
Thu Jan 19, 2017 5:27 pm
by 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
Re: Create a file with a stored procedure in Advantage Database
Posted:
Thu Jan 19, 2017 5:34 pm
by Massimo Linossi
You're right.
Is a 32 bits Dll and the function is not exported.
Re: Create a file with a stored procedure in Advantage Database
Posted:
Thu Jan 19, 2017 5:38 pm
by Massimo Linossi
Antonio,
is it possible to make a little c program to test only with a little function that makes a messagebox ?
Re: Create a file with a stored procedure in Advantage Database
Posted:
Thu Jan 19, 2017 5:40 pm
by Antonio Linares
Try this change in the code:
__declspec(dllexport) public Int32 Get10Percent(Int32 ulConnectionID,
Int32 hConnection,
ref Int32 ulNumRowsAffected)
Re: Create a file with a stored procedure in Advantage Database
Posted:
Thu Jan 19, 2017 5:41 pm
by Antonio Linares
Yes, that is possible but lets get this working first