I'm left wondering if something changed to the Retool platform. We have a tool that is used in production by employees to register their work. It has been working flawlessly since september. This morning however I had a call from one of the staff on the working floor reporting that the personnel is unable to submit their registrations.
I've been looking into this problem since.
People need to input some basic information and then just press a submit button. On submit an insert query is launched and I inject form values, view state info and sub query results into the insert query.
This has worked perfectly for me since the start, however since today it is no longer working.
In case I'm inserting an Id from a textbox input field or from a selected item of a combobox I get a "Validation failed for parameter 'param1'. Invalid string." error when running the insert statement.
I've run the query result with and without quotes directly on the server and it just works. I'm really confused why it just doesn't go through.
The UI
The insert action
-- Validation failed for parameter 'param1'. Invalid string.
INSERT INTO dbo.ActionRegistrations ([OrderId],[UserId],[Vin],[LicensePlate],[Make],[Model],[Created],[CreatedBy],[Modified],[ModifiedBy],[ExtraDirty],[IsTotalLoss],[IsCommercialVehicle],[WiperFluid],[DeStickeringAmount],[DestickeringPanels],[PolishingPanels],[ActionType],[ProcessedInTms],[CreatedDate],[BoosterAmount]) VALUES
(
{{actionTypeTabs.currentViewIndex === 1 && radioGroupCleaningRent.value==='Alphabet' ? -1 : getCarDetails.data.OrderId}},
{{getRegistrationUser.data.ID}},
{{actionTypeTabs.currentViewIndex === 1 && radioGroupCleaningRent.value==='Alphabet' ? 'No vin' : getCarDetails.data.Vin}},
{{actionTypeTabs.currentViewIndex === 1 && radioGroupCleaningRent.value==='Alphabet' ? crLicensePlateInput.value : getCarDetails.data.LicensePlate}},
{{actionTypeTabs.currentViewIndex === 1 && radioGroupCleaningRent.value==='Alphabet' ? 'No make' : getCarDetails.data.Make}},
{{actionTypeTabs.currentViewIndex === 1 && radioGroupCleaningRent.value==='Alphabet' ? 'No model' : getCarDetails.data.Model}},
GetDate(),
{{getRegistrationUser.data.UserName}},
null,
null,
{{ actionTypeTabs.currentViewIndex === 1 ? crExtraDirty.value : actionTypeTabs.currentViewIndex === 2 ? scExtraDirty.value:null }},
{{ actionTypeTabs.currentViewIndex === 0 ? bpIsTotalLoss.value : null }},
{{ actionTypeTabs.currentViewIndex === 1 ? crIsCommercialVehicle.value : null }},
{{ actionTypeTabs.currentViewIndex === 1 ? crWiperFluid.value : null }},
{{ actionTypeTabs.currentViewIndex === 3 ? dsStickersRemovedAmount.value : null }},
{{ actionTypeTabs.currentViewIndex === 3 ? dsStickersPanelCount.value : null }},
{{ actionTypeTabs.currentViewIndex === 2 ? scPolishingPanels.value : null }},
{{ actionTypeTabs.currentViewIndex + 1}},
0,
GetDate(),
{{ actionTypeTabs.currentViewIndex === 4 ? bAmount.value : null }}
)
-- Validation failed for parameter 'param3'. Invalid string.
INSERT INTO dbo.ActionRegistrations ([OrderId],[UserId],[Vin],[LicensePlate],[Make],[Model],[Created],[CreatedBy],[Modified],[ModifiedBy],[ExtraDirty],[IsTotalLoss],[IsCommercialVehicle],[WiperFluid],[DeStickeringAmount],[DestickeringPanels],[PolishingPanels],[ActionType],[ProcessedInTms],[CreatedDate],[BoosterAmount], [CompoundId]) VALUES
(
CASE WHEN '{{actionTypeTabs.currentViewIndex}}' = '1' AND '{{radioGroupCleaningRent.value}}' = 'Alphabet' THEN -1 ELSE '{{getCarDetails.data.OrderId}}' END,
'{{getRegistrationUser.data.ID}}',
CASE WHEN '{{actionTypeTabs.currentViewIndex}}' = '1' AND '{{radioGroupCleaningRent.value}}' = 'Alphabet' THEN 'No vin' ELSE '{{getCarDetails.data.Vin}}' END,
CASE WHEN '{{actionTypeTabs.currentViewIndex}}' = '1' AND '{{radioGroupCleaningRent.value}}' = 'Alphabet' THEN '{{crLicensePlateInput.value}}' ELSE '{{getCarDetails.data.LicensePlate}}' END,
CASE WHEN '{{actionTypeTabs.currentViewIndex}}' = '1' AND '{{radioGroupCleaningRent.value}}' = 'Alphabet' THEN 'No make' ELSE '{{getCarDetails.data.Make}}' END,
CASE WHEN '{{actionTypeTabs.currentViewIndex}}' = '1' AND '{{radioGroupCleaningRent.value}}' = 'Alphabet' THEN 'No model' ELSE '{{getCarDetails.data.Model}}' END,
GetDate(),
'{{getRegistrationUser.data.UserName}}',
null,
null,
CASE WHEN '{{actionTypeTabs.currentViewIndex}}' = '1' THEN '{{crExtraDirty.value}}' WHEN '{{actionTypeTabs.currentViewIndex}}' = '2' THEN '{{scExtraDirty.value}}' ELSE null END,
CASE WHEN '{{actionTypeTabs.currentViewIndex}}' = '0' THEN '{{bpIsTotalLoss.value}}' ELSE null END,
CASE WHEN '{{actionTypeTabs.currentViewIndex}}' = '1' THEN '{{crIsCommercialVehicle.value}}' ELSE null END,
CASE WHEN '{{actionTypeTabs.currentViewIndex}}' = '1' THEN '{{crWiperFluid.value}}' ELSE null END,
CASE WHEN '{{actionTypeTabs.currentViewIndex}}' = '3' THEN '{{dsStickersRemovedAmount.value}}' ELSE null END,
CASE WHEN '{{actionTypeTabs.currentViewIndex}}' = '3' THEN '{{dsStickersPanelCount.value}}' ELSE null END,
CASE WHEN '{{actionTypeTabs.currentViewIndex}}' = '2' THEN '{{scPolishingPanels.value}}' ELSE null END,
'{{actionTypeTabs.currentViewIndex}}' + 1,
0,
GetDate(),
CASE WHEN '{{actionTypeTabs.currentViewIndex}}' = '4' THEN '{{bAmount.value}}' ELSE null END,
1
)
The values used inside the insert query (obfuscated obviously)
- actionTypeTabs.currentViewIndex: 2
- getCarDetails.data.OrderId: 1808847
- getRegistrationUser.data.ID: 12345
- getCarDetails.data.Vin: "WZ....76"
- getCarDetails.data.LicensePlate: "1XXX123"
- getCarDetails.data.Make: "Make"
- getCarDetails.data.Model: "Model"
- getRegistrationUser.data.UserName: "Username"
- scExtraDirty.value: false
- scPolishingPanels.value: 0
Table definition (T-SQL)
I started a sql server profiling session to see what was sent over to the server. In case of this insert script, it never arrives at the server other scripts do arrive without problem.
The first two entries come when executing a select query, the third line is what comes through when trying to execute the insert statement
First try mitigation
Because the error states that "Validation failed for parameter 'param1'. Invalid string." I thought, maybe I need to cast these to bigint explicitly (see sp_help output as the id fields are bigints). But to no avail.
Second try mitigation
I tried to use the AI Helpbot to fix the problem. The bot says "Errors fixed". Obviously it's not fixed, because otherwise I'd have closed the issue already.
After multiple AI fix my code runs the code got converted by pulling out the inline if statements out of the javascript brackets into t-sql and further more all javascript value brackets were put in quotes. The AI says the problem is fixed, but of course when running it will keep on saying that there still is a problem "Validation failed for parameter 'param3'. Invalid string." So the only progress made is that the problem identified is now input parameter 3 instead of 1 or 2
Third try mitigation
I just created a new query with a simple insert statement where all values are filled in manually. That one comes through on sql server profiler and the record is saved successfully.
Once I substitute the first id with the results of the getCarDetails query (by calling getCarDetails.data.OrderId)
App json export
PDI Registration.json (149.1 KB)
Actual solution
I solved it by adding a .toString() after each referenced value of the referenced queries
I still have some questions
- What changed on your end that necessitates this change?
- Was this change communicated and where?
If this was the result of an announced change I absolutely missed it. If not I really would like you to improve upon the current communication surrounding possible breaking changes.
Issue resolved.
