I need to read an MS-Access file from an Azure Function and Access Database Engine does not work in this scenario. I've found UCanAccess library, and I tried all the suggestions from UCanAccess, IKVM and C#, and my current code looks like this:
internal string QueryData(string tableName)
{
Properties props = new Properties();
props.setProperty("user.language", "en");
props.setProperty("user.country", "US");
props.setProperty("locale", "en_US");
props.setProperty("hsqldb.reconfig_logging", "false");
string connString = "jdbc:ucanaccess://D:\\path to\\mdb file\\myfile.mdb";
ikvm.runtime.Startup.addBootClassPathAssembly(System.Reflection.Assembly.Load("ucanaccess-5.0.1"));
java.sql.Driver v_driver = (java.sql.Driver)java.lang.Class.forName("net.ucanaccess.jdbc.UcanaccessDriver, ucanaccess-5.0.1, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null").newInstance();
java.sql.Connection v_con = v_driver.connect(connString, props);
v_con.setReadOnly(true);
v_con.clearWarnings();
java.sql.Statement v_st = v_con.createStatement();
java.sql.ResultSet v_res = v_st.executeQuery(string.Format("select top 10 * from {0}", tableName));
java.sql.ResultSetMetaData v_resmd = v_res.getMetaData();
StringBuilder stringBuilder = new();
for (int i = 1; i <= v_resmd.getColumnCount(); i++)
stringBuilder.AppendLine(v_resmd.getColumnLabel(i) + "|");
while (v_res.next())
{
for (int i = 1; i <= v_resmd.getColumnCount(); i++)
stringBuilder.AppendLine(v_res.getString(i) + "|");
}
return stringBuilder.ToString();
}
The code above is in a separate class called from the function method which is invoked with a "get" operation. If I try the code in a console program, it works, but if I try to use the same code in the azure function, I'm getting the following error:
System.Private.CoreLib: Exception while executing function: myFunctionName. ucanaccess-5.0.1: java.util.MissingResourceException: Can't find bundle for base name net.ucanaccess.util.logger_messages, locale es_CO
As the message seemed to be related to locale configuration, I tried to change locale and languague properties through an instance of Properties class as shown above and using it as a parameter to instance the driver with no luck. Also, all the properties set were for trial/error tests.
As for why I'm trying to do this in an Azure Function, the reason is I have several mdb files in a shared folder, accesible through a VPN. the VPN is configured in Azure with a VNet and is accesible from the Function, and the goal is to read the files, transform the data and return it in json format. The function will be called from a Logic App.
What can I do to make UCanAccess to work in this scenario? Thanks in advance.