AX 2012 to external DB connectivity

Dynamics 365 for Finance and Operations/AX Developer Forum
21

But did you write this code - convert(varchar(10),res.ResultTime,120) as Sample Data - in AX 2012??

It seems for me that this code is for SQL and not Axapta

22

Anyway, I will try it!

Thanks a lot Piotr

23

Exactly like this:

objConn.Open();
cmdSelect = objConn.CreateCommand();
inventBatchId = subStr(caller.inventBatchId(), strFind(caller.inventBatchId(),’-’,1,100)+1,100);
cmdSelect.set_CommandText("select CAST(res.WorkstationId as varchar)+’’+CAST(res.SampleIndex as varchar)+’’"
+"+CAST(res.Intakenumerator as varchar)+’’+CAST(res.syntheticnumerator as varchar) as recID " //+’’+CAST(dc.cid as varchar) as recID "

  • ",res.SampleIndex as k1 "
  • ",res.WorkstationID as k2 "
  • ",res.Intakenumerator as k3 "
  • ",res.syntheticnumerator as k4 "
  • ",tc.Value as LabProtocols_LabProtocolDocRef "
    + ",convert(varchar(10),res.ResultTime,120) as LabSamples_LabSampleData "
    //+ ",convert(varchar(8),res.ResultTime,114) as LabSamples_LabSampleTime "
  • ",datepart(hh, res.ResultTime) * 3600 + datepart(mi, res.ResultTime) * 60 + datepart(ss, res.ResultTime) as LabSamples_LabSampleTime "
  • ",dc.cid as CID "
  • ",dc.value as LabAnalytes_LabAnalyteValue "
  • ",convert(varchar(10),res.ResultTime,120) as LabAnalytes_LabAnalyteData "
  • ",convert(varchar(8),res.ResultTime,114) as LabAnalytes_LabAnalyteTime "
  • ",tc.Value as LabAnalytes_LabAnalyteDesc "
  • "from FIBase.dbo.DoubleComponents dc "
  • "inner join FIBase.dbo.Results as res on (res.SampleIndex = dc.SampleIndex and res.WorkstationID=dc.WorkstationID and res.Intakenumerator=dc.intakenumerator and res.syntheticnumerator=dc.syntheticnumerator) "
  • "inner join FIBase.dbo.TextComponents tc on (tc.SampleIndex = dc.SampleIndex and tc.WorkstationID=dc.WorkstationID and tc.Intakenumerator=dc.intakenumerator and tc.syntheticnumerator=dc.syntheticnumerator and tc.cid=318832640) "
  • "left join FIBase.dbo.TextComponents tc2 on (tc2.SampleIndex = dc.SampleIndex and tc2.WorkstationID=dc.WorkstationID and tc2.Intakenumerator=dc.intakenumerator and tc2.syntheticnumerator=dc.syntheticnumerator and tc2.cid=335609856) "
    //+ “where left(tc.value, 9) = '” + inventBatchId + "’ and tc2.value = ‘Średnia’ "
  • “where upper(left(tc.value, 12)) = '” + _caller.PickListJournalId + "’ and tc.value is not null and tc.value <>’’ and tc2.value = ‘Średnia’ "
  • “order by recId”);

reader = cmdSelect.ExecuteReader();

24

Hope this code helps! [:D]

static void TestODBC()

{

LoginProperty loginProperty;

OdbcConnection odbcConnection;

Statement statement;

ResultSet resultSet;

str sql, criteria;

SqlStatementExecutePermission perm;

;

//Set information on the ODBC

loginProperty = new LoginProperty();

loginProperty.setDSN(“dsn”);

loginProperty.setDatabase(“databaseName”);

//Create connection to external DB

odbcConnection = new OdbcConnection(loginProperty);

if (odbcConnection)

{

sql = “SELECT * FROM MYTABLE WHERE FIELD =” +

criteria + " ORDER BY FIELD1,FIELD2 ASC";

//assert permission for sql string

perm = new SqlStatementExecutePermission(sql);

perm.assert();

//Prepare statement

statement = odbcConnection.createStatement();

resultSet = statement.executeQuery(sql);

//Running statement

while (resultSet.next())

{

//It is not possible to get field 3 and then 1.Always get fields in numerical order: 1,2,3,4,5,6

print resultSet.getString(1);

print resultSet.getString(3);

}

//Shutting down the connection

resultSet.close();

statement.close();

}

else

error(“Failed to log on to the database”);

}