How to send a DataTable/Array/List from SpringBoot API to SingleStore Stored Procedure

Off-Topic
1

Hi Team,

My project uses MemSQL Database to connect through our Java Spring Boot API.
We have a requirement to save list of records in one of the table in MemSQL database.
So for that we have to write a stored procedure which can take this list/array/datatable as input parameter, but we are not sure which data type supports this in MemSQL.
Can some one please help us achieve this functionality.

This is Table create script:
CREATE TABLE Dummy_Table (

ID bigint(20) NOT NULL AUTO_INCREMENT,

LOB varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

Rpt_LOB varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

L4_Tax varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

IsSelected int(11) DEFAULT 0,

Load_Date datetime DEFAULT NULL,

PRIMARY KEY (ID),

KEY LOB (LOB,Rpt_LOB,L4_Tax)

) AUTO_INCREMENT=112 /*!90623 AUTOSTATS_CARDINALITY_MODE=PERIODIC, AUTOSTATS_HISTOGRAM_MODE=CREATE / /!90623 SQL_MODE=‘STRICT_ALL_TABLES’ */;

And we want to create a stored procedure which will take the list/array/datatable as input and in that stored procedure we will write the logic to insert/update all the records in the above table in one transaction.

Any help will be appreciated. :slight_smile:

2

First, I’d consider using a query-type value (QTV) as an argument to the SP that will be digesting and processing and saving the information. If that doesn’t work, consider using an array of records as an argument to the SP. Another possibility, is to use a JSON object that is a JSON array of records. You could crack that open with the new TABLE() function and JSON_TO_ARRAY(). See also INSERT_ALL() to put the contents of an array into a table faster than a traditional loop with row-at-a-time inserts.

Here are some relevant documents: