topic Sqlite connection in Product Forum

Sqlite connection

VratHimbo — Fri, 09 Oct 2020 09:49:00 GMT

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
------------------------------

RE: Sqlite connection

ewilson — Fri, 09 Oct 2020 11:39:00 GMT

@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
------------------------------

RE: Sqlite connection

VratHimbo — Fri, 09 Oct 2020 11:50:00 GMT

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
------------------------------

RE: Sqlite connection

ewilson — Fri, 09 Oct 2020 13:53:00 GMT

@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
------------------------------

RE: Sqlite connection

ewilson — Fri, 09 Oct 2020 17:21:00 GMT

@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
------------------------------

RE: Sqlite connection

VratHimbo — Mon, 12 Oct 2020 08:33:00 GMT

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
------------------------------

RE: Sqlite connection

ewilson — Mon, 12 Oct 2020 09:59:00 GMT

@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
------------------------------

RE: Sqlite connection

VratHimbo — Mon, 16 Nov 2020 09:41:00 GMT

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
------------------------------

RE: Sqlite connection

ewilson — Mon, 16 Nov 2020 13:49:00 GMT

@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
------------------------------

RE: Sqlite connection

ewilson — Mon, 16 Nov 2020 13:52:00 GMT

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
------------------------------

RE: Sqlite connection

VratHimbo — Tue, 17 Nov 2020 08:20:00 GMT

Hi @ewilson,
thanks for the reply, in that case with microsoft.data.sqlite i don't need the odbc driver installed, is that correct?
For do that, i need to import that library on your odbc xml, the rest remains the same, is correct?

Thanks

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

RE: Sqlite connection

ewilson — Tue, 17 Nov 2020 12:15:00 GMT

@VratHimbo,

Unfortunately, it's not that simple. The Microsoft.Data.Sqlite library has its own set of required libraries. I took a quick look at it yesterday and there are several that NuGet pulled down when I compiled a test app. The only difference is that you could pull these individual DLL's yourself as opposed to having to run an install script.

Do you have permission to copy files to the Blue Prism installation folder? You would probably have to place them in that folder to make use of them. As I think most are native code.

Cheers,

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

RE: Sqlite connection

uncki2 — Mon, 10 Oct 2022 02:22:00 GMT

Hi Eric,

Good morning.
I am able to make connection to .db file using SQLite VBO. However, "Execute - Get Collection" page throws error i.e. SQL logic error no such table: <tablename> when trying to read the table in data base file.
Please see the code from SQLite VBO below. I am using BP version 6.4.

Could you please assist? Thank you

Regards,
Kishlay

------------------------------
Kishlay Singh
Senior Officer
UOB
Asia/Singapore
------------------------------

RE: Sqlite connection

ewilson — Mon, 10 Oct 2022 15:52:00 GMT

Hello @uncki2,

Have you tried using the test process included in the .bprelease? I'm not seeing any issues with Execute - Get Collection action when I use it. The error you've shown implies that the table name you're using does not exist in the database. Are you sure you have the correct table name?

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Sqlite connection

uncki2 — Tue, 11 Oct 2022 00:51:00 GMT

Hi @ewilson,

Thank you for the prompt response.
Table name is correct as I am able to query the table after tweaking the code. Please see the code which returns data as collection from the table below:

I am just wandering why the SQLite VBO is unable to recognize the table. Please let me know if you need more info.

Thank you so much 🙂

------------------------------
Kishlay Singh
Senior Officer
UOB
Asia/Singapore
------------------------------

RE: Sqlite connection

ewilson — Tue, 11 Oct 2022 14:13:00 GMT

@uncki2,

I think we figured out what the issue was. There was a bug in the creation of connection strings based on just passing in the database file location. We pushed an update of the VBO to the DX yesterday. You might give it a try if you have time.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Sqlite connection

uncki2 — Tue, 11 Oct 2022 15:39:00 GMT

Hi @ewilson,

Thank you so much for your assistance. Updated VBO is able to read the DB file.

Really appreciate your help. Cheers!

------------------------------
Kishlay Singh
Senior Officer
UOB
Asia/Singapore
------------------------------