Fixing SQLite3 Connection Failures

[andymenon]

Hello,

This is a generic post on how you might fix connection failure issues if you are a using the latest SQLite 3 database. You will need the Function for Data - SQLite - 1.0.1 VBO available here on the Digital Exchange to work with your SQLite 3 database. 

NOTE and DISCLAIMER

The VBO works for 32-bit versions of SQLite, but version 3 requires modifications. Therefore, there is nothing wrong with the VBO. But if you are like me and in a hurry to make progress, then you may find the steps in this post helpful.

This post assumes that you have already setup this VBO to work with your Blue Prism Studio environment.

Issue(s): 

Using the Set Connection Details action does not result in any errors

However, when the Open action is called, the output message returned in the Error output parameter will look something like this:

an attempt was made to load a program with an incorrect format

Reason: This is typical of a 32-bit vs. 64-bit mismatch.

Solution: We will need to replace the DLLs that accompany the above VBO with those that can work with a 64-bit version of SQLite database. 

• Based on the version of .NET installed on my machine, I downloaded this zip file:
  • https://system.data.sqlite.org/downloads/1.0.118.0/sqlite-netFx40-binary-x64-2010-1.0.118.0.zip
• That said, you may want to go to this page here to download the right zip for your situation!
• Unzip this file to a suitable location on your machine

Checking if the DLLs will work with your SQLite 3 Database

Before you proceed with additional steps, it would be worthwhile to see if the DLLs in the file that you just downloaded work with your SQLite database to begin with.  To do that, follow these steps:

1. Run the test.exe utility inside the folder that you unzipped in the preceding steps
2. Update the connection string to include the full path to your database.
3. In this case I'm using the sample chinook.db database downloaded from the SQLite web page and the connection string would look like this following the update to the database path
   
   Data Source=C:\Users\Public\Documents\DATABASE\sqlite3\db\chinook.db;Pooling=true;FailIfMissing=false
   
   
4. Hit the Run button to test
5. Should the DLLs line up with your version of SQLite 3, then you must see along list of test results marked Succeeded as shown below (that also displays the exact version of SQLite 3 Engine)
6. Copy the full connection string from the test.exe Connection String field as you will need it for testing further down below

Replacing the DLL Files in the \Blue Prism Automate Folder

If you have already setup the VBO in your environment previously, then you know that two DLL assets specified in the VBO documentation (SQLite.Interop.dll & System.Data.SQLite.dll shown below) will need to be dropped into the Blue Prism Automate folder that is normally located here:

C:\Program Files\Blue Prism Limited\Blue Prism Automate

Before proceeding, make sure to close the test.exe as it would be latched on to the System.Data.SQLite.dll file. In addition backup the current versions of the DLLs you are going to be replacing.

The two DLLs will be present in the folder alongside the test.exe utility. To replace them, simply copy the two DLL files from this folder, and drop them into the \Blue Prism Automate folder.

You will be prompted with file replacement warning. And if you compare the files note that the new files on the left are different in size when compared to what's currently being used by the VBO. 

Complete the file replacement. You will likely require to approve the Administrator permissions dialog if it pops us when the replacement is in progress for each of the files. 

Testing the Solution from Blue Prism Object Studio

Here is the simple VBO that I created to test the impact of the solution. I used the Connection String option when connecting to the chinook database.  The connection string you copied from test.exe will come in handy here.

As seen from the Collection, 275 records were returned from the artists table  

Thanks and I hope this helps you in working with the most latest version of SQLite 3 databases.

------------------------------
Prashanth (Andy) Menon
------------------------------