ODBC Connection error

(0) Share

Report

Posted on by Community Member

I am sending data from AX 2012 R3 CU10 to ODBC. I get a critical error when calling ODBC however, SQL is still updated. Not sure what could be causing the issue. Please find the code below and error message.

server public static void runOdbcQuery(str _OdbcQuery)
{

LoginProperty loginProperty;
OdbcConnection odbcConnection;
Statement statement;
ResultSet resultSet;
SqlStatementExecutePermission perm;
str connection;
BSKParameters odbc;

// Set the information on the ODBC.
select * from odbc;
loginProperty = new LoginProperty();
connection = "Driver=" + odbc.Driver +";Description="+ odbc.Description +";Server=" + odbc.Server + ";Database="+ odbc.Database + ";UID=" + odbc.UserId + ";PWD=" + odbc.Password + ";";
//connection = "Driver=ODBC Driver 11 for SQL Server;Description=OMS_32;Server=" + odbc.Server + ";Database="+ odbc.Database + ";UID=" + odbc.UserId + ";PWD=" + odbc.Password + ";";

loginProperty.setOther(connection);

// Create a connection to external database.
try
{
odbcConnection = new OdbcConnection(loginProperty);
}
catch
{
throw error("Error creating the ODBC Connection");
}

if (odbcConnection)
{
//Prepare the SQL statement.
statement = odbcConnection.createStatement();
perm = new SqlStatementExecutePermission(_OdbcQuery);
perm.assert();

resultSet = statement.executeQuery(_OdbcQuery);
//resultSet = statement.executeUpdate(sql);

}
resultSet.next();
resultSet.close();
statement.close();

}

Error message relates to Invalid Cursor state and from my research it seems as if the Cursor starts before it should. Not sure how to resolve it though. Appreciate any help.

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Martin Dráb 236,394 Most Valuable Professional on at

Like (0)

Report

RE: ODBC Connection error

Which line of code is throwing the error?

Was this reply helpful? Yes No
Suggested answer

Iulian Cordobin 8,201 on at

Like (0)

Report

RE: ODBC Connection error

I think your T-SQL statement is wrong; can you review that and make sure it is a valid update table t-sql. On second thought, reading closely your code I can see you are doing executeQuery with your update T-Sql, and then a .next()? Or the last code you have here is not erroring out?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: ODBC Connection error

It starts erroring at resultSet.next()

The ODBC statement runs and when I check SQL the entry exists there, however I believe it's related to a Cursor error. From my reading, it seems as if when I run resultSet.next() it sends through an empty command and that's whats causing it. However I have no idea how to clear the Cursor state

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: ODBC Connection error

The T-SQL is correct, as I've run it in SQL Server Management Studio and runs fine.

The .next() is where the error is occuring, which is weird becasue the insert statement into SQL is successful and the entry exists in SQL

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

RE: ODBC Connection error

The issue is I was sending an Insert SQL command and not a Select, so therefore the Resultset was pointless as I wasn't retrieving any fields back.

To fix I changed the following line:

resultSet = statement.executeQuery(_OdbcQuery);

with the following line:

statement.executeUpdate(_OdbcQuery);

And that resolved the issue!

Was this reply helpful? Yes No