Adding a string that is a number with a leading zero

Question and Answers
1

Okay, I’m running into the bug described at [Bug] In prepared statements, it is impossible to use numeric values with leading zero as strings · Issue #4056 · appsmithorg/appsmith · GitHub

I’m trying to run an insert query, with the iAddValue.text being the string “0447” (for example). When it gets inserted into the database, the value “447” gets inserted. This only happens if I use prepared statements, not if I turn prepared statements off. It’s identical to the issue I listed above, but that issue is marked as fixed. So I’m not sure if I need to change my query, or if it isn’t really fixed. Here’s the query I’m using (though I’ve tried several different iterations of it):

insert into keysandvalues(name, key, value) values ({{Clients.selectedRow.name}},{{iAddKey.text}}, {{iAddValue.text}}::varchar);

2

Hi Anthony,

This change of typecasting a value in Prepared Statements has only been made for Postgres database today. What database are you using? I could raise an issue and this can be tackled for the others as well.

3

I’m using postgres. I’ll check it out again later today.

Until it is fixed, I’ve found that turning off prepared statements and dollar-quoting with a long random string seems to be the workaround.

4

Thanks! This fix went out to on prem deployments 6 days ago. It would be great if you can check it out and let me know if you are still facing this.

5

Yes, it works now.

A note about the benefit of adding typecasting at Postgres - Appsmith would probably save people some time debugging this relatively rare issue.

I guess best practice will be to explicitly typecast everything?

6

Use the following two functions to solve your problem
SUBSTR()
CHAR_LENGTH()

image
image1086×357 18.4 KB

7

Hi, I have been running into this issue with MYSQL. Looks like the fix has not been implemented for that yet?

8

@trebuchet yes, this requires the use of prepared statements to be off since the underlying driver tries to identify the value as a number instead of a string

9

Ok, thanks @Nikhil - as workaround for those that want to use Prepared Statements while still using leading zeros in strings like this you can use LPAD()

10

Hi @trebuchet
Or you can use concat method to keep the leading zero intact in your query like below (with Prepared Statement: ON)

SELECT 
*
FROM your_table
WHERE col = CONCAT('0', {{Input.text}}) -- e.g. Input.text = "097363"

Created a separate GitHub issue for MySQL. Here’s the GitHub issue link for the ease of your tracking.

11

I know in Postgres I can override the type by putting ::varchar at the end, but this doesn’t work in MS SQL. Is there a similar workaround for MS SQL?

12

To elaborate on the question I posed a few days ago, I have a field that is a two character string. Here are some examples of values:

03
09
12
13
14
H8
J1
Z1
...

If I use a prepared statement, ‘03’ and ‘09’ will be inserted/updated as ‘3’ and ‘9’ respectively.

In Postgres, I could have simply done this:

UPDATE form
  SET code = {{input_code.selectedOptionValue}}::varchar
WHERE id = {{appsmith.store.payrollFormId}}

But ::varchar doesn’t work in MS SQL. Is there another way to explicitly typecast? I don’t want my insert/update scripts to be filled with messy CASE statements.

Please help!

13

You could try forcing a string by surrounding the binding with quotes or by casting the value. Would you try one of these and see if they help?

UPDATE form
  SET code = CAST({{input_code.selectedOptionValue}} AS VARCHAR(2))
WHERE id = {{appsmith.store.payrollFormId}}

Or

UPDATE form
  SET code = '{{input_code.selectedOptionValue}}'
WHERE id = {{appsmith.store.payrollFormId}}
14

@jdufault Have you tried the workaround suggested in this comment?

16

CONCAT(‘0’, {{Input.text}}) works only if the value is one character in length.

Example:

3 --> 03
9 --> 09
12 --> 012
13 --> 013
14 --> 014
H9 --> 0H9
J1 --> 0J1
Z1 --> 0Z1
17

I’ve tried both of your suggested solutions, but neither work. If there is a leading zero in the value, it is stripped before being sent to MS SQL. Appsmith typecasts the value as a number before the SQL script is even executed.

18

Hi @jdufault
To be precise CONAT('0', {{Input.text}}) works when there is only one leading zero in your column. Can you please tell us how many zeros you might need to add as prefix? Can you try with the solution using LPAD as mentioned in this comment?

In your case it would be

LPAD({{Input.text}}, 2, '0')

19

Hi @subratadeypappu

Thanks for your reply. LPAD is not a valid function in MS SQL Server, so that won’t work.

My point is that I do not necessarily know if or how many leading zeros will be needed. This is a two-character alphanumeric field. In Appsmith, the values that need a leading zero already have one. it’s just that Appsmith converts them to a number and removes the leading zero prior to execution.

Please again look at the following example data:

03
09
12
13
14
H9
J1
Z1
20

Yes, I totally get your point. That’s an issue in Appsmith. This soon will get resolved. What I have proposed is a workaround for this (Prepared Statemtnt: OFF).

By the way, LPAD doesn’t come out-of-the-box in SQL-Server. You can check this Stackoverflow post.

But this should work when you turn off the prepared-statement.

21

Yes, I am aware that I can turn off Prepared Statements. But understandably, I want to try every available option before doing that.

I will look at adding my own LPAD function in SQL Server as a workaround. But ultimately I’m really looking forward to seeing an actual resolution to this problem. Imagine, for example, a field that doesn’t have a fixed length and some values contain leading zeros.