Show documents in a grid stored in a external database

Finance and Operations/AX Developer Forum
1

Hello,

We are using AX 2009 and we would like to know if it’s possible to show and download documents stored in a external SQL server database.

The documents are stored as image type and ideally we want to connect via odbc and build a grid to show and donwload the documents.

It’s possible?

Thanks in advance

2

You can open a execute a sql query on remote server using OBDC and fetch objects.

Check an example from http://msdn.microsoft.com/en-us/library/ee677510.aspx I think it should be valid in 2009.

3

Hi Nuno,

We have tried a similar code but we are unable to donwload the document . We think the problem is due to the type field (image).

Thanks

4

You are trying to download and a manipulate a blog data type directly from SQL Server. I would rather download the blob from a .NET assembly inside AX.

5

image type means binary data, i.e. raw zeros and ones. You need to know how to interpret them. How do you write documents to the database?

6

We wanted to use image and always we had and error message but if you use FILE instead will let you do it without problems, try it

7

Based on your description, I agree with Martin comments.

If you can process images correctly from a file but you can’t process images from the database, it seems to be an interpretation error of the format.

8

Hello,

We are not writing the docs, we are only consumers of this files (pdf stored as image type).

I’ll ask to the owner of the database.

Thanks

9

Hello,

Database owner says we could follow similar steps like this but we would prefer to translate this code to X++ and show the information in a AX grid where we users could apply filters and download the files directly

It’s possible?

Thanks

10

You can use the same classes through .NET Interop. The exact solution depends on how you read the data from the external database. I would encapsulate the whole communication with the database to a separate .NET library, rather then coding it in X++.

You can, for example, pass a binary array encoded to a Base64 string and use BinData::loadFromBase64() in AX, or you can save data to a file and pass its name to AX, or so.

If you want a more specific answer, please tell me more about how you’re going to communicate with the database and where you’re going to store the data (e.g. in AX DB, in file system, loading from the external DB every time).

11

Hello,

The external database is linked to our AX database and we want to load the documents assigned to users every time and eventually open or download. At the moment only one document at once but we have in mind to allow multiple selections and zip all the documents.

We are trying this code but it doesn’t work :

static void BD_Doc(Args _args)
{

str txtSelect;
Statement stmt;
OdbcConnection con;
ResultSet resultado;

LoginProperty LP1 = new LoginProperty();

str id_doc = ‘687686506’;

binData binData;
binData binData2;

container basecontainer;
;

LP1.setDSN(“BDDOCS”);

con = new OdbcConnection(LP1);
stmt = con.createStatement();

txtSelect = ’ SELECT BDDOCS.ID_DOC,’;
txtSelect += ’ BDDOCS.OBJ_OBJECT ‘; //The data type in the external database is stored as image
txtSelect += ’ FROM BDDOCS ‘;
txtSelect += ’ WHERE BDDOCS.ID_DOC = ’ + "’" + id_doc + "’";

resultado = stmt.executequery(txtSelect);

while (resultado.next())
{

//obj_object field doc pdf database SQL

binData = new BinData();
binData.setStrData(resultado.getString(6)); //We have troubles here. Which type should be use? GetString? ,GetByte?, GetInt? GetInt64?

binData.getData();

info(int2str(binData.size()));
//binData.saveFile(“c:\Prueba_pdf_jpm” + int2str(TimeNow()) +".pdf");
}
}

Databases have diferent collation. AX database is Modern_Spanish_CI_AS and remote database is SQL_Latin1_General_CP1_CI_AS. Could this be related?

Thanks

12

It’s clearly not a string - it’s binary data. Get all individual bytes by GetByte().

Or you make it simpler with .NET - either with SqlDataReader.GetBytes() or with a higher-level API such as ADO.NET or LINQ to SQL.

13

Yes, we tried with GetByte () without success.

The different collation in the databases could affect?

Thanks

14

Collation is related to texts, not to binary data.

I think you’re making your life more difficult than needed. Use LINQ to SQL or something, so you don’t have to deal with individual bytes and such stuff. Or you may want to hire somebody to do it for you.

15

Hi,

GetByte() function, returns a single Byte and not all blob data, to manipulate blob information I personally prefer ADO.NET