Sqlite connection

[VratHimbo]

Hi team,
i didn't found any resource to connect sqlite to blue prism for doing some querys, the first question is: is possible to do that?
Then, i've just tried to set a connection through Data - OLEDB - Set Connection and connection string: 

"DRIVER=SQLite3 ODBC Driver;Database=C:\BluePrismTest.sqlite3;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;"

without success. Any idea?
Thanks in advance

------------------------------
andre a
------------------------------

[ewilson]

@VratHimbo,

Have you installed an actual SQLite ODBC driver? If so, which one (Ex1, Ex2, ...)? Do you require the use of ODBC?

Cheers,
​

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

[VratHimbo]

Hi @ewilson,

like the screenshot attached sqlite3 odbc datasource. I just need to submit some query inside sqlite, unfortunately i don't know very well the difference beetwen using or not odbc. 

Thanks in advance

------------------------------
andre a
------------------------------

[ewilson]

@VratHimbo

There are a few things working against you here I think. First, I don't think the OLEDB provider will be able to work with this ODBC connection. I think you would actually need to leverage the objects in the System.Data.Odbc namespace to make this work. Furthermore, I believe you will need to install the 32-bit versions of those SQLite ODBC drivers. Blue Prism is not a fully 64-bit application. Some constituent parts are still based on 32-bit libraries. When I ran a quick test of this using the 64-bit ODBC driver referenced in your image I received an exception related to this.

If you want to stick with ODBC, you'll have to set up your own code stages to work with the System.Data.Odbc objects as it appears we don't have an ODBC VBO on the DX. We can probably put one together, but it may take a few days to get to it. Otherwise, you might try looking at some of the other SQLite providers available on the internet. Here's a pretty decent example.

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

[ewilson · Accepted solution]

@VratHimbo,

I whipped up a quick ODBC VBO that I think you'll be able to use. There's not a lot to it. As I said previously, you'll need to install the 32-bit version of the SQLite ODBC drivers , which are available here.

The VBO exposes 4 actions:

• Open - Use this to connect to your ODBC data source.
• Close - Use this when you're done to clean up resources.
• Execute - Use this to execute a query that returns a result set (ex. SELECT * FROM Table123)
• ExecuteNonQuery - Use this to execute a query that does not return a result set (ex. INSERT INTO ....)

Also, the connection string for SQLite in this case should actually look something like this:
"DSN=SQLite3 Datasource;Database=C:\TestDB.db;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;" ​Notice the use of the DSN name instead of the driver name.

NOTE: This VBO was built in v6.8. If you're running an older version of Blue Prism (pre-v6.7 I believe) let me know and I'll get it converted. Otherwise, you might run into the issue of all the stages being stacked on top of each other in Object Studio.​

Once we have some time to put together an actual users guide, we'll get this posted to the DX.

Hope this helps.

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

[VratHimbo]

Hi  @ewilson,
​​thank you very much, that's just what I needed, and I really believe that it will be helpful to someone else in the future.
I have tested it and it works perfectly in BP 6.8; I would also need the xml for the version 6.6, if possible or have instructions on how to convert it so I can try to do it myself.

I don't know if I can be useful to write a guide, I would be really happy to help.

Thanks again,
A

------------------------------
andre a
------------------------------

[ewilson]

@VratHimbo I've converted it and submitted it to the DX. It should be published in the next day or so.

In the mean time you can access the VBO from GitHub.

Cheers,
​

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

[VratHimbo]

Hi @ewilson,
just another question for you, is possible to include sqlite3 dll in your object without installing the 32 bit driver?
Because i've tried but unfortunately i'm not able to read the external reference like C:\sqlite3\sqlite3.dll, i don't have any admin permission so i need to read the dll from desktop or C.

Thanks in advance​

------------------------------
Vrat Himbo
------------------------------

[ewilson]

@VratHimbo,

I'm not sure I follow. Are you able to get the 32-bit DLL installed on the system at all? If so, you can pick the installation folder from the installer script. You should require admin permissions to read/load the DLL when you run the VBO.

From the VBO perspective, there's nothing we can do to include the VBO in the DLL.

Cheers,
​

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

[ewilson]

FWIW - There is a Microsoft.Data.Sqlite library available on NuGet. It's an ADO.NET abstraction for SQLite. You might have better luck with it if ODBC continues to be an issue for you.

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------