Creating an ODBC connection - Step to StepContestant

Introduction

This article is intended to be a simple tutorial on how to create ODBC connections and working with them, since I found starting with them a little bit confused, but I had amazing people to take my hand and walk me through it, and I think everyone deserves that kind of help too.
I'm going to divide each little part in sections, so feel free to jump to the one you feel the need to, although I recommend reading everything.
I'm going to use the sample data created in a previous article, Quick sample database tutorial: Samples.PersistentData, with the properties Name and Age.

Creating the connection

1. Open the ODBC Data Sources - search for ODBC in the search bar of your computer and you will find it.
2. Select the System DNS tab
3. Press Add
4. Select the apporpriate driver - for this example I'm using InterSystems IRIS ODBC35
5. Choose a name for the connection
6. Type the server, port and namespace you want to connect to (e.g. IP 12.0.0.1, port 1972 and namespace SAMPLE)
7. Type the Username and Password you will use to connect

Press "try connection" to see if everything is working out fine - if it doesn't succeed, double check Username and Password, server, port and namespace, and also check if your IRIS is started (for this example), or if you need a VPN for this connection.

OBS.: I don't know if this works similarly in Linux or iOS, sorry!

Using your connection in a Business Operation on Production

This is only one example of how you can put on practice this connection, but it's a wild used one.

With a Business Operation with adapter "EnsLib.SQL.OutboundAdapter" on Production, open its configuration tab and expand the Basic Parameters part. You're gonna see a DSN input like this:

expand the input and find the connection we've just created. If it's not there, make sure you created it on the correct ODBC Data Source (32-bit or 64-bit). If it's not there just follow the steps again on the other option and check the DSN input once more.

Credentials

IRIS might need a username and password to have access to this connection, so you need to provide it.

Right under the DSN input, you're going to find a Credentials input with a magnifier beside it.

Click the magnifier and you'll find yourself in the Credentials menu.

On the tab to your right, click "New", type an ID that will help you identify this credential, the username and password needed and save.

Great! Now that you have your credentials you can go back to the production and select them by the ID you chose.

PS.: an example for you to test

For this simple tutorial, I've created the following class in a different namespace than the one where "Sample.PersistentData" is:

Class Sample.ODBC.Operation Extends Ens.BusinessOperation
{

Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";
Property Adapter As EnsLib.SQL.OutboundAdapter;
Parameter INVOCATION = "Queue";
Method LegalAge(Request As Sample.request, Response As Sample.response) As %Status
{
    // instanciate the response
	Do Request.NewResponse(.Response)
	
	// Execute the query and select the first result
	Do ..Adapter.ExecuteQuery(.result, "SELECT Name, Age from Sample.PersistentData where Age > 20")
	Do result.%Next()
	
	// just for visualizing, sets the first result in the response
	Set Response.result = result.%Get("Name")_" "_result.%Get("Age")
	
	Quit 1
}

XData MessageMap
{
<MapItems>
	<MapItem MessageType="Sample.request"> 
		<Method>LegalAge</Method>
	</MapItem>	
</MapItems>
}

}

Conclusion

Thank you for reading and I hope this was helpful!
Feel free to contact me for any doubts.