How can I access the data of a property that's in another table? (postgres, express)

See also questions close to this topic

• Legacy Query Sybase to TSQL MSSQL migration with multiple left join with or operator

  I would like to convert this sybase legacy sql code to ansi tsql new standard for ms sql, but I can't find the right way. Any help would be appreciated.

  SELECT 3 FROM  x_linea_cred, x_linea_cred_priv, x_clt_prd 
   WHERE x_clt_prd.r_client = @rclient AND
        (x_clt_prd.nro_prod *= x_linea_cred.nro_prod or
         x_clt_prd.nro_prod *= x_linea_cred_priv.nro_prod))
  
• Implement the stored proc iterative approach in a sql manner

  I am trying to implement convolution between two vectors v1 and v2. As part of that I have written a stored proc with an iterative approach using while loops. Here is the below code as below. I am not able to think through how to do it using sql as the stored proc is inefficient w.r.t performance. Can somebody share your thoughts on this. Any inputs would be appreciated.

  Idea behing writing the stored proc:

  https://software.intel.com/en-us/ipp-dev-reference-convolve

  

  Table Schema and Sample Input Data:

  CREATE TABLE AIRWork..TableA( idx INT, val INT );
  
  CREATE TABLE AIRWork..TableB( idx INT, val INT );
  
  INSERT INTO AIRWork..TableA VALUES( 0,-2 );
  INSERT INTO AIRWork..TableA VALUES( 1, 0 );
  INSERT INTO AIRWork..TableA VALUES( 2, 1 );
  INSERT INTO AIRWork..TableA VALUES( 3,-1 );
  INSERT INTO AIRWork..TableA VALUES( 4, 3 );
  
  INSERT INTO AIRWork..TableB VALUES( 0, 0 );
  INSERT INTO AIRWork..TableB VALUES( 1, 1 );
  

  Stored Proc :

  ALTER PROCEDURE Calc_Convolved_Values_Test  
  AS
  BEGIN
      DECLARE @srclen1 INT 
      DECLARE @srclen2 INT 
      DECLARE @n INT = 0
      DECLARE @k INT
      DECLARE @m INT
      DECLARE @SQL NVARCHAR(1000)
      DECLARE @x int
      DECLARE @xx int = 0
      DECLARE @sum INT = 0
      DECLARE @y int
      DECLARE @yy int = 0
      DECLARE @a INT = 0
      DECLARE @b INT = 0
  
      SELECT @srclen1=COUNT(*) FROM AIRWork..TableA;
  
      SELECT @srclen2=COUNT(*) FROM AIRWork..TableB;
  
      SET @m = @srclen1 + @srclen2 -1
  
      WHILE @n < @m
      BEGIN
              SET @k = 0
              SET @sum = 0
              WHILE @k <= @n
              BEGIN 
  
                        SET @SQL = 'SELECT @x=val FROM AIRWork..TableA WHERE idx ='+CONVERT(VARCHAR(5),@k)
  
                        EXEC sp_executesql @SQL, N'@x int out', @xx out
  
                        SET @a = @xx
  
                        IF @n-@k < @srclen2
                        BEGIN
                              SET @SQL = 'SELECT @y=val FROM AIRWork..TableB WHERE idx ='+CONVERT(VARCHAR(5),@n-@k)
  
                              EXEC sp_executesql @SQL, N'@y int out', @yy out
  
                              SET @b = @yy
                        END
                        ELSE 
                        BEGIN
                              SET @b = 0
                        END 
  
                        SET @sum = @sum + @a*@b
  
                        SET @k = @k + 1
              END 
              PRINT @sum 
              SET @n = @n + 1 
      END
  
  
  END
  GO
  

  Sample Output :

  pDst[n] --> Please check the formula in the beginning of the question.

      0
     -2
      0
      1
     -1
      3
  
• SQL - most performant solution for landing page-led return of results

  I am looking for the fastest method to return results from 1 table, based on criteria listed in another table.

  We have a number of landing pages on our website. Each has a record in our Landing table in an SQL 2016 database, thus:

  ID int (primary key + identity)

  Name varchar(30)

  Criteria_Type varchar(30)

  Criteria_Colour varchar(10)

  Each landing page record describes criteria of the products to be displayed. Additionally, the user on the website can override some criteria such as colour. Also, the criteria in Landing may be null because we might want all Widget product types irrespective of colour.

  At present, the fastest way I can return results is by a stored procedure, thus:

  CREATE PROCEDURE dbo.GET_LANDING_RESULTS
  
     @LPID int,
     @PageNumber int,
     @Override_Colour varchar(10)       
  
  AS
  BEGIN
  
  SET NOCOUNT ON
  
  DECLARE @Criteria_Type varchar(30)
  DECLARE @Criteria_Colour varchar(10)
  
  /* read criteria from landing table */
  
  SELECT
  @Criteria_Type=Criteria_Type,
  @Criteria_Colour=Criteria_Colour
  FROM dbo.Landing
  WHERE ID = @ID
  
  IF @Override_Colour is not null
      @Criteria_Colour = @Override_Colour
  
  /* get results */
  
  SELECT
  Count(ID) OVER() As TotalProducts,
  ItemID,
  Name,
  ProductType,
  Colour
  Price
  FROM dbo.Products
  WHERE
  (ProductType = @Criteria_Type OR @Criteria_Type is null) AND
  (Colour = @Criteria_Colour OR @Criteria_Colour is null)
  OFFSET @PageNumber ROWS
  FETCH NEXT 10 ROWS ONLY
  
  END
  

  The web page passes through the ID number, page number and any colour override information and the procedure returns the products.

  Is there a better way, perhaps with joins? There are many different criteria that can be applied - I have cut down this example.
• How are you supposed to create Winston logger stream for Morgan in TypeScript

  What is the correct way to create a winston logger in TypeScript that will log the express Morgan middleware logging? I found a number of JavaScript samples but have had trouble converting them over to TypeScript, because I get an error Type '{ write: (message: string, encoding: any) => {}; logger: any; }' is not assignable to type '(options?: any) => ReadableStream'. Object literal may only specify known properties, and 'write' does not exist in type '(options?: any) => ReadableStream'.

  Here is my code:

  import { Logger, transports } from 'winston';
  
  // http://tostring.it/2014/06/23/advanced-logging-with-nodejs/
  // https://www.loggly.com/ultimate-guide/node-logging-basics/
  
  const logger = new Logger({
      transports: [
          new (transports.Console)({
              level: process.env.NODE_ENV === 'production' ? 'error' : 'debug',
              handleExceptions: true,
              json: false,
              colorize: true
          }),
          new (transports.File)({
              filename: 'debug.log', level: 'info',
              handleExceptions: true,
              json: true,
              colorize: false
          })
      ],
      exitOnError: false,
  });
  
  
  
  if (process.env.NODE_ENV !== 'production') {
      logger.debug('Logging initialized at debug level');
  }
  
  
  
  // [ts]
  // Type '{ write: (message: string, encoding: any) => {}; logger: any; }' is not assignable to type '(options?: any) => ReadableStream'.
  //   Object literal may only specify known properties, and 'write' does not exist in type '(options?: any) => ReadableStream'.
  logger.stream = {
      write: function (message: string, encoding: any) {
          logger.info(message);
      };
  }
  
  
  export default logger;
  

  I have been able to work around this by adjusting my code to use const winston = require('winston'); but would like to know how you are supposed to do this maintaining types?
• Insert the input into mysql

  Im a newbie in programming. Excuse my ignorance. My english is not too good. Hope you understand what i mean in my question.

  How can i get the value of id 'NILAI' below and insert into mysql.

       html += '<tr>';
                html += '<td class="indi" align="center">';
                html += row.nis;
                html += '</td>';
                html += '<td class="indi">';
                html += row.nama_siswa;
                html += '</td>';
                html += '<td>';
                html += '<input id="nilai" type="text" placeholder="nilai"/>';
                html += '</td>';
                html += '</tr>';  
  

  I want the input value insert into mysql where col 'nis' in mysql table same with row. nis

  I've been trying this but It's wrong. Its just read the value of the first row in input nilai and make a new nis=0.

  var data1 = document.getElementById("nilai") ;
  var i;
      for (i=0;i<data1.length;i++) {
         var sql='INSERT INTO nilaitugas(no_tugas, nilai) VALUES ("'+dataid+'","'data1.elements[i]. value+'")';
     } 
  

  Note:dataid is a variabel from url that i need to input.
• specify path to pdf in node.js project that can be reached when deployed

  I am deploying a node.js website on Google cloud platform, and I would like my keep my resume there, which is currently in my local directory. My code is as follows:

  import { Router }   from 'express';
  import * as express from 'express';
  import * as path    from 'path'   ;
  import * as pug     from 'pug'    ;
  import * as fs from 'fs';
  
  const index: Router = Router();
  
  
  index.get('/curriculum-vitae', (req, res, next) => { 
  
      var pdf_path = "local/path/to/resume.pdf"   
  
      fs.readFile( pdf_path, (err, data) =>{
          res.contentType('application/pdf');
          res.send(data)
      })
  });
  

  However, I would like to change pdf_path to relative path so that when I deploy the app it can be found on the remote server. It is not clear how to do so since fs.readFile seem to only take absolute paths.
• Rails pg database: schema_migrations empty

  I am trying to deploy a new release of a project to my staging server, but the schema_migrations table in the database is inexplicably empty.

  It is now trying to run all migrations while deploying, causing issues since the other tables exist and are intact.

  Instead of dropping/recreating the database and losing all my data(though inconvenient, a valid option), is it possible to generate the schema_migrations table without dropping?
• node-postgres pool management

  I'm trying to connect Nodejs to PostgreSQL database, for that I'm using node-postgres.

  var pool = new Pool({
      user: username,
      password: password,
      host: server
      database: database,
      max: 25
  });
  module.exports = {
   execute_query: function (query2) {
      //usage of query
      pool.query('query2', function(err, result){
  
      return (result);
      });
  
  }
  };
  

  Then in my application, the function execute_query is called in different places in the application. Locally it works but I wonder how the pool is managed, is this enough configuration to manage concurrent users if my application is used by different people ? Do I need to do anything else to ensure that I have clients in the pool ? Or should I use the old way of managing clients with a hard code ?

  I read the documentation of node-postgres and it says that pool.query is the simplist way but it doesnt say how it manages the connections... Do you have any information ?

  Thank you
• Is there an equivalent PostgresSQL window function (or alternate procedure) for the aggregate function bool_or()?

  Given the following data:

  select a,b from newtable;
   a | b
  ---+---
   a | f
   a | f
   a | f
   b | f
   b | f
   b | t
  (6 rows)
  

  The statement

  select a, bool_or(b) from newtable group by a;
   a | bool_or
  ---+---------
   a | f
   b | t
  

  will produce a single row per distinct value (as expected from an aggregate function).

  I was looking for an equivalent window function but seems that there is no such function in PostgreSQL. Is there any way to get the same result? Just to be clear I was looking for this result:

   a | bool_or
  ---+---------
   a | f
   a | f
   a | f
   b | t
   b | t
   b | t 
  
• Catching all errors in async-await express route handlers

  Suppose I have a route like this:

  app.get('/broken', (req, res) => {
    throw new Error('Broken!');
  });
  

  This will never send a response to clients.

  However, I can add a middleware for all errors:

  const errorMiddleware = (error, req, res, next) => {
    if (error) {
      console.error(error);
      return res.status(500)
        .json({
          message: 'Internal server error', 
        });
    }
    next(error);
  };
  

  But this will not work for async routes, because they do not throw directly.

  For example, this will not work:

  app.get('/broken', async (req, res) => {
    throw new Error('Broken!');
  });
  

  So I can create a wrapper like this:

  const asyncRoute = f => (req, res, next) => {
    return Promise.resolve(f(req, res, next)).catch(next);
  };
  
  app.get('/broken', asyncRoute(async (req, res) => {
    throw new Error('Broken!');
  }));
  

  But this is a real pain, because now I have to call this function for every route!

  What is a better way of handling this?

  ---

  • The answer to Is there a way to wrap an await/async try/catch block to every function? is just what I describe above
  • The answer to how to use Promise with express in node.js? does not use await
• setting content type for static javascript files in express

  I am using express to serve up a page with js files using es6 modules. About es6 modules - https://jakearchibald.com/2017/es-modules-in-browsers/

  my server.js file is -

  const app = express();
  app.use( express.static( __dirname + '/src' ));
  
  app.get('*', (req, res) => {
     res.sendFile(path.join(__dirname + '/index.html'));
  });
      
  app.listen(8080, () => console.log('Listening on port 8080!'));

  and my index.html is -

  <html lang="en">
    <body>
      <script type="module" src="./src/test.js"></script>
    </body>
  </html>

  In the file test.js i am using es6 modules, thus the type="module" in the script tag.

  But test.js script is not loading when i serve this html in browser. It is giving the error -

  Failed to load module script: The server responded with a non-JavaScript MIME type of "text/html". Strict MIME type checking is enforced for module scripts per HTML spec.
  

  I tried adding the mime type for js files in express using this in the server.js file -

  express.static.mime.define({'application/javascript': ['js']});
  

  But still the same result. I guess i need to somehow send the mime/content-type from express for js files, but how? Please help.
• Validate input in Nodejs/Express back-end and send result to React front-end

  I want to check if a domain (eg.google.com) that a user enters in my React front-end is valid.
  I'm sending the domain to my Nodejs/Express back-end and using the Node function dns.lookup to check if the domain is valid as follows:

  app.post('/new-cert', function (req, res) {
      var lookup = dns.lookup(req.body.deletedCert, function (err, addresses, family) {
          console.log(addresses); //eg.74.125.224.72 or undefined
      });
  
      // Only run this bit if `addresses` above is NOT `undefined`
      fs.appendFile('certs-list', '\n' + req.body.domainInput, function (err) {
          res.send('POST request: ');
          exec('sh cert-check-script-insert.sh');
          if (err) throw err;
      });
  });
  

  If addresses is undefined then I want to tell my React front-end that the domain entered is invalid and then it can print a relevant message to the user.
  Else I want to run the rest of the function from fs.appendFile onwards to go ahead and insert the domain.

  Sorry I'm new to React/Node and was unable to find a post which could help me, any help is appreciated!