Automation Object Scalar SQL Query

colin

Hi Fellow programmers!

I hope one of you has an answer for me.

During our migration from NAV4.03(SQL) -> NAV5.00(SQL) we decided to minimise the changes in the default objects.
Instead we are using more SQL Server objects Outside NAV.

One of these are Targets.
We used to have only Targets for Salespersons and Customers, but now we are also have extra dimensions within these targets.
( for example Targtes per ProductCategory Per Customer per Month. )

We use an Automation Object which connects to the specific SQL Server and uses a generated Query to get the right Target value.

But what I still not manage to get it work is passing this result back to NAV.

Here is the Function for the easiest targetinfo. Hope the answer is very simple

gFncGetTargetInfo()
  BEGIN
    gtxtSQLString := 'SELECT Target FROM Targets.Targets WHERE Code = '' + USER + '' ';
    gtxtSQLString := gtxtSQLString + ' AND [Year] = Year(GETDATE()) AND [Month] = Month(GETDATE())';
    gDecTarget := autobj.GetScalar('SRV-SQL01','Development',gtxtSQLString);
      END

This function uses the following function in a codeunit:

GetScalar(ltxtServer : Text[50];ltxtDatabase : Text[50];ltxtSqlstring : Text[1024]) lDecOutput : Decimal

CREATE(sqlConn);
gtxtsqlconn := 'Provider=sqloledb;Data Source=' + ltxtServer + ';'
+ 'Initial Catalog=' + ltxtDatabase + ';Integrated Security=SSPI;';
sqlConn.Open(gtxtsqlconn);
sqlConn.Execute(ltxtSqlstring);
sqlConn.Close;
CLEAR(sqlConn);

EXIT(lDecOutput);

- How do I get the result back ?
- And is it also possible to return a complete recordset?

All help is welcome.
Colin

colin

I have found an Answer.

Instead of using a Codeunit I added an Automation object in the form itself.

next to the Create and Close I put in the following code:

  autRecSet.Open(gtxtSQLString, autConnection, 1, 1);
  IF NOT autRecSet.EOF THEN BEGIN
    autRecSet.MoveFirst;
    gDecTarget := autRecSet.Fields.Item('Target').Value;
    END
  ELSE BEGIN
    gDecTarget := 0;
  END;

With the help of the "'Microsoft ActiveX Data Objects 2.8 Library'.Recordset" I can get one scalar value but also the complete recordset!

I'm happy!

Marije_Brummel

This is a strange way of implementing NAV.

Can you explain more why you do this?

IMHO it will create a system that is very hard to maintain...