MySQL Serverless RDSDataService.batchExecuteStatement Syntax error on Table insert

Question

I need to load data from a json file onto the MySQL Database Table called Locality. The code below returns a MySQL syntax error. MySQL version 5.7.30 for Aurora MySQL Serverless. I am using the aws-sdk, importing all the libraries, creating the RDS instances. But for some reason it returns a SQL syntax error.

It would also help if i could see what the rds.batchExecuteStatement produces as SQL. I've tried but haven't been able to find what the SQL statement is. (help?)

I've translated python to javascript from the following AWS Documentation Using the Data API for RDS Serverless

Quick link to review the batchExecuteStatement function: RDS.batchExecuteStatement Docs

const loadLocalities = () => {

    // Read data from the json file
    const data = require('./GeoDyDB.json')

    // Extract the data from the JSON into a format RDSDataService.batchExecuteStatement expects for the parameterSets argument
    const localityParameterSets = data.map(function(location){
        return [
            {name: 'code', value: {stringValue: generate_locality_sort_key(location)}},
            {name: 'synonyms', value: {stringValue: location.formatted_address}},
            {name: 'country', value: {stringValue: location.Country}},
            {name: 'state', value: {stringValue: location.State}},
            {name: 'city', value: {stringValue: location.City}},
            {name: 'zone', value: {stringValue: location.Zone}},
            {name: 'ward', value: {stringValue: location.Ward}},
            {name: 'colony', value: {stringValue: location.Colony}},
            {name: 'pincode', value: {isNull: true}},
            {name: 'lat', value: {stringValue: location.Lat.toString()}},
            {name: 'lng', value: {stringValue: location.Lng.toString()}},
            {name: 'geohash', value: {stringValue: location.Country}},
            {name: 'obj', value: {stringValue: location.Country}}
            /* more columns */
        ]
    })




    // Create the SQL statement to run for all the items to insert as per AWS Docs linked above
    sqlStatement = `INSERT INTO Locality (code, synonyms, country, state, city, zone, ward, colony, pincode, lat, lng, geohash, obj) VALUES (:code, :synonyms, :country, :state, :city, :zone, :ward, :colony, :pincode, :lat, :lng, :geohash, :obj);`


    var params = {
        resourceArn: 'arn:aws:rds:...xxx', /* required */
        secretArn: 'arn:aws:secretsmanager:...xxx', /* required */
        sql: sqlStatement, /* required */
        database: 'mydb',
        parameterSets: localityParameterSets
    };
    rds.batchExecuteStatement(params, function(err, data) {
    if (err) console.log(err, err.stack); // an error occurred
    else     console.log(data);           // successful response
    });
}

This returns the following error

BadRequestException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('loc/ncr-newdelhi#patparganj@acharyaniketan', 'Acharya Niketan, Mayur Vihar, Ne' at line 2
    at Object.extractError (/Users/angad737/Dropbox/Breadwinner/App Dev Data/Database Folder/GeoDynamoDB/node_modules/aws-sdk/lib/protocol/json.js:52:27)
    at Request.extractError (/Users/angad737/Dropbox/Breadwinner/App Dev Data/Database Folder/GeoDynamoDB/node_modules/aws-sdk/lib/protocol/rest_json.js:55:8)
    at Request.callListeners (/Users/angad737/Dropbox/Breadwinner/App Dev Data/Database Folder/GeoDynamoDB/node_modules/aws-sdk/lib/sequential_executor.js:106:20)
    at Request.emit (/Users/angad737/Dropbox/Breadwinner/App Dev Data/Database Folder/GeoDynamoDB/node_modules/aws-sdk/lib/sequential_executor.js:78:10)
    at Request.emit (/Users/angad737/Dropbox/Breadwinner/App Dev Data/Database Folder/GeoDynamoDB/node_modules/aws-sdk/lib/request.js:688:14)
    at Request.transition (/Users/angad737/Dropbox/Breadwinner/App Dev Data/Database Folder/GeoDynamoDB/node_modules/aws-sdk/lib/request.js:22:10)
    at AcceptorStateMachine.runTo (/Users/angad737/Dropbox/Breadwinner/App Dev Data/Database Folder/GeoDynamoDB/node_modules/aws-sdk/lib/state_machine.js:14:12)
    at /Users/angad737/Dropbox/Breadwinner/App Dev Data/Database Folder/GeoDynamoDB/node_modules/aws-sdk/lib/state_machine.js:26:10
    at Request.<anonymous> (/Users/angad737/Dropbox/Breadwinner/App Dev Data/Database Folder/GeoDynamoDB/node_modules/aws-sdk/lib/request.js:38:9)
    at Request.<anonymous> (/Users/angad737/Dropbox/Breadwinner/App Dev Data/Database Folder/GeoDynamoDB/node_modules/aws-sdk/lib/request.js:690:12) {
  code: 'BadRequestException',
  time: 2020-10-19T05:28:35.734Z,
  requestId: '9e57d418-1018-49e0-a5bd-717ab1cf3ac4',
  statusCode: 400,
  retryable: false,
  retryDelay: 94.67460516360629

I ran what I expect the SQL query to be in the AWS Query editor to check for any syntax errors but this query ran successfully.

INSERT INTO Locality (code, synonyms, country, state, city, zone, ward, colony, pincode, lat, lng, geohash) VALUES 
('loc/ncr-newdelhi#patparganj@acharyaniketan', 
 'Acharya Niketan, Mayur Vihar, New Delhi, Delhi 110091',  
 'India',
'New Delhi',
'New Delhi',
'Shahadra South',
'Patparganj',
'Acharya Niketan',
'undefined',
'28.6081393',
'77.29474669999999',
'India');

At first I thought maybe it could be the 'undefined' as a reserved keyword but it wasn't. I feel like I'm missing something obvious because it should be a syntax error but I just can't spot it. Be my StackOverflow Angel.

Thanks

Answer 1

You forgot to supply your SQL query (sqlStatement ) into the rds.batchExecuteStatement function, you are giving it the parameters, but there is no SQL code to apply it to.

Answer 2

I ran into the same issue. It took hours of debugging to find that you cannot include the semicolon at the end of your sql statement...

Answer 3

For people arriving here looking for answers to problems with BatchExecuteStatementCommand, just a reminder that the value for an integer is a longValue (see https://docs.aws.amazon.com/AWSJavaScriptSDK/latest/AWS/RDSDataService.html#batchExecuteStatement-property) in your parameterSets. Also remember to add the 'JSON' type hint and pass it as stringValue into the RDS, this works remarkably well.

The BatchExecuteStatementCommand appears to have no set limit on the number of commands but it should be subject to the 4MB input limit, so worth thinking about whether you might exceed that.

Happy batching!