Update sql statement

[hmandirola]

I don't have much knowledge of JAVA and I want to record from MIRTH in a database with sql statement.
The Insert work perfect but does not happen the same with the update
use the following code in the insert

INSERT INTO Patient (Apellido,Nombre)
VALUES (${maps.get('Apellido')}, ${maps.get('Nombre')})

and for the update

UPDATE Patient
SET Patient.Apellido = (${maps.get('Apellido')}, Patient.Nombre = ${maps.get('Nombre')})
Where Patient.PID = ${maps.get('PID')})

could someone give me a hand?
Thanks in advance

[jonbartels]

How does the UPDATE fail?
Are there errors?
Does it simply not update?

[hmandirola]

Dear jonbartels
Thanks for answering, it just gives me an Error message: Failed to write to the database does the channel deploy correctly ... Basically, I use everything the same as for the insert that works correctly when I want to do the update, it fails
Kind Regards

[rjkroll]

Can you show us your actual code from the transformer step to see what you are doing to make sure your other syntax/etc is correct?

[hmandirola]

Dear rjkroll

Thanks for answering, I copy you the complete channel, but I use the same transformer as when I put the insert code without problems
Best regards

[hmandirola]

[jonbartels]

🤔 that should work

Are there errors in mirth.log?

If you view the message and look at the "Mappings" do you see values for PID, Apellido, and 'Nombre?

What are the column types for the Patient table? This could be MC trying to cast strings to numeric SQL types.

[hmandirola]

Apellido is Family Name in English an Nombre is Name Given, both are string. I see without problems the mapped variables in fact when I change for an insert it inserts them without any problem
INSERT INTO Patient (Last Name, First Name)
VALUES ($ {maps.get ('Surname')}, $ {maps.get ('Name')})
But the same does not happen when I put the SQL code of Update
UPDATE Patient
SET Patient.Last Name = ($ {maps.get ('Last Name')}, Patient.Name = $ {maps.get ('First Name')})
Where Patient.PID = $ {maps.get ('PID')})

[jonbartels]

What datatype is Patient.PID in your database? Is it numeric or varchar?

If the object in the MC map is a string and the column is numeric you might be getting a cast exception.

Maybe try:
UPDATE Patient SET Patient.Last Name = ($ {maps.get ('Last Name')}, Patient.Name = $ {maps.get ('First Name')}) Where Patient.PID = $ {maps.get ('PID')})::BIGINT

Either mirth.log or your database logs will tell you what the problem with the query is.

If this isn't helping maybe chatting in MC Slack would help solve the question. https://mirthconnect.herokuapp.com/

[hmandirola]

Hi
the database is MS SQL tonygermano advise me to use

UPDATE Patient
SET Patient.Apellido = ${Apellido}, Patient.Nombre = ${Nombre}
Where Patient.PID = ${PID}

and this worked perfectly
Thanks

[tonygermano]

Pretty sure the problem is your parentheses. That is not valid SQL. You can also shorten your code by skipping the maps.get, which isn't necessary for your variable names.

UPDATE Patient
SET Patient.Apellido = ${Apellido}, Patient.Nombre = ${Nombre}
Where Patient.PID = ${PID}

[hmandirola]

Yes, this worked perfectly
UPDATE Patient
SET Patient.Apellido = ${Apellido}, Patient.Nombre = ${Nombre}
Where Patient.PID = ${PID}
Thanks