Adding a string that is a number with leading zeros into Mysql database

Question and Answers
1

I am facing this error where if I try to add a number with leading zero’s as a string into the database, it is converted to an integer. (with the prepared statement turned on). If I do the same thing with prepared query turned off, this type casting is not happening.

{
	"response":[
		0:{
			"affectedRows":1
		}
	]
	"request":{
		"requestParams":{
			"Query":{
				"value":"INSERT INTO MyTable (Name, Order, Code) VALUES ( $1, $2, $3 );"
				"substitutedParams":{
					"$1":{
						"value":"Local"
						"type":"STRING"
					}
					"$2":{
						"value":"1"
						"type":"INTEGER"
					}
					"$3":{
						"value":"0001"
						"type":"INTEGER"
					}
				}
			}
		}
	}
}

I know this issue was fixed on Posgresql. Is there any other better way to handle this? Or is there a plan to fix it on MySql?

2

@rtg Can you check if explicit typecasting in the INSERT query using
:: String with Prepared Statement on works?

3

@dancia : I tried using the cast function. But that is also not helping. Attaching the log.

{
	"response":[
		0:{
			"affectedRows":1
		}
	]
	"request":{
		"requestParams":{
			"Query":{
				"value":"INSERT INTO Vendor (Name, UIOrder, Code) VALUES ( $1, $2, CAST($3 AS CHAR) );"
				"substitutedParams":{
					"$1":{
						"value":"MOUSER 7"
						"type":"STRING"
					}
					"$2":{
						"value":"24"
						"type":"INTEGER"
					}
					"$3":{
						"value":"0024"
						"type":"INTEGER"
					}
				}
			}
		}
	}
}
4

@rtg Sorry about that. I checked with the team and unfortunately, turning off prepared statement is the only way right now to avoid this. We have created a new issue to support explicit typecasting in MySQL with prepared statement on. You can track it here - [Feature]: Add support for explicit data typecasting in MySQL prepared statement · Issue #10456 · appsmithorg/appsmith · GitHub

1 Like
5

Thanks for the update!! Hope to have it fixed soon :smiley:

1 Like