The Informix.Database connector enables you to connect to an Informix database server and retrieve data from it. The connector uses the Power Query M function, which is a functional programming language that is used to define data transformations and manipulations. In this article, we will explore the M code behind the Informix.Database connector and how it works.

Connecting to an Informix Database

To connect to an Informix database using Power Query, you need to have the following information:

– Database server name or IP address

– Database name

– Port number

– Authentication method (Windows or database authentication)

– Username and password (if using database authentication)

Once you have this information, you can use the following M code to connect to the database:

let

serverName = “serverName”,

dbName = “dbName”,

port = 1234,

authMethod = DatabaseCredential,

username = “username”,

password = “password”,

connectionString = “DRIVER={IBM INFORMIX ODBC DRIVER};SERVER=”+serverName+”;DATABASE=”+dbName+”;HOST=”+serverName+”;SERVICE=”+port+”;PROTOCOL=onsoctcp;CLIENT_LOCALE=en_US.utf8;DB_LOCALE=en_US.utf8;”,

source = Odbc.DataSource(connectionString, [HierarchicalNavigation=true, Credential=authMethod, UID=username, PWD=password])

in

source

let

serverName = “serverName”,

dbName = “dbName”,

port = 1234,

authMethod = DatabaseCredential,

username = “username”,

password = “password”,

connectionString = “DRIVER={IBM INFORMIX ODBC DRIVER};SERVER=”+serverName+”;DATABASE=”+dbName+”;HOST=”+serverName+”;SERVICE=”+port+”;PROTOCOL=onsoctcp;CLIENT_LOCALE=en_US.utf8;DB_LOCALE=en_US.utf8;”,

source = Odbc.DataSource(connectionString, [HierarchicalNavigation=true, Credential=authMethod, UID=username, PWD=password]),

dbo_Customers = source{[Schema=”dbo”,Item=”Customers”]}[Data]

in

dbo_Customers

let

serverName = “serverName”,

dbName = “dbName”,

port = 1234,

authMethod = DatabaseCredential,

username = “username”,

password = “password”,

connectionString = “DRIVER={IBM INFORMIX ODBC DRIVER};SERVER=”+serverName+”;DATABASE=”+dbName+”;HOST=”+serverName+”;SERVICE=”+port+”;PROTOCOL=onsoctcp;CLIENT_LOCALE=en_US.utf8;DB_LOCALE=en_US.utf8;”,

source = Odbc.DataSource(connectionString, [HierarchicalNavigation=true, Credential=authMethod, UID=username, PWD=password]),

dbo_Customers = source{[Schema=”dbo”,Item=”Customers”]}[Data],

filteredCustomers = Table.SelectRows(dbo_Customers, each [City] = “Seattle”)

in

filteredCustomers