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

[sumire]

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

[Walter.Koller]

Hi

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

[Rambo27]

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

[sumire]

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

[Walter.Koller]

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

[sumire]

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#bm...

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

[Walter.Koller · Accepted solution]

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

[sumire]

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

[Walter.Koller]

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

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

[sumire]

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