DAX code connection to another SQL database

During data migration it is often necessary to pull data from another SQL server database. It can also be necessary for integration purposes. Below is some sample code that can be used to get data from another system.

As with all code posted, it should be modified to your requirements and thoroughly tested before being released to a production environment.

LoginProperty loginProperty;
OdbcConnection odbcConnection;
Statement statement;
ResultSet resultSet;
str sql;
SqlStatementExecutePermission perm;

;

loginProperty = new LoginProperty();

loginProperty.setServer(“<SQL Server>”);
loginProperty.setDatabase(“<database>”);
odbcConnection = new OdbcConnection(loginProperty);
if (odbcConnection)
{
sql = “SELECT “;
sql += “<field 1>,”;    //1
sql += “<field 2>,”;    //2
sql += ” FROM <table>”;
sql += ” WHERE “;
sql += ” <table>.<field> = ‘<field value>'”;

perm = new SqlStatementExecutePermission(sql);
perm.assert();
statement = odbcConnection.createStatement();
resultSet = statement.executeQuery(sql);
CodeAccessPermission::revertAssert();

while(resultSet.next())

{
<field 1> = strLRTrim(resultSet.getString(1));

<field 2> = strLRTrim(resultSet.getString(2));

}

resultSet.close();
statement.close();
}
else
{
error(“Failed to log on to the database through ODBC.”);
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s