Sunday, March 11, 2018

webapi call to nodejs to postgres with multiple placeholders not working

By Hường Hana 10:00 AM , Leave a Comment

I have two tables recipemaster and ingredientmaster.

recipemaster table

recipe_code |recipe_title | ------------|-------------| 1           |    one      | 2           |    two      | 3           |    three    | 4           |    four     |

ingredientmaster table

ingredient_code |recipe_code |ingredient_detail | ----------------|------------|------------------|                 |            |                  |

In front end react, i am entering ingredient_detail based on the selected recipe_title from the recipemaster table using select box, my task is to select recipe_title from recipemaster table and that should select the particular recipe_code based on the recipe_title and populate that particular recipe_code in the ingredientmaster table. Ofcourse ingredient_detail is also entered together. Ingredient_code is auto incremented, so no need to worry about that.

what i tried :-

app.post('/api/new-ingre',function(request,response){     console.log('Ingredients connected to db')     var recipe_code;     var recipe_title = request.body.recipe_title;     var recipe_text = request.body.recipe_text;     let values = [recipe_title,recipe_text];     pool.connect((err,db,done)=>{         if(err){             return response.status(400).send(err);         }         else{             console.log(request.body.recipe_title)             console.log(request.body.recipe_text)             db.query('INSERT INTO public.ingredientmaster(recipe_code,ingredient_detail=?) select recipe_code from recipemaster where recipe_title = ?',[recipe_text,recipe_title],(err,table)=>{                 done();                 if(err){                     return response.status(400).send(err);                 }                 else{                     console.log('INGREDIENT DATA INSERTED');                     response.status(201).send({message:'Ingredient data inserted'});                 }             })         }     }) });

The query works when executed in database. But i dont know how to use this query in nodejs when this type of complex query comes. Help is appreciated. Thank you.

1 Answers

Answers 1

OK, I had similar problems at the beging of my implementation and it is because I didn't had enough understanding about postgres, so my advise is:

  1. I'm using pg-promise library

  2. Create a connect singleton file like this (mydb.js)

    'use strict'; let pgp = require('pg-promise')(); //let db = pgp(config.postgres); module.exports.db = db;

  3. Connect to PostgreSQL on you app.js (or equivalent main file) using previous file, something like this:

    import { db } from './mydb'; //.... more imports and initialization lines //.... //I use this to validate postgres connection only on app.js db.proc('version') .then(() => {   console.log('Postgres Connected.... '); }) .catch(err => {   console.log('Postgres connection error:');   console.error(err);   process.exit(-1); });

  4. On your controller use this code or similar:

    import { db } from './mydb'; //...more code app.post('/api/new-ingre',function(request,response){    let sqlInsert = `INSERT INTO ......`;    db.any(sqlInsert, [])    .then(()=>{        console.log('INGREDIENT DATA INSERTED');        response.status(201).send({message:'Ingredient data inserted'});     })    .catch((err)=>{        response.status(400).send(err);    }) });
If You Enjoyed This, Take 5 Seconds To Share It

0 comments:

Post a Comment