Search code examples
node-postgres

error: syntax error at or near "S" node posgres

My question is I can't resolve the error 42601 which is I know that a syntax error. I run the query on pgadmin and it just works fine. Here's my code I need to concatenate process of query to solve different requests of user.

ps. im using node posgres promise

return new Promise((resolve,reject)=>{
    let {searchType , searchValue , sortType , filterType , size , index} = req.params;

    var select_clause = `SELECT svn_id , mobile_number , network_prefixes.prefix , network_prefixes.country_name , date_created , 
        date_subscribed , expiry_date , svn_status , 
        (select count(*) from svn_transactions where svn_status = 'ACTIVE') "total_active_svn", 
        (select count(*) from svn where status = 'UNASSIGNED') "total_available_svn", 
        (select count(*) from svn_transactions where (svn_status = 'INACTIVE' OR svn_status = 'INCOMING_SMS_ONLY')) "total_grace_svn"
        FROM svn_transactions 
        LEFT JOIN network_prefixes ON network_prefixes.prefix = substr(svn_transactions.mobile_number,1,length(network_prefixes.prefix)) `;

    var where_clause;
    switch(searchType){
        case 'MOBILE_NUMBER':
            where_clause = `WHERE (mobile_number LIKE '%' || $1 || '%') `;
            break;
        case 'SVN_NUMBER':
            where_clause = `WHERE (svn_id LIKE '%' || $1 || '%') `;
            break;
        default: //ALL
            where_clause = `WHERE (mobile_number LIKE '%' || $1 || '%') OR (svn_id LIKE '%' || $1 || '%') `;
    }

    var sort_type;
    switch(sortType){
        case 'MOBILE_NUMBER':
            sort_type = `order by mobile_number `;
            break;
        case 'SVN_NUMBER':
            sort_type = `order by svn_id `;
            break;
        case 'RENEWAL_DATE':
            sort_type = `order by date_subscribed `;
            break;
        case 'SUBSCRIPTION_DATE':
            sort_type = `order by date_created `;
            break;
    }

    var pagination = ` limit $2 offset $3`;

    var query = `${select_clause} ${where_clause} ${sort_type} ${filterType} ${pagination}`;

    pool.query(query[  
        searchValue , size , index
    ],function(err,result){
        if(err) {
            console.log(err);
            reject(err);
        }resolve(result);
    });
});

response error is:

error: syntax error at or near "S"
at Connection.parseE (/var/www/appvno/admin/node_modules/pg/lib/connection.js:614:13)
at Connection.parseMessage (/var/www/appvno/admin/node_modules/pg/lib/connection.js:413:19)
at Socket.<anonymous> (/var/www/appvno/admin/node_modules/pg/lib/connection.js:129:22)
at Socket.emit (events.js:315:20)
at addChunk (_stream_readable.js:295:12)
at readableAddChunk (_stream_readable.js:271:9)
at Socket.Readable.push (_stream_readable.js:212:10)
at TCP.onStreamRead (internal/stream_base_commons.js:186:23) {
length: 89,
severity: 'ERROR',
code: '42601',
detail: undefined,
hint: undefined,
position: '1',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'scan.l',
line: '1134',
routine: 'scanner_yyerror'}

I tried just combining them into 1 single query and it just works fine.:

var example_query = `SELECT svn_id , mobile_number , network_prefixes.prefix , network_prefixes.country_name , 
        date_created , date_subscribed , expiry_date , svn_status , 
        (select count(*) from svn_transactions where svn_status = 'ACTIVE') "total_active_svn",
        (select count(*) from svn where status = 'UNASSIGNED') "total_available_svn",
        -- total_recycled_svn 
        (select count(*) from svn_transactions where (svn_status = 'INACTIVE' OR svn_status = 'INCOMING_SMS_ONLY')) "total_grace_svn"
    FROM svn_transactions 
    left join network_prefixes on network_prefixes.prefix = substr(svn_transactions.mobile_number,1,length(prefix)) 
    WHERE (mobile_number LIKE '%' || $1 || '%')
    order by mobile_number DESC
    limit $2 offset $3`;

I researched if its theres a limitation on concatenation of select query but I found nothing. Any help will do, thanks!

EDIT 1: I already checked if spaces are correct and also no missing semicolons... based on 42601 meaning

EDIT 2: Here's my console.log of concatenated query"

SELECT svn_id , mobile_number , network_prefixes.prefix , network_prefixes.country_name , date_created ,
        date_subscribed , expiry_date , svn_status ,
        (select count(*) from svn_transactions where svn_status = 'ACTIVE') "total_active_svn",
        (select count(*) from svn where status = 'UNASSIGNED') "total_available_svn",
        (select count(*) from svn_transactions where (svn_status = 'INACTIVE' OR svn_status = 'INCOMING_SMS_ONLY')) "total_grace_svn"
        FROM svn_transactions
        LEFT JOIN network_prefixes ON network_prefixes.prefix = substr(svn_transactions.mobile_number,1,length(network_prefixes.prefix))  WHERE (mobile_number LIKE '%' || $1 || '%')  order by mobile_number  DESC  limit $2 offset $3

Still works fine on pgadmin but still error 42601 on node postgres

Solution

  • I found the error. It's not the query but the error was my setup of pool.query. I just forget adding comma after the query and before the values. hehe stupid me before: pool.query(query[.. after: pool.query(query,[..