SQL Select to State

1

Hi all – I’m struggling to understand the SQL() sequence, for language-postgresql.

I want to retrieve an ID from the destination DB and save it in state. I am able to do this with findValue in another part of my code, but in this particular instance I want the highest-value id, which I don’t think findValue supports. SQL Example:

MAX(id)
order by id desc limit 1

The closest I can get to saving this variable, is some code like this. But whenever I try to uncomment and store the variable (state.data.evidence_id), it breaks the SQL() function. Please help!

sql(state=> {
  return (
    //state.data.evidence_id  = 
    `SELECT id from evidence order by id desc limit 1;`
    );
});
2

Ahhh! I think you’ve hit on something that can be easy to get tripped up on.

So the sql function is expecting a function that will return the query; common use cases for this are paginating through records or doing a select with a where clause based on data from a inbox message.

I suggest the following approach, using different operations for each step:

// This one to execute the query (in this case a static query to get the first record)
sql(state => `SELECT id from evidence order by id desc limit 1;`);

fn(state => {
   // to see what that first operation returned:
  console.log(state.data)
  // an example of picking out the result that you are looking for
  // and assigning it to state for later use
  state.myThing = state.data.results[0].id 
  return state;
});

// and then do whatever you want knowing the result is in state for later use.
sql(state => { ...do something else });

Hope that helps!

3

Ah, @davidc , I took a look at language-postgresql and it seems like the entire response of the SQL query is dumped in state.response, not state.data.

So try:

sql(state => `SELECT id from evidence order by id desc limit 1;`);
fn(state => {
  // "response", not "data" for language-postgresql
  console.log(state.response) 
  return state;
});
4

@taylordowns2000 @stu Thank you guys so much for the assistance. I’m much closer now but still coming up short:

console.log(state.response); Is dumping out this:

{
  body: Result {
    command: 'SELECT',
    rowCount: 1,
    oid: null,
    rows: [ [Object] ],
    fields: [ [Field] ],
    _parsers: [ [Function: parseInteger] ],
    _types: TypeOverrides { _types: [Object], text: {}, binary: {} },
    RowCtor: null,
    rowAsArray: false
  }
}

Is there any way to write/access the values here? I tried things like:

  console.log(state.response.body.rows[0]); // response: [object Object]
  console.log(dataValue('rows[0]')); // response: [Function (anonymous)]

But none of them returned the value (happens to be 468) that I should be getting back.

5

False alarm @taylordowns2000 @stu. The issue appeared to be data types. If I CAST the SELECT statement as a text string, I’m able to retrieve from state.response. Thanks for the great assist!

6

For sure! JSON.stringify the bit you want. (Or the whole thing.) On phone or I’d send a proper reply
—but something like this will work: JSON.stringify(thingYouCareAbout, null, 2)

Let me know if that does it! For what it’s worth, I bet the data you’re looking for are in state.response.rows[0]

1 Like