topic RE: retrieve data from a table in a Blue Prism database using OLEDB's VBO in Product Forum

retrieve data from a table in a Blue Prism database using OLEDB's VBO

sumire — Thu, 14 Dec 2023 09:41:00 GMT

Hello,

I am trying to retrieve data from a table in a Blue Prism database using OLEDB VBO.
For example, if I try to retrieve the contents of the BPAUser table, how should I write the SELECT statement?
The SQL statement "SELECT * FROM BPAUser" gave me an error.

Thanks for all your help,

------------------------------
Mitsuko
Asia/Tokyo
------------------------------

RE: retrieve data from a table in a Blue Prism database using OLEDB's VBO

Walter.Koller — Thu, 14 Dec 2023 13:23:00 GMT

BP database is no different to any other SQL Server database.

If you could share the exact error message and the configuration and usage of the OLEDB VBO we might help you better.

But first... does the user that is used in DB connection have select rights on BPA tables?

------------------------------
Walter Koller
Solution Manager
Erste Digital / Erste Group Bank
Europe/Vienna
------------------------------

RE: retrieve data from a table in a Blue Prism database using OLEDB's VBO

Rambo27 — Thu, 14 Dec 2023 13:30:00 GMT

Hi Mitsuko,

Check this video to OLEDB operations.

How to use OLEDB in Blue Prism | Detailed Explanation of OLEDB | Actions of OLEDB VBO - YouTube

------------------------------
Shikhar Mishra
RPA Lead
Infosys
------------------------------

RE: retrieve data from a table in a Blue Prism database using OLEDB's VBO

sumire — Thu, 14 Dec 2023 23:35:00 GMT

Hello @Walter Koller,

Thank you for your response.

user ID and password combination has the correct permissions to the DB.
This is because this ID and password combination allows me to manipulate tables in SSMS.

Blue Prism Process worked correctly until Set Connection and open. the 'select' statement gave me an error.

------------------------------
Mitsuko
Asia/Tokyo
------------------------------

RE: retrieve data from a table in a Blue Prism database using OLEDB's VBO

Walter.Koller — Fri, 15 Dec 2023 09:51:00 GMT

Hi @sumire

Please make a screenshot or copy/paste the exact error message. This will help a lot to resolve the problem.

Until then my next guess would be a wrong/missing default database for this user.

Try if full qualified table names work. Instead of

select * from BPAUser

try

select * from <databasename>.<owner>.BPAUser

(<owner> is normally dbo)

You can also add a default database in the connection string by adding Database=<databasename>; to the string.

Microsoft OLE DB Driver for SQL Server Connection Strings - ConnectionStrings.com

There is also a VBO specifically to MS SQL Server on DX that may avoid some configuration and driver issues. (and may add some others though :D)

------------------------------
Walter Koller
Solution Manager
Erste Digital / Erste Group Bank
Europe/Vienna
------------------------------

RE: retrieve data from a table in a Blue Prism database using OLEDB's VBO

sumire — Mon, 18 Dec 2023 05:20:00 GMT

Hello @Walter Koller,

Thanks for your reply.

For example, if the database structure is as shown in the following figure,

select * from Dev.dbo.BPAAlertEvent

The contents of the BPAAlertEvent table should be retrieved.
However, when I issue such a command in OLEDB VBO's 'Get collection',
I get an error that says "Internal : Could not execute code stage, System.Guid cannot be converted to Blue Prism data type".

(Note: The error text is in Japanese, so the English translation may not match the error text in the English version.)

In this thread,

https://community.blueprism.com/communities/community-home/digestviewer/viewthread?MessageKey=a25576f7-9615-4f5e-8441-2b22622ee48d&CommunityKey=1b679a41-ef27-4557-a2dd-9af040812654&tab=digestviewer#bmf1f539a2-017e-408e-b9ba-264e28c5861e

@Dave Morris recomends the SQL statement "SELECT * FROM BPAScheduleTrigger".
However, it doesn't work neither "select * from BPASchedule" nor "select * from dbo.BPASchedule" nor "select * from Dev.dbo.BPASchedule".

------------------------------
Mitsuko
Asia/Tokyo
------------------------------

RE: retrieve data from a table in a Blue Prism database using OLEDB's VBO

Walter.Koller — Mon, 18 Dec 2023 08:37:00 GMT

Hello @sumire

Thanks for translating the error message 🙂 There seem to be several issues.

GUID is a very specific database data type and does not exist as unified data type across programming languages and therefore is not automatically converted from DB data type to local data type.

UID columns can be spotted by their data types:

Those columns do have little meaning on their own and are normally not part of a query result (except for some specific cases).

A query with explicitly names columns and omitting columns with data type uniqueidentifier should work.

In case you need those columns in the result set you have to convert them. eg:

SELECT CAST(processid as VARCHAR(50)) FROM BPAProcess

However, the other tables do not have GUID columns and there seem to be another issue. There should be another error message.

I normally stay within SSMS to write a query until it does what I expect.

Getting the GUID conversion error means the OLEDB connection actually works. You can also use queries like those to verify the connection works technically:

select getdate()

select username from BPAUser

------------------------------
Walter Koller
Solution Manager
Erste Digital / Erste Group Bank
Europe/Vienna
------------------------------

RE: retrieve data from a table in a Blue Prism database using OLEDB's VBO

sumire — Wed, 20 Dec 2023 01:27:00 GMT

Hello @Walter Koller,

Thank you for the very clear explanation.
I now understand about the System.Guid type columns.
When I write the query, I will make sure to specify the column name explicitly after the SELECT.
For example, if I want to retrieve values from the BPALicense table:
SELECT licensekey FROM [DB name].dbo.BPALicense
That's what you mean, right?

------------------------------
Mitsuko
Asia/Tokyo
------------------------------

RE: retrieve data from a table in a Blue Prism database using OLEDB's VBO

Walter.Koller — Wed, 20 Dec 2023 09:32:00 GMT

Yes, your SELECT licensekey... is a correct example.

------------------------------
Walter Koller
Solution Manager
Erste Digital / Erste Group Bank
Europe/Vienna
------------------------------

RE: retrieve data from a table in a Blue Prism database using OLEDB's VBO

sumire — Wed, 20 Dec 2023 23:38:00 GMT

I found a similar issue on Ideas Portal.

Add support for System.Guid

I agree with this post.

This data type is difficult to understand and is an roadblock when dealing with database tables.

------------------------------
Mitsuko
Asia/Tokyo
------------------------------

Re: retrieve data from a table in a Blue Prism database using OLEDB's VBO

Pralay — Fri, 10 May 2024 09:10:19 GMT

Hi,

I am new to use smartsheet automation in our project. Can you please help me how to automate in BP. I got one release file from digital exchange but I tried to import in Release/Skill, it is navigating to import release file but after importing i am not able to find this release file in BP studio. Given below is the release file. Is this bellow file is helpful to automate smartsheet?

Can you please how to automate SmartSheet - Inserting, updating, deleting data functionality

Thanks,

Pralay