para aquellos que estéis interesados en atacar al ERP Infor BaaN /LN lo podéis hacer a través de OLE Automation.
Un pequeño ejemplo:
- Code: Select all Expand view
#include "FiveWin.ch"
FUNCTION Main()
LOCAL oBaan4Obj
LOCAL cComando
LOCAL oError, Retorno
Local proyecto := "0783-01"
Local B_dll := "otpptpdllcargah"
Local B_function := "tpptpdllcargah.carga_control_horas(" + Chr(34) + proyecto + Chr(34) + ")"
SET EPOCH TO 1920
SET DATE FORMAT "dd/mm/yyyy"
SET WRAP ON
Retorno := .F.
TRY
// Creamos el objeto OLE Automation
oBaan4Obj:= CreateObject("Baan.Application.planif")
oBaan4Obj:ParseExecFunction(B_dll,B_function)
oBaan4Obj:Quit()
oBaan4Obj := NIL
Retorno:=.T.
// Si no conectamos con BaaN, capturamos el error (oErr) y mostramos mensaje
CATCH oError
//MSGAlert("Error : No se tiene acceso al servidor de BaaN " + CRLF + "por favor verifique conexion a red o que el servidor este funcionando", "Error: " + oError:Operation + " -> " + oError:Description )
Retorno:=.F.
END
// Comprobamos conexion a BaaN
If !Retorno
//MsgInfo("Lo sentimos, no hay conexión a BaaN")
Return Nil
Else
// MsgInfo("Conectado a BaaN")
Endif
hb_gcAll() // Recolector de basura de Harbour
SET RESOURCES TO
ResAllFree()
RETURN NIL
La información que disponemos de OLE Automation desde Infor (BaaN / LN) es la siguiente:
The BAAN application exposes just one object, which is the application object.
In Visual Basic this object can be created with:
Set Baan4Obj = CreateObject("Baan4.Application")
This will start BAAN, with the BW user interface, in an invisible way, i.e. only the Option Dialog icon will be displayed.
OLE Automation objects, Baan4Obj is one, can have methods and properties.
- A method is a function that can be called to perform a certain action.
- A property is an attribute that has a value. Property values can be set and/or retrieved
The BAAN application object has the following methods:
- ParseExecFunction "dllname", "functioncall"
- Quit
The BAAN application object has the following properties:
Name Type R/W
Timeout long R/W
FunctionCall string R/W
Error long R
ReturnValue string R
These methods and properties allow Visual Basic programmers to call any function in any dll.
For example:
Baan4Obj.Timeout = 10 ' 10 seconds timeout
Baan4Obj.ParseExecFunction "mydll", "myfunction(arg1, arg2)"
If Baan4Obj.Error <> 0 Then MsgBox("An error occurred")
MsgBox("The return value is " + Baan4Obj.ReturnValue)
Note:
1.The property Timeout is used to prevent blocking on erroneous calls.
2.Note that arguments to automation methods (ParseExecFunction) are not surrounded by brackets.
Note also how arguments are passed to myfunction. If arguments are passed by reference they might
be changed by the function call, therefore the property FunctionCall will contain the modified arguments
after the call.
3. The property Error contains the return status of the ParseExecFunction call while the property
ReturnValue is the actual return value of the dll function.
4. If the DLL function returns a long this will be converted to a string in ReturnValue.
5. Possible values for Error are:
0 success
-1 unknown dll name
-2 unknown function name
-3 syntax error in function call
-10 timer expired
To end an automation session the following example shows how to quit the BAAN application.
Baan4Obj.Quit
Set Baan4Obj = Nothing
3 Limitations
Following are some limitations when using the OLE Automation interface:
• Due to the limitation in the 3GL function parse_and_exec_function, it will not be possible to supply arrays as arguments to a DLL function.
• DLL functions with a variable number of arguments or optional arguments are not supported.
• When a string is passed by reference, the result cannot become longer than the input string. So when a string must be filled then it should be initialized with enough spaces.
• The maximum size of the FunctionCall string is 4Kb (bshell limit).
• The maximum size of the ReturnValue string is 4Kb (bshell limit).
The following simple example will fill an MS-Excel spreadsheet with the names of all BAAN users.
To implement this, we have to do two things:
1. Implement DLL function(s) to obtain the names of the users from the database.
2. Implement some Visual Basic code in MS-Excel.
DLL function example
The DLL below (called demodll) contains two functions to retrieve the names of the users from the database. One function to initiate the query and get the first name and another function to get the next name. When the last record has been returned an empty string will be returned.
table tttaad200
long sql_id
function extern string get_first_user()
{
string sql_query(512)
string user(512)
switch.to.company(0)
sql_query = "select ttaad200.user from ttaad200"
sql_id = sql.parse(sql_query)
sql.exec(sql_id)
e = sql.fetch(sql_id)
if e = 0
then user = ttaad200.user
else user = ""
endif
return( user )
}
function extern string get_next_user()
{
string user(512)
e = sql.fetch(sql_id)
if e = 0
then user = ttaad200.user
else user = ""
endif
return( user )
}
Visual Basic example
MS-Excel contains Visual Basic for Applications (VBA) that allows for scripting to control the Excel application, but also to control other applications through OLE Automation. The following is the Visual Basic source that will call the DLL functions mentioned above and display the results in a spreadsheet. The subroutine GetBaan4Users is an MS-Excel macro which can be invoked through user interface controls on the spreadsheet (buttons or menus).
Dim user As String
Dim Baan4Object As Object
Sub GetBaan4Users()
On Error GoTo CannotCreateBaan4
' run Baan4
Set Baan4Object = CreateObject("Baan4.Application")
On Error GoTo Baan4AutomationError
' get first user
Baan4Object.ParseExecFunction "demodll", "get_first_user()"
If (Baan4Object.Error <> 0) Then GoTo Baan4AutomationError
user = Baan4Object.ReturnValue
Row = 1
Column = 1
While (user <> "")
' fill the spreadsheet
Worksheets("Main Sheet").Cells(Row, Column) = user
Row = Row + 1
If Row > 15 Then
Row = 1
Column = Column + 2
End If
' get next user
Baan4Object.ParseExecFunction "demodll", "get_next_user()"
If (Baan4Object.Error <> 0) Then GoTo Baan4AutomationError
user = Baan4Object.ReturnValue
Wend
' exit Baan4
Baan4Object.Quit
Set Baan4Object = Nothing
Exit Sub
' error handling
CannotCreateBaan4:
MsgBox "Unable to start Baan4"
Exit Sub
Baan4AutomationError:
MsgBox "Baan4 automation error"
Baan4Object.Quit
Set Baan4Object = Nothing
Exit Sub
End Sub
Example: Using Baan IV SQL
The following example will fill an MS-Excel spreadsheet using BAAN SQL. To implement this a DLL, named ottdllsql_query, has been developed to parse and execute the query. To receive more information about this library, enter the following command: *> bic_info6.1 -eu ottdllsql_query [SHbic_info6.1 -eu ottdllsql_query]
DLL information
The DLL 'ottdllsql_query' contains:
• functions to parse, execute and stop a query
- olesql_parse
- olesql_fetch
- olesql_break
- olesql_close
• functions to import query results into Visual Basic
- olesql_getstring
- olesql_getint
- etc
• a function to convert an Easy SQL query to a string
- easysql_to_olesql
• declaration of external variables, as
- olesql_count
- olesql_float0, olesql_float1 ... olesql_float9
(double variables)
- olesql_int0, olesql_int1 ... olesql_int9 (long variables)
In the SAMPLES directory of BW an MS-Excel example is installed (BAAN4.XLS). This spreadsheet contains the following macros:
• The first macro, named GetBaanUsers, will show a number of Baan IV users on the spreadsheet (Worksheet 'Users'). Simply enter the button on the Worksheet 'Users' which starts this macro. For more information you are referred to the Visual Basic code to be found in Worksheet 'Module 1'.
• The second macro, named GetItemCount, will show all item groups in Baan IV with the numbers of items per item group on the spreadsheet (Worksheet 'Itemcount'). You can enter the button on the Worksheet "Itemcount' which starts this macro. To run this macro you have to insert an Easy SQL query in the Baan IV Application. To insert the query start the session -> Maintain Query Data (ttadv3180m000), insert a record named 'item' and choose the option "Maintain Query by Text Manager". In the text editor enter the following query:
select tiitm001.citg, | Item group count(tiitm001.item) | Item
from tiitm001 | Item data group by tiitm001.citg order by tiitm001.citg
For more information you are referred to the Visual Basic code to be found in Worksheet 'Module 2'.
Saludos,
Félix
