Using ADO & Stored Procedures in C/SIDE

Recently, I had to do a small project. A client of us wanted to see the global inventory of all its companies. Furthermore, the companies were also spread over multiple databases. They wanted quick answers on questions like "What is the inventory of this item in any location in any company in any database". Now, I'm not going to tell you how I solved the case. I just wanted to highlight some interesting stuff that I used.

Some basics about using ADO in NAV.

ADO (ActiveX Data Objects) is described as "ADO is designed to provide a consistent way of accessing data regardless of how the data are structured. ". It's actually automation that you can use (should be installed together with windows - but I'm not sure) to access about any kind of database. It's generally used by developers in C/SIDE to access data in another database then the current database.

It's basically 3 steps:

  1. Make a connection to the database, using a "connectionstring"

    http://www.connectionstrings.com/ is a very helpful website to help you compose your connectionstring. Usually, I create a seperate setup table with the parameters needed to create a connectionstring.

    In C/SIDE, you can create a connection like this:

    IF ISCLEAR(lADOConnection) THEN CREATE(lADOConnection);
    lADOConnection.ConnectionString:=GetConnectionString(precDBServer."Server Name", precDBServer."Database Name", precDBServer.Login, precDBServer.Password);
    lADOConnection.Open;

    LADOConnection is an automation variable of the type "'Microsoft ActiveX Data Objects 2.8 Library'.Connection". You also see that I compose my connectionstring in a seperate function:

    GetConnectionString(...) : Text[1024]

    ltxtConnectionString:='Driver={SQL Server};'
        + 'Server='+pcodServerName+';'
        + 'Database='+pcodDatabaseName+';'
        + 'Uid='+pcodUserID+';'
        + 'Pwd='+pcodPassword+';';

    EXIT(ltxtConnectionString);

       

  2. Do your thing in the database (read, write, loop data, call SP, grant permissions,...) ... usually using SQL commands.

    We will go into this deeper further down the road. 
  3. Close your connection

    This is how I close my connection:

    lADOConnection.Close;
    CLEAR(lADOConnection);

If you're a dedicated C/SIDE developer, you see that this is not usual. Creating a connection? Closing a connection?? In C/SIDE, you're already connected by just declaring a record variable - by sorts of speaking. That's because the native client connects with the database before you can start working - and therefore, you're always connected Indifferent. It's logical, I know, but anyway ...

What could it be useful for?

So, what can we do in for example in the database. Well. A number of things. From the top of my head:

  • Creating company-specific views when a new company is created
  • Create data in a queue table
  • Compose a SQL String, execute that SQL String and givie back the results ... .
  • ...

It all comes down on executing a stored procedure in a SQL Server database. If you can do that, then the possibilities is limited to what you can do with Stored Procedures. And there is not much limitation to that Wink. About everything you can do in T-SQL, you can do with SP's!

So, let me try to explain all this by using a few examples:

  1. Creating company-specific views when a new company is created

    First of all, I created a stored procedure in SSMS (SQL Server Management Studio) like this (I added some comments to make things clearer):

    CREATE PROCEDURE [dbo].[SP_CreateView_ItemLocation]
        
    @CompanyName VARCHAR(30)
    AS
    BEGIN
        
    SET NOCOUNT ON;

    DECLARE @SQLString NVARCHAR(MAX)

    --If the view already exists, drop the view

    SET @SQLString = 'IF OBJECT_ID (''['+@CompanyName+'$SP_ItemLocation]'', ''view'') IS NOT NULL DROP VIEW ['+@CompanyName+'$SP_ItemLocation]'

    EXEC sp_executesql @SQLString

    --assemble the SQLString (including the companyname)

    SET @SQLString =
        
    'CREATE VIEW [dbo].['+@CompanyName+'$SP_ItemLocation]
        
    AS
        
    SELECT DISTINCT ''ILE'' AS TableName, [Item No_] AS ItemNo, [Location Code] AS LocationCode, 
        
    ['+@CompanyName+'$Location].Name AS LocationName, ['+@CompanyName+'$Location].[Main Location] AS MainLocation 
        
    FROM dbo.['+@CompanyName+'$Item Ledger Entry], ['+@CompanyName+'$Location] 
        
    where dbo.['+@CompanyName+'$Item Ledger Entry].[Location Code] = ['+@CompanyName+'$Location].[Code]'

    print @SQLString
        
    --this "print " is optionally - it is useful when you're debugging your SP 
        
    --in SSMS, because it shows the SQLString that you have been building.

    exec sp_executesql @SQLString

    END

    Then, I wrote some C/AL to call this SP (I excluded the connection-stuff mentioned above. Note that a connection is still necessary):

       

    CreateCompanyViews()

    ... <Open your connection here (see above)>

    IF ISCLEAR(lADOCommand) THEN
    CREATE(lADOCommand);    

    lvarActiveConnection := lADOConnection;
    lADOCommand.ActiveConnection := lvarActiveConnection;    

    lADOCommand.CommandText := 'SP_CreateView_ItemLocation';
    lADOCommand.CommandType := 4;
    lADOCommand.CommandTimeout := 0;    

    lADOParameter:=lADOCommand.CreateParameter('@CompanyName', 200, 1, 30,COMPANYNAME);
    lADOCommand.Parameters.Append(lADOParameter);    

    lADOCommand.Execute;

    ... <Close your connection here (see above)>

You see that I'm using LADOCommand, which is an automation variable of the type "'Microsoft ActiveX Data Objects 2.8 Library'.Command" and LADOParameter is a "'Microsoft ActiveX Data Objects 2.8 Library'.Parameter". Some explanation is required, I guess:

  • A command needs a Command Type. This site is useful to help you with the different command types. You see we used "4", being a stored procedure.
  • To let the SP know for what company we have to create the view, we have to send a parameter. Therefor, we use the ADO parameter automation mentioned above. To help you with this, this site is useful. You have to define the direction of the parameter (input, output, ...) and the data type and its length. In my example, I used an input parameter, varchar as datatype and length 30.
  • Note that we used lvarActiveConnection. This variable is of the type Variant. You have to give the active connection to the command variable, and this is the way to do it:
    • Load it into your variant like: lvarActiveConnection := lADOConnection;
    • Use it in your command like: lADOCommand.ActiveConnection := lvarActiveConnection;

To conclude, I added this code in Codeunit 2 at the end of the OnRun trigger. The code in this codeunit is executed when a company is created.

cduMyADOSamplesMgt.CreateCompanyViews;

I added this after the COMMIT, because I want to make sure that my company is created, no matter if my view was successfully created or not.

  1. Compose a SQL String, execute that SQL String and give back the results ... .

    This time, I went a little bit further Smile, because now I have an output result set Indifferent.

    First, my stored procedure:

    CREATE PROCEDURE [dbo].[SP_LoadInventory] 
        
    @ItemNo AS VARCHAR(20)
    AS

    BEGIN 
         SET NOCOUNT ON;    
         DECLARE @CompanyName VARCHAR(30) 
         DECLARE @OrigCompanyName VARCHAR(30) 
         DECLARE @SQLString NVARCHAR(MAX) 
         DECLARE @Counter INT

    DECLARE curCompany CURSOR FOR SELECT [Name] as CompanyName from dbo.Company
    OPEN curCompany

    SET @Counter = 0
    SET @SQLString=''

    FETCH NEXT FROM curCompany INTO @CompanyName
    -- Begin looping all companies in the database

    WHILE @@FETCH_STATUS = 0
    BEGIN 
        
    -- Converting the wonderful NAV-supported-but-best-not-used-in-SQL characters 
         SET @OrigCompanyName = @CompanyName 
         SET @CompanyName = REPLACE(@CompanyName,'.','_'); 
         SET @CompanyName = REPLACE(@CompanyName,'"','_'); 
         SET @CompanyName = REPLACE(@CompanyName,'\','_'); 
         SET @CompanyName = REPLACE(@CompanyName,'/','_'); 
         SET @CompanyName = REPLACE(@CompanyName,'''','_');    

    -- Only put the UNION in between of two SELECT statements
    IF @Counter > 0
    BEGIN 
        
    SET @SQLString = @SQLString + 'UNION' 
    END
    SET @SQLString = @SQLString + 
        

         SELECT DISTINCT ''' + @OrigCompanyName + ''' AS CompanyName,ItemNo, LocationCode, LocationName, MainLocation 
        
    FROM dbo.[' + @CompanyName + '$SP_ItemLocation] a 
        
    WHERE ItemNo = ''' + @ItemNo + ''' 
        
    '
    FETCH NEXT FROM curCompany INTO @CompanyName

    SET @Counter = @Counter + 1

    END;

    print @SQLString

    EXEC sp_executesql @SQLString        

    CLOSE curCompany
    DEALLOCATE curCompany

    END

    This is just a simple stored procedure that is going to UNION a SQL Statement for each company. In C/AL, you can call this Stored Procedure like:

    ... <Open your connection here>

    IF ISCLEAR(lADOCommand) THEN
    CREATE(lADOCommand);    

    lvarActiveConnection := lADOConnection;
    lADOCommand.ActiveConnection := lvarActiveConnection;    

    lADOCommand.CommandText := 'SP_LoadInventory';
    lADOCommand.CommandType := 4;
    lADOCommand.CommandTimeout := 0;    

    lADOParameter:=lADOCommand.CreateParameter('@ItemNo', 200, 1, 20,pcodItemNo);
    lADOCommand.Parameters.Append(lADOParameter);    

    lADOCommand.Execute;

    IF ISCLEAR(lADORecordset) THEN 
         CREATE(lADORecordset);

    lADORecordset.ActiveConnection := lvarActiveConnection;
    lADORecordset.Open(lADOCommand);    

    WHILE NOT lADORecordset.EOF DO BEGIN 

    ptmpGlobalInventoryBuffer.INIT;
    ptmpGlobalInventoryBuffer."Item No" := lADORecordset.Fields.Item('ItemNo').Value;
    ptmpGlobalInventoryBuffer."Company Name" := lADORecordset.Fields.Item('CompanyName').Value;
    ptmpGlobalInventoryBuffer."Location Code" := lADORecordset.Fields.Item('LocationCode').Value;
    ptmpGlobalInventoryBuffer."Location Name" := lADORecordset.Fields.Item('LocationName').Value;
    ptmpGlobalInventoryBuffer."Main Location" := lADORecordset.Fields.Item('MainLocation').Value;
    ptmpGlobalInventoryBuffer.INSERT;

    lADORecordset.MoveNext;

    END;

    ... <Close your connection here>

    May be here also some explanation:

  • Note that we used lvarActiveConnection to load the active connection into our recordset variable as well.
  • The execution of the SP is the same as before, but now you expect something back. That's why you're loading the resultset into a recorset ('Microsoft ActiveX Data Objects 2.8 Library'.Recordset) with the statement: lADORecordset.Open(lADOCommand);
  • After the recordset is loaded, we can loop that recordset and put (in my case) the results into a temp table.
  • Not that you can move through your recordset with MoveNext, MoveFirst, MoveFirst, MoveLast. You can check if you're at the end with EOF (End Of File).
  1. Using an output parameter in a Stored Procedure

    OK, taking a little step back. Suppose we want to create a SP with a simple output parameter like a decimal, boolean or whatever. So you won't get back a recordset like above, but just an output parameter. Not really creative, but a SP with an output parameter looks something like this:

    CREATE PROCEDURE [dbo].[WALDO_test] 
        
    @Result AS VARCHAR(20) OUTPUT
    AS
    BEGIN 
        
    SET @Result = 'Test Output';
    END

    You define your outputparameter with the OUTPUT keyword.

    In NAV it could be something like:

    ... <Open your connection here>

    IF ISCLEAR(lADOCommand) THEN 
        
    CREATE(lADOCommand);

    lvarActiveConnection := lADOConnection;
    lADOCommand.ActiveConnection := lvarActiveConnection;

    lADOCommand.CommandText := 'WALDO_test';
    lADOCommand.CommandType := 4;
    lADOCommand.CommandTimeout := 0;    

    lADOParameter:=lADOCommand.CreateParameter('@Result', 200, 2, 20,ltxtResult);
    lADOCommand.Parameters.Append(lADOParameter);    

    lADOCommand.Execute;

    //Get your result back from the command variable :
    ltxtResult := FORMAT(lADOCommand.Parameters.Item('@Result').Value);    

    MESSAGE(ltxtResult);

    ... <Close your connection here>

    You see that you need an extra statement to pull your result back from the command variable. It's not going to do that for you automatically (allthough you defined your parameter as being "output" (we defined the direction-argument in the CreateParameter-statement as "2")).

    One comment might be that you want to use the return value in stead of an output parameter, but keep in mind that the return value of a SP must be "integer".

There are tons of other things you can do with ADO, Stored Procedures,... . I just wanted you to experience how you can use ADO, and what it might be useful for. I'm not saying that this is always the best solution in any case. It's just one of the possibilities where much is possible and flexible solutions can be built with.

Sorry for the somewhat bad editing. I had my hands full with it Wink.

Any comment is highly appreciated.

Small remark: I created this blog using a live example (as mentioned above).  I just copied / pasted / changed variables / rearranged code / ... .  So I don't guarantee that everything just works by copy/paste/run it ... .

Comment List
  • Gordon,

    your first question I don't fully understand.  I guess updating a customer's address is no problem when using an SP.  What is your problem doing so exactly?

    Your second question:

    It can be quite frustrating, I know.

    First of all, check the case sensitivity.  I know it is an issue when you have set your collation Case sensitive... .

    Furthermore, I would like to see the entire function, not only the part with parameters.

    But again, do this on mibuso and post the link here.  More people can help then..

  • gordon,

    may be it's better to start a thread on mibuso and put it here ... many more people can help there...

  • Hi Waldo

    When I run this code I receive an error

    'the SP expected parameter @NavCode which was not

    supplied'

    I have put the code below.

    All I am trying to do is add a customer record to

    to a non Navision SQL database.

    Very frustrating!!

    **********************************************

    AdoParameter:=AdoCommand.CreateParameter('@NavCode',200,1,32,"No.");

    AdoCommand.Parameters.Append(AdoParameter);

    AdoParameter:=AdoCommand.CreateParameter('@Code',200,1,32,"No.");

    AdoCommand.Parameters.Append(AdoParameter);

    AdoParameter:=AdoCommand.CreateParameter('@Name',200,1,64,Name);

    AdoCommand.Parameters.Append(AdoParameter);

    AdoParameter:=AdoCommand.CreateParameter('@Address1',200,1,64,Address);

    AdoCommand.Parameters.Append(AdoParameter);

    AdoParameter:=AdoCommand.CreateParameter('@Address2',200,1,64,"Address 2");

    AdoCommand.Parameters.Append(AdoParameter);

    AdoParameter:=AdoCommand.CreateParameter('@Town',200,1,64,City);

    AdoCommand.Parameters.Append(AdoParameter);

    AdoParameter:=AdoCommand.CreateParameter('@County',200,1,64,County);

    AdoCommand.Parameters.Append(AdoParameter);

    AdoParameter:=AdoCommand.CreateParameter('@Postcode',200,1,10,"Post Code");

    AdoCommand.Parameters.Append(AdoParameter);

    AdoParameter:=AdoCommand.CreateParameter('@Contact',200,1,64,Contact);

    AdoCommand.Parameters.Append(AdoParameter);

    AdoParameter:=AdoCommand.CreateParameter('@Tel',200,1,32,"Phone No.");

    AdoCommand.Parameters.Append(AdoParameter);

    AdoParameter:=AdoCommand.CreateParameter('@Fax',200,1,32,"Fax No.");

    AdoCommand.Parameters.Append(AdoParameter);

    AdoParameter:=AdoCommand.CreateParameter('@WebURl',200,1,255,"Home Page");

    AdoCommand.Parameters.Append(AdoParameter);

    AdoParameter:=AdoCommand.CreateParameter('@CurrencyCode',200,1,10,"Currency Code");

    AdoCommand.Parameters.Append(AdoParameter);

    AdoParameter:=AdoCommand.CreateParameter('@CountryCode',200,1,10,"Country/Region Code");

    AdoCommand.Parameters.Append(AdoParameter);

    AdoParameter:=AdoCommand.CreateParameter('@VatRegNo',200,1,64,"VAT Registration No.");

    AdoCommand.Parameters.Append(AdoParameter);

    AdoParameter:=AdoCommand.CreateParameter('@PaymentTerms',200,1,64,"Payment Terms Code");

    AdoCommand.Parameters.Append(AdoParameter);

    AdoCommand.Execute();

  • I need to update a customer table in a non Navision SQL

    2008 database and need to execute a stored procedure by passing variables to it . I do not think it is possible with a sql string as it only sees literals not variables so in passing the customer address I am passing 'Address'not the actual address.

    Is there a way around this or do I have to adandon ADO

    completely?

  • It should be an automation of the type:

    'Microsoft ActiveX Data Objects 2.8 Library'.Connection

    hope this helps

  • What is the variable declaration for this lvarActiveConnection? I can't seem to make this work in Nav 5.0...Thanks...

  • Hello Waldo. I have been searching the net, hoping you wrote something somewhere that will help me specifically about sending a SQL query into SQL Server from NAV using C/SIDE variables. Here is the link: mibuso.com/.../viewtopic.php, the topic is: Question for Waldo and the Gurus.

    Cant wait to read your input. I know you can help me. Please!

  • I just wanted to add some additional suggestion.

    When using recordset variable. Add it as local otherwise you will get an error for duplicate Variable.

    Second. When running a sql statement that returns a record set, for Decimal values etc, you will get an error

    This message is for C/AL programmers:

    This data type is not supported by C/SIDE. You can access data of any of the following data types: VT_VOID, VT_I2, VT_I4, VT_R4, VT_R8, VT_CY, VT_DATE, VT_BSTR and VT_BOOL"

    You can change you sql statement and use cast function

    Select Amount

    from Customer

    to

    Select cast (Amount as float)

    from Customer

  • I suggest that you open a new thread on Mibuso for that ... then more people will respond ... .

    When you copied the xp_ndo in the binn folder, you can add the extended stored procedures by adding them manually on the master database.

  • Hi,

    Please I need someone to help me out urgently.

    I have installed SQL Server 2005 and Navision client (sql server option) on my system and I was able to connect to the database, restore data and do all sort.

    When I tried connecting from another system, a got an error message cannot find server. I typed the serverpath and name but it still gave me the same thing. Could this be because the systems are in workgroup not domain? Or do I have to set anything on the other systems? (Like the host file when using the native database server). Please help me out

    Secondly, I dont know how to bring the stored procedure into SQL, I have already unzipped the xp_ndo.dll file into the binn directory of SQL installation. Can anybody send me the codes on how to do this.

    Please, I'll appreciate your urgent response.

    Thanking you in anticipation

    You can reply back to debby_box@yahoo.co.uk

  • Hm, strange.  I wrote the above using an SQL2005 database...

  • Hi Waldo, have you ever done this for a SQL 2005 database? We have some code that worked on SQL 2000 and now get an error saying that the SQL-DMO cannot be used to connect to the server and you must use SQL Server Management Objects (SMO) instead.

  • Thanks pdj for this info.  It is indeed something to watch out for.  I know I didn't handle security very well in this post, so your comments are very valuable... .

  • Great stuff! I was actually working on a private NAV-ADO project at the very moment I noticed your post in Google Reader :-) My project might show up in the Mibuso downloads if I get it to work ;-)

    Only comments:

    * You should mention the risk for SQL-Injections. An evil person with permission to create companies could execute any SQL statement in your database impersonating the service user with this solution.

    * You should mention the possibility of using "Integrated Security=SSPI" to use the credentials of the current AD user instead. (Some times it is a better solution, sometime not)

    But a yet another great article - keep'em coming :-)

Related
Recommended