Inserting records into PostgreSQL using standard custom_query

Help
,
1

Hi,

I’m currently attempting to utilize Execute Custom Query on PostgreSQL to perform an new record inserts.

My source is API data coming from a standard HTTPS webhook (previous step in workflow).

I’ve able to successfully configure an UPDATE query using a custom_query step, but on attempts to performs inserts, I keep coming up with the same error:

Error The number of values provided does not match the number of values in the query.
at Object.run (file:///opt/ee/c_y1fjPnVJ/user/actions/execute-custom-query/execute-custom-query.mjs:43:13)
at global.executeComponent (/var/task/launch_worker.js:142:53)
at MessagePort.messageHandler (/var/task/launch_worker.js:614:28)

The configuration I am doing does not seem to suggest I am doing this, but alas I have looked at database logs that indicate the error is accurate. It seems I am doubling (via duplication) the number of values being put into the insert query string in the end.

Here is a screen shot of my current configs for the step:

image
image1295×655 30.8 KB

The trick is that the API is sending a varying number of values for custom fields within the record coming in (from Insightly CRM), based on what fields were populated during object record creation.

I am using a node.js function to determine the number of custom fields included and creating both fields and values strings (comma-separated). This is going into a variable that is passed via $return_value - hence the refs in the custom query fields.
[1] = fields list
[2] = values list

The lengths of these lists are the same, but I’m thinking that referencing the values list in the string and the values field is my issue - I just don’t know how to get around that.

Appreciate any timely insights!

2

Hi @superorbi

You’re very close. I think you’re just slightly misconfiguring the SQL Query prop in the first step.

The help text says:

Your custom SQL query using `$1`, `$2` ... to represent values.

But you’re passing the step export value of steps.nodejs.$return_value[1] and steps.nodejs.$return_value[2] respectively.

Have you tried removing those references and simply passing in $1 an $2 to represent that 2 values are being inserted into the table?

Second, the Values prop only has 1 value defined, perhaps try adding the second variable there.

3

Very good point on the values portion of the query string itself - definitely should insert the correct number of placeholder variables (or whatever they should be called) that correspond to the number of values.

The variable in the values field (same as in the string) represents a compiled list of values (within a string) as opposed to one - seems to be the only way to do it since the number of values to include is variable. (sadly)
This approach did work for putting together an update query, but perhaps the mechanics are different.

Going to start with updating the query and see where that takes me.

Thanks a bunch for the reply!

1 Like
4

UPDATE - this morning I was able to fully implement the change in the query string - inserting the placeholders ($1, $2, etc).

When doing that along with some field name cleanup in the query string scripting, it TOTALLY WORKS!!

So again, thanks for that catch - given the somewhat unique requirements for this effort, I’m definitely planning to put out a separate post in “Show and tell” to hopefully benefit a few others - something I haven’t been able to do often enough.

For now - onto to the next challenge. :+1: :fist_right: :muscle:

1 Like
5

Hi @superorbi amazing! Glad you were able to figure it all out :slight_smile: that’s the best feeling right?

Yes please let us know the blog post / social media post you make, we’d be happy to promote it.