SQL from C/AL

Steve_Contris

We are using Navision 3.7 native database installation. I have a need to access data in a SQL Server 2005 database. I am needing guidance on how to make SQL queries from C/AL code.

Marije_Brummel

Check out the downloads section, there is a great post about ADO!

You can also find a lot of info about ADO on the msdn websites.

Steve_Contris

I only found one download in the mibuso downloads that had to do with ado and that was a code unit that I imported but am unable to view or use. It tells me I don't have permission to read it.

Is this the download you were referring to?

nunomaia

if are talking about http://www.mibuso.com/dlinfo.asp?FileID=589

Before you import codeunit from text file, change object ID to your current license range. Normally start at 50 000

Steve_Contris

how do I change the object ID?

I actually tried that already with notepad but it would not import it. No error, no nothing, no import. Is there a better way?

And of course, I can't delete the one I imported - because I don't have permissions.

nunomaia

Edit Text file

Change

OBJECT Codeunit 91000 ADO test
{
  OBJECT-PROPERTIES
  {
    Date=06.04.27;
    Time=16:42:54;
    Modified=Yes;
    Version List=ADO Test by Miklos Hollender;
  }

to

OBJECT Codeunit 50000 ADO test
{
  OBJECT-PROPERTIES
  {
    Date=06.04.27;
    Time=16:42:54;
    Modified=Yes;
    Version List=ADO Test by Miklos Hollender;
  }

To delete previous object you need a license a developer license.

Steve_Contris

I was not aware you could import objects in that text form. So, I modified the ID number to a value in the range we are licensed for but now I get a message"

'Automation' is not an option.

The existing options are:

System, File, TransactionType, InStream, OutStream, Variant........

nunomaia

Can you confirm if your license has

Granule 1750 C/OCX

Steve_Contris

I dont have that granule.

nunomaia

Steve Contris wrote:

I dont have that granule.

So, you can't import codeunit in text that use automation. Neither develop using automation.

Steve_Contris

I wish it would have been mentioned somewhere earlier that in order to do SQL queries from navision, that I would need that particular granule. It would have saved everyone some time - seems like a fundamental piece of this process.

But, is that a granule that I should already have? I notice I already have the C/ODBC granule. - Or is it one I have to purchase special?

nunomaia

That it's a different granule.

Do you have developer license?
You only need that granule if you are developing with automation.
Importing compiled fobs you won't have any problems.

From Granule explanation:

The automation controller functionality means that you can control an application that is an automation server (for example, Microsoft Excel or Microsoft Word) from C/AL. In this way, you can use Microsoft Excel as a function library, or you can transfer data from C/SIDE to Microsoft Excel and generate a graph based on this data. You can also create a form letter in Microsoft Word directly from C/SIDE. Other possibilities include integration with Microsoft Outlook (for example, creating an Outlook Contact directly from a Customer record).

Non-visual OCXs are also supported. An OCX is a COM object that executes in process (that is, it is not a stand-alone program). An OCX could contain, for example, a mathematical function library or an interface to an external device. You can buy third-party controls, or develop your own in Microsoft Visual C++ or Visual Basic.

The granule allows you to create OCX and Automation variables in any object containing C/AL code. It also allows you to import objects in text format that contains OCX and Automation variables. This granule is, however, not required to run objects that use OCX or Automation variables.

Steve_Contris

Not sure what constitutes a "developers license". I do have the application builder granule.

Where can I find a list of explanations for the granules like the one you posted? That would be very useful.

But, are you saying that if the fob that I downloaded had been compiled with an ID in the appropriate range for my license I could import and use it even though I do not have the C/OCX granule?

DenSter

You need the C/OCX granule to get access to automation variables.