Announcement

Collapse
No announcement yet.

Row count from query

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Row count from query

    I am losing my mind. been searching the forums and i can not find my mistake.

    No matter which example or how i change it always get 0 as the total. Any assistance would be appreciated

    var dbConn;
    var pagecount;
    //Total number of pages in the document
    //sql connection string
    var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://isql:1433/db1','user','password');
    var doc_id = $('document_id');
    var sql = ("select count(*) as total FROM page WHERE (document_id = '" + doc_id + "' )");
    //execute sql to get total page count
    var pagecount = dbConn.executeCachedQuery(sql);

    if (pagecount.next()) {
    num_records = pagecount.getString(1);
    channelMap.put('totalpages',num_records);
    }


    //close sql channel
    dbConn.close();

  • #2
    Change this

    if (pagecount.next())

    to

    Code:
    while(pagecount.next()) {}
    HL7v2.7 Certified Control Specialist!

    Comment


    • #3
      Hi,

      The only problem I see in your code is the DB url, there's a space between "sql" and "server", an exception is fired when you execute it.

      Otherwise it worked for me once query is set appropriately for my test db.

      Is this the full JS code or just an exerpt?

      And (sorry to ask) are you sure you have a $('document_id') value ? What is the data type of document_id column in your DB?
      Tom

      Comment


      • #4
        I made the change as you suggested but still get result of 0
        The executed sql query returns 3
        Not sure what is going wrong or what mistake i did

        var doc_id = $('document_id');
        var sql = ("select count(*) as total FROM page WHERE (document_id = '" + doc_id + "' )");
        logger.info(sql);
        //execute sql to get total page count
        var pagecount = dbConn.executeCachedQuery(sql);

        while(pagecount.next()) {
        num_records = pagecount.getString(1);
        channelMap.put('totalpages',num_records);
        }


        //close sql channel
        dbConn.close();

        Comment


        • #5
          the typo with the space was where i was removing the actual url and password

          the $(document_id) is valid and the sql does return a vaid count

          Comment


          • #6
            Same result if you don't use the num_record variable ? You don't declare it explicitely, so it's a global variable or it is declared and reused elsewhere?

            Code:
               channelMap.put('totalpages', pagecount.getString(1));
            Tom

            Comment


            • #7
              Originally posted by mroberts View Post
              var sql = ("select count(*) as total FROM page WHERE (document_id = '" + doc_id + "' )");
              remove the brackets:

              Code:
              var sql = "select count(*) as total FROM page WHERE document_id = '" + doc_id + "'";

              Comment


              • #8
                Originally posted by odo View Post
                remove the brackets:

                Code:
                var sql = "select count(*) as total FROM page WHERE document_id = '" + doc_id + "'";
                Same result from both of your suggestions

                channel map total pages gets 0 versus 3 that should be returned.
                Here is the total javasc transformer
                var dbConn;
                var pagecount;
                //Total number of pages in the document
                //sql connection string
                var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://sql:1433/db','user','password');
                var doc_id = $('document_id');
                var sql = "select count(*) as total FROM page WHERE document_id = '" + doc_id + "'";
                logger.info(sql);
                //execute sql to get total page count
                var pagecount = dbConn.executeCachedQuery(sql);

                while(pagecount.next()) {
                //num_records = pagecount.getString(1);
                //channelMap.put('totalpages',num_records);
                channelMap.put('totalpages', pagecount.getString(1));
                }


                //close sql channel
                dbConn.close();

                Comment


                • #9
                  It helps legibility if you use [CODE]tags[/CODE] around your code. This forum also adds spaces when "words" get too long outside of any preformatted tags, which is probably what created the space in sql server.

                  Using if instead of while should have been fine, since your query is only going to return one row. while is used when you don't know how many rows will be returned.

                  Could it be that count() doesn't return a string value type?

                  Does this work?
                  Code:
                  num_records = pagecount.getObject(1);
                  If not, maybe try using getInt or getLong or using the column name instead of number?

                  Code:
                  num_records = pagecount.getInt('total');

                  Comment


                  • #10
                    Still returning 0

                    Below returns 0.0 and the other way returns just 0
                    select count(*) as total FROM page WHERE document_id = '200845D4-159A-48F0-BE46-A94E197894EE' returns 3 as it should

                    is there anything different because this is in a transformer

                    var doc_id = $('document_id');
                    var sql = "select count(*) as total FROM page WHERE document_id = '" + doc_id + "'";
                    logger.info(sql);
                    //execute sql to get total page count
                    var pagecount = dbConn.executeCachedQuery(sql);

                    if (pagecount.next()) {
                    num_records = pagecount.getInt('total');
                    channelMap.put('totalpages',num_records);
                    }
                    //close sql channel
                    dbConn.close();

                    Comment


                    • #11
                      Hi,

                      There's nothing wrong in your piece of code, it worked for me.

                      As you did for 'sql' var I'd log the query result too:

                      Code:
                      logger.info(pagecount.getString(1));
                      The only things I can figure out is your 'totalpage' map variable being altered anywhere else in your transformer (and no, it's OK to do this in a transformer), or a scope issue with 'num_record' that you didn't explicitely declare here (var num_records=...).
                      Tom

                      Comment

                      Working...
                      X