I'm having an issue trying to BulkInsert data to a Mysql (5.7.23-23) DB from an Excel File. There is an AUTOINCREMENT ID column in the table. Already troubleshoot through several Column Name length issues, but now it's sending this Exception. I've tried ignoring that column in the ImportSpreadSheetFile_P_TV Server Action (ColumnsToIgnore), even tried putting the name of the temporary entity and the actual Entity before the ID column name:
"ID"
"Puntuaciones_TV.ID"
"Excel_PuntuacionesTV.ID"
I'd appreciate any suggestion as to what should be a way to solve the issue. The table is around 44 columns. Best regards.
Ed
System.InvalidOperationException: Parameter '@id' was not found during prepare. at MySql.Data.MySqlClient.PreparableStatement.Prepare() at MySql.Data.MySqlClient.MySqlCommand.Prepare(Int32 cursorPageSize) at OutSystems.NssBulkInsert.CssBulkInsert.MySqlBulkInsert(IOSList recordList, String ssTableName, String ssColumnsToIgnore, Dictionary`2 recordFields, String Catalog, String ExternalDatabaseConnectionName) at OutSystems.NssBulkInsert.CssBulkInsert.MssBulkInsert(Object ssRecordList, String ssTableName, String ssColumnsToIgnore, String ssReferenceColumns, Boolean ssUseTableLock, String ssCatalog, String ssExternalDatabaseConnectionName)
Hello Eduardo,
It seems you include name of ID column into insert statement but not passing ID value or parameter into values.
If you can share a screenshot of insert statement or your action this may help to identify the issue
Hello Mostafa,
Thank you for your answer. This is what it's showing right before teh BulkInsert. It does have an ID column, but I have even work through the assignment to make sure ColumnsToIgnore and the rest of the Variables get passed to BulkInsert Server Action. On the ListAppendAll Mapping I'm passing NullIdentifier() to the ID field. Thanks once again.
I have set the structure and all to Bulkinsert the data, but I'm still unable to get pass that same error about the @id parameter (Which is the name of the first column). While debugging I can see that the dataset has the data, but for some reason it still sends the same error. The logs don't show anything else. If anyone has come across this type of error, I'd appreciate any input. Best regards.
I removed the ID from the Structure, and I get the same error, but with the next field, FECHA. Apparently t
Found this at Stackoverflow. Could this be a bug in MySql.Data, could it be related to this? I'd appreciate any help. Cheers.
Hi @Eduardo M ,
You did the right thing by removing ID from structure as BulkInsert does not work with Id if its auto increment. Do try these below options:
1. Hardcode physical table name in TableName property, without using schema name.
2. UseTableLock: True
3. Check data types thoroughly for each attribute of your structure and entity with physical table attributes, specially for Fecha first.
Hope this may help!
Hello @Sanjay Kumar Sahu,
Thanks for the input, still getting the same error. Can't get past that message.
Hi Eduardo,
I've also been having a lot of problems entering data into a MySql Database... I have a small table with an auto-incremental ID...
I was only able to solve my problem when I decided NOT to ignore the ID column and when I mapped my data list (to the List of records to be introduced into the Database) I decided to give a value of: NullTextIdentifier() instead of Nullidentifier( ).
It worked perfectly for me, I hope it works for you.Rui
Hello @Rui Fonseca,
I had to use the structure to organize my fields as they were not coming in the proper order. Dataset is assigned in that order.
Thanks for your input Rui.
Hello,
Is there anyway I can debug what's happening withing the Bulkinsert server action? Is there a way to know what query is being sent out? I'd really appreciate any help. Thanks.
Best regards,
Hello Eduardo.
When I sent my first suggestion I was working on a client-side action and I was using a structure that represented the record of the table I was working on.
However, in order to improve performance and security I decided to "pass" everything to a server action. And the problems started again...
After a lot of time, testing and research I discovered that the structure that was working perfectly on the client side no longer worked on the server side.
When I switched to direct table registration (without using the structure) everything worked perfectly. In other words, the "Record" must be of the "table data type".
On the image, the left side works and in the right side don´t (only on client-actions)
I can't explain the platform's behavior in this situation because I've used structures multiple times and it always worked...
I hope you can resolve your problem.