Forum Stats

  • 3,872,498 Users
  • 2,266,434 Discussions
  • 7,911,226 Comments

Discussions

Setting the util object after a CONNECT statement

StuartH
StuartH Member Posts: 21 Red Ribbon
edited Jul 30, 2019 6:36AM in SQLcl

I'm starting up sqlcl using the /nolog switch

  • "C:\Program Files\SQL Developer\sqldeveloper\bin\sql.exe" /nolog

And then calling a .js script in the login.sql file that will manage the database connection using...

This is working fine, until I need to run a SQL using...

  • var ret = util.executeReturnList(sqlStr, null);

util is still set as null.  And will remain as such for until the current .js file finishes and I start a new one.

Is there any command to set the util from the current connection of the sqlcl object without leaving the current .js file?

Or is there an alternative method to connect to a database that won't have this drawback?

«1

Answers

  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited Jul 29, 2019 6:49PM

    According to the github doc for SQLcl (https://github.com/oracle/oracle-db-tools/blob/master/sqlcl/SCRIPTING.md)  "util" is a global variable that the scripting engine feature of SQLcl initializes.  Since it is based on a connection, it must be getting initialized only after a connection is made and, as you point out, that initialization seems to get delayed until after the js script that makes the connection terminates.  It is not clear to me if that is a necessary limitation of the scripting engine or just a bug in SQLcl.  Perhaps someone with more knowledge in this area can comment.

    For now, I suppose you could always use the technique described in http://krisrice.io/2016-01-07-tuning-sql-with-javascript-in-sqlcl/ to create a secondary connection and assign it to a "util2" variable to run your sql query.  You just need to figure out in advance the JDBC URL to pass into the DriverManager.getConnection call since in your case there is no existing connection to call getMetaData().getURL() from.

    Cheers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Jul 29, 2019 10:30PM

    As an alter native, you could try calling connect.js that does the connection and initialises util, then call the "rest of your code.js".

  • StuartH
    StuartH Member Posts: 21 Red Ribbon
    edited Jul 30, 2019 4:42AM

    I couldn't find any information on connnect.js?  Do you have a link?

  • StuartH
    StuartH Member Posts: 21 Red Ribbon
    edited Jul 30, 2019 6:01AM

    I re-wrote my script so that it could be called as follows:

    • var dbConn = loadWithNewGlobal('myConnection.js');
    • dbConn.init()

    The dbConn.init() function will run the CONNECT command, then I have a second function that can continue where it left off dbConn.runSQLcheck().

    But even if I wait and manually type the statement to run...

    • SCRIPT
    • dbConn.runSQLCheck()
    • /

    ...I will still get the error

    • "util" is not defined in file ...

    But yet I can still run the follow and it works fine...

    • var ret = util.executeReturnList(sqlStr, null);

    So even though util is a global variable, my instance of dbConn has retained it's own copy of util?

  • StuartH
    StuartH Member Posts: 21 Red Ribbon
    edited Jul 30, 2019 6:36AM

    I've come up with a work around to pass the util backwards by creating a function inside my dbConn...

    •     function passbackControls(utilRef, connRef, ctxRef) {
              util = utilRef;
              conn = connRef;
              ctx = ctxRef;
          }

    Then, immedicately after running the CONNECT statement, I call this function to update the references...

    • sqlcl.setStmt('SCRIPT \n dbConn.passbackControls(util,conn,ctx); \n/');
    • sqlcl.run();

    Now the util is correctly set and can be used as required.

    Should I be including anything else in the passback function?  I added conn and ctx just encase, but maybe they aren't needed?

  • User_J13V3
    User_J13V3 Member Posts: 4 Green Ribbon

    Hi StuartH,

    Could you share your complete example? Would be help a lot to adopt the solution for our problem.

    Greetings

    StuartH
  • StuartH
    StuartH Member Posts: 21 Red Ribbon

    Hi User_J13V3

    This was from a long time ago; so it's not fresh in my head. But looking at my current release of the code, I never went with the solution documented above that uses the "passbackControls" function".

    I can't share you the complete code (as it's over a 1000 lines); but below I've removed a lot of application-specific code and tried to just stick to the problem identified on this ticket. It should hopefully get you going in the right direction


    login.sql

    --Prompt to show login.sql is called twice
    PROMPT login.sql started . . .
    
    --Install Connection functions
    SCRIPT connection.js
    
    --Database selection and connection
    SCRIPT
    dbConn.init();
    /
    


    connection.js

    if (!dbConn) var dbConn = new function() {
    
      //Variables
      var existingConnection = false;
      var dbName;
       //Database lookup
       var databases = {
           SAMPLE1: {sid: 'SAMPLE1', host: 'server' , port: 1521}
           SAMPLE2: {sid: 'SAMPLE2', host: 'server' , port: 1521}
       };
    
      //Startup function
      this.init = function() {
    
           //Get current connected database (if /nolog switch was NOT used, or on second run-through after CONNECT statement)
           if (typeof conn != 'undefined') {
               existingConnection = true;
               var connStr = conn.getMetaData().getURL().split('/');
               dbName = connStr[connStr.length-1];
    
           } else {
    
               //CODE REMOVED that sets the dbName var from environment variables or user prompt
               //dbName = ;
    
           }
    
           //Database selection
           var dbo = databases[dbName]
    
           //CODE REMOVED to determine/prompt for username/password
           //var dbUser = 
           //var dbPass = 
    
           //Startup connection
           if (!existingConnection) {
    
               //NOTE: this will cause the login.sql to re-execute and call this dbConn.init() function again but where existingConnection = true
               print('Connecting to ' + dbo.sid + ' . . .');
               sqlcl.setStmt('CONNECT '  + dbUser 
                           + ((dbPass) ? '/' + dbPass : '')
                           + '@//' + dbo.host 
                           + ':'  + dbo.port 
                           + '/'  + dbo.sid);
               sqlcl.run();
    
               //Check it was successful
               var errCode = ctx.getProperty("sqldev.last.err.message.forsqlcode");
               if (errCode) {
                   this.exitWithError(errCode);
                   return;
               }
    
               //Confirm connected          
               existingConnection = true;
    
               //If successful the connect command will run another instance of login.sql. So kill this one
               return;
           }
    
           //Check for password expiry (E.G. the main reason for this ticket, because util is null)
           var sqlStr = 'SELECT account_status,expiry_date,FLOOR(expiry_date-TRUNC(sysdate)) expire_days FROM user_users WHERE username = user')
           var ret = util.executeReturnList(sqlStr, null);
           if (ret[0].ACCOUNT_STATUS != 'OPEN' || new Date(ret[0].EXPIRY_DATE) < new Date()) {
               var expireDays = ret[0].EXPIRE_DAYS.toString();
               print('Password will expire in ' + expireDays + ' days');
           }
    
           //Run config script (alternative to login.sql)
           sqlcl.setStmt('@connection.sql');
           sqlcl.run();
    
           //Various other application-specific code removed 
    
      }
    
       this.exitWithError = function(errMessage) {
           System.err.println(errMessage);
           this.pauseThenExit();
       }
    
       this.pauseThenExit = function() {
           disconnect();
           print('\n');
           sqlcl.setStmt('HOST PAUSE');
           sqlcl.run();
           quit();
       }
    
       function disconnect() {
           if (existingConnection) {
               sqlcl.setStmt('DISCONNECT');
               sqlcl.run();
               existingConnection = false;
           }
       }
    
       function quit() {
           print('\nClosing . . .');
           disconnect();
           System.exit(0); //Can't use the EXIT command while the login.sql is still processing
       }
    
    }();
    


    connection.sql

    --Display Format settings
    SET echo OFF
    SET verify OFF
    SET timing OFF
    SET termout ON
    SET serveroutput ON
    SET sqlblanklines ON
    SET sqlformat ANSICONSOLE
    SET pagesize 40
    
    --Update Window Title
    HOST TITLE &_USER&_CONNECT_IDENTIFIER
    
  • User_J13V3
    User_J13V3 Member Posts: 4 Green Ribbon

    Thanks for the fast response.

    Do you have an idea, why dbConn is undefined if I use it like you mentioned it before? It only works if I use it inside the same script call.

    --Install Connection functions
    SCRIPT connection.js
    
    --Database selection and connection
    SCRIPT
    dbConn.init();
    /
    
    // Following works but that doesn't help to fix the problem
    SCRIPT
    load('connection.js');
    dbConn.init();
    /
    


  • StuartH
    StuartH Member Posts: 21 Red Ribbon

    Not sure?

    So does this first statement not run:?

    --Install Connection functions
    SCRIPT connection.js
    


    I'm not familiar with the load() functon you've used? But if that's what works for you, have you tried it like this...

    --Install Connection functions
    SCRIPT
    load('connection.js');
    /
    
    --Database selection and connection
    SCRIPT
    dbConn.init();
    /