[BulkInsert] Running into problem while using upload with BulkInsert

[BulkInsert] Running into problem while using upload with BulkInsert

  
Forge Component
(11)
Published on 14:36 (5 hours ago) by Marcelo Ferreira
11 votes
Published on 14:36 (5 hours ago) by Marcelo Ferreira

I have a simple upload widget with a button action. In the action, I have a excel to record list function and then the BulkInsert action. In BulkInsert action I am passing :

ToObject(ExcelToRecordList1)

as RecordList and 

"TT"

as TableName which is the table I want to insert the data in


I am getting an error saying : Cannot access destination table "TT"

Solution

Hi Rupinder,

Is TT the name of an entity in OutSystems, or the name of the table in your database?

BulkInsert expect the TableName to be the name of the table in your database, which is usually the entity's name with a prefix generated by the OutSystems platform.

If you are trying to insert into an external table, not managed by the platform, then you should check if your platform's runtime user (usually called OSRUNTIME) has permissions to access that table.

Solution

João Pedro Abreu wrote:

Hi Rupinder,

Is TT the name of an entity in OutSystems, or the name of the table in your database?

BulkInsert expect the TableName to be the name of the table in your database, which is usually the entity's name with a prefix generated by the OutSystems platform.

If you are trying to insert into an external table, not managed by the platform, then you should check if your platform's runtime user (usually called OSRUNTIME) has permissions to access that table.

Thanks João


João Pedro Abreu wrote:

Hi Rupinder,

Is TT the name of an entity in OutSystems, or the name of the table in your database?

BulkInsert expect the TableName to be the name of the table in your database, which is usually the entity's name with a prefix generated by the OutSystems platform.

If you are trying to insert into an external table, not managed by the platform, then you should check if your platform's runtime user (usually called OSRUNTIME) has permissions to access that table.

Hi João, is there a function that calls the Outsystems prefix to dynamically fill in the table name?


Raymond Kwok wrote:

João Pedro Abreu wrote:


Hi João, is there a function that calls the Outsystems prefix to dynamically fill in the table name?


There isn’t a function for that, but you can reference the Entity table in the System module and query it to find the physical table name of an entity.


Hi João,

Thank you for implementing this component. 

I’m trying to load an Excel file (~3000 records, 3 columns) from Outsystems into our on-premise SQL database.

It’s really slow (~15 minutes) to insert 3000 records into our on-premise SQL table using a for-loop, so I tried the BulkInsert module on the Forge.

However, I keep getting the error “Cannot access destination table '<table name>'”.

I’ve tried different variations of <server>.<schema>.<table name>, with no luck.

We have a user defined on our on-premise SQL database (named ‘OutSystems’), and I’ve verified that this user is able to perform all CRUD operations on the table via our extension module. Our extension module is able to successfully use our 'OutSystems' SQL user connection. We didn't call ours 'OSRUNTIME'. I imagine this shouldn't be a problem.

Any further ideas?


Thanks,

Nick

Nicolaas Kuit wrote:

Hi João,

Thank you for implementing this component. 

I’m trying to load an Excel file (~3000 records, 3 columns) from Outsystems into our on-premise SQL database.

It’s really slow (~15 minutes) to insert 3000 records into our on-premise SQL table using a for-loop, so I tried the BulkInsert module on the Forge.

However, I keep getting the error “Cannot access destination table '<table name>'”.

I’ve tried different variations of <server>.<schema>.<table name>, with no luck.

We have a user defined on our on-premise SQL database (named ‘OutSystems’), and I’ve verified that this user is able to perform all CRUD operations on the table via our extension module. Our extension module is able to successfully use our 'OutSystems' SQL user connection. We didn't call ours 'OSRUNTIME'. I imagine this shouldn't be a problem.

Any further ideas?


Thanks,

Nick

This extension assumes you'll use the same connection the OutSystems platform is currently using, so you shouldn't need to specify a server or schema; the physical table name should be enough.

For future reference, the physical table name of a table generated by the OutSystems Platform should be something like OSUSR_2qj_BulkInsertDemo , where BulkInsertDemo is the name of the entity in Service Studio.