Scripting enhancement - :if statement should also consider SQL statements

[GStegemann]

Hello Edwig,

as you may have seen from my sample script I intend to use ":if" statements in different ways, not only together with ":print" or other script sstatements, but also for SQL statements. But however that does not work, since ":if" statements have only an effect on succeeding scripting statements.

Here an example:

-- Next test if application is already defined. variable2 has Parent ID of this host as Object ID
:variable3 = variable2;
select objectid from OBJECTS where classname = 'Flapp' and objectname = 'XXXApp';
:if variable1 = 0;
:print 'No such application in Objects';
:if variable1 = 0;
insert into Objects (objectname, parentid, classname) values ('XXXApp', ?, 'Flapp');
-- Retrieve eventually created new Object ID
:if variable1 = 0;
select objectid from OBJECTS where classname = 'Flapp' and objectname = 'XXXApp';
:if variable1 = 0;
:print 'ERROR: New Application could not be created';
-- Exit now
:if variable1 = 0;
:file close;
:if variable1 = 0;
:exit;

In this example the "insert into Objects" statement should only be executed if variable1 is zero. But the "insert" statement is executed always.

:ASSIGN variable3 = [243]
Query: select objectid from OBJECTS where classname = 'Flapp' and objectname = 'XXXApp' 
:COLUMN [1] = [objectid]
:ROW [1:objectid] = [288]
:IF variable1 [288] = [0]
Query: insert into Objects ( objectname , parentid , classname ) values ( 'XXXApp' ,?, 'Flapp' )
Query: select objectid from OBJECTS where classname = 'Flapp' and objectname = 'GurtenApp' 
:COLUMN [1] = [objectid]

       2             select objectid from OBJECTS where classname = 'Flapp' and objectname = 'XXXApp' ..... : OK                                                                                                                                                    
       0             :if 0 : OK                                                                                                                                                                                                                                        
       0             :if 0 : OK                                                                                                                                                                                                                                        
       1             insert into Objects ( objectname , parentid , classname ) values ( 'XXXApp' ,?, 'Flapp' )..... : OK                                                                                                                                            
       1             :COLUMN [1] = [objectid]
 : OK                                                                                                                                                                                                                    
       1             :ROW [1:objectid] = [288]
 : OK                                                                                                                                                                                                                   
       1             :ROW [2:objectid] = [289]
 : OK                                                                                                                                                                                                                   
       1             :ROW [3:objectid] = [290]
 : OK                                                                                                                                                                                                                   
       3             select objectid from OBJECTS where classname = 'Flapp' and objectname = 'XXXApp' ..... : OK                                                                                                                                                    
       0             :if 0 : OK                                                                                                                                                                                                                                        
       0             :if 0 : OK                                                                                                                                                                                                                                        
       0             :if 0 : OK                                                                                                                                                                                                                                        
       3             :variable = variable1 : OK                                                                                                                                                                                                                        

If such a feature could be implemented can it have side effects on already existing scripts? Or would it be better to prefix such other statements with a special character like "!"?

Best regards, Gerhard

[edwig]

Hello Gerhard,

This proposed scripts will not work, because the variables only are changed in value when called as a parameter to a stored procedure or stored function, and only then when they are declared as an output or an inout parameter. The statement ":if variable1 = 0" is working properly, but the variable is never assigned a new value!

What you probably want is something of the state of the last SQL statement, like 'number-of-rows' or 'selected', or 'no-rows'. The SQL standard is not very helpfull here as the only defined standard errors are:

• 0 -> Nothing went wrong
• 100 -> no more data
• 99 -> Success with info

So probably a virtual variable "ROWS" is best in your case. Something like this:

SELECT column FROM tablename WHERE other_column = 1234;
:if ROWS = 1
:file close

If this is your intent, please let me know, as this is quite easy to implement. Kind regards,

Edwig.

[GStegemann]

No, the script should work. I have only added a snippet of my script to this issue, not including the variables declarations. The full script is:

:file write 'D:\temp\Add_Application_Test.log';
:print 'Adding Application to Namespace.';

-- Result variables
:variable1 COLUMN LONG;
:variable2 COLUMN LONG;
:variable3 INPUT LONG;
:variable4 RESULT LONG;

-- Check if current host is defined table OBJECTS
:select on;
select parentid,objectid from OBJECTS where classname = 'Flink' and objectname = 'IGSVM04';

-- Next test if application is already defined. variable2 has Parent ID of this host as Object ID
:variable3 = variable2;
select objectid from OBJECTS where classname = 'Flapp' and objectname = 'XXXApp';
:if variable1 = 0;
:print 'No such application in Objects';
:if variable1 = 0;
insert into Objects (objectname, parentid, classname) values ('XXXApp', ?, 'Flapp');
-- Retrieve eventually created new Object ID
:if variable1 = 0;
select objectid from OBJECTS where classname = 'Flapp' and objectname = 'XXXApp';
:if variable1 = 0;
:print 'ERROR: New Application could not be created';
-- Exit now
:if variable1 = 0;
:file close;
:if variable1 = 0;
:exit;

The log of ODBCQuery:

Status Affected rows Result                                                                                                 

       0             :file D:\temp\Add_Application_Test.log : OK                                                            
       1             :variable LONG : OK                                                                                    
       2             :variable LONG : OK                                                                                    
       3             :variable LONG : OK                                                                                    
       4             :variable LONG : OK                                                                                    
       0             :select on : OK                                                                                        
       1             :COLUMN [1] = [parentid]
 : OK                                                                         
       1             :COLUMN [2] = [objectid]
 : OK                                                                         
       **1             :ROW [1:parentid] = [53]
 : OK                                                                         
       1             :ROW [1:objectid] = [243]**
 : OK                                                                        
       1             select parentid , objectid from OBJECTS where classname = 'Flink' and objectname = 'IGSVM04' ..... : OK
       **3             :variable = variable2 : OK**                                                                             
       1             :COLUMN [1] = [objectid]
 : OK                                                                         
       0             select objectid from OBJECTS where classname = 'Flapp' and objectname = 'XXXApp' ..... : OK            
       0             :if 0 : OK                                                                                             
       0             :if 0 : OK                                                                                             
       **1             insert into Objects ( objectname , parentid , classname ) values ( 'XXXApp' ,?, 'Flapp' )..... : OK**    
       1             :COLUMN [1] = [objectid]
 : OK                                                                         
       1             :ROW [1:objectid] = [299]
 : OK                                                                        
       1             select objectid from OBJECTS where classname = 'Flapp' and objectname = 'XXXApp' ..... : OK            
       0             :if 0 : OK                                                                                             
       0             :if 0 : OK                                                                                             
       0             :if 0 : OK                                                                                             

The script log file:

ODBC Script output: 2024-07-09 15:58:41
=======================================
Adding Application to Namespace.
:NEW variable1
:PARAM variable1 [COLUMN]
:DTYPE variable1 [LONG]
:NEW variable2
:PARAM variable2 [COLUMN]
:DTYPE variable2 [LONG]
:NEW variable3
:PARAM variable3 [INPUT]
:DTYPE variable3 [LONG]
:NEW variable4
:DTYPE variable4 [LONG]
:SELECT on
Query: select parentid , objectid from OBJECTS where classname = 'Flink' and objectname = 'IGSVM04' 
:COLUMN [1] = [parentid]
:COLUMN [2] = [objectid]
:ROW [1:parentid] = [53]
:ROW [1:objectid] = [243]
:ASSIGN variable3 = [243]
Query: select objectid from OBJECTS where classname = 'Flapp' and objectname = 'XXXApp' 
:COLUMN [1] = [objectid]
:IF variable1 [53] = [0]
:IF variable1 [53] = [0]
Query: insert into Objects ( objectname , parentid , classname ) values ( 'XXXApp' ,?, 'Flapp' )
Query: select objectid from OBJECTS where classname = 'Flapp' and objectname = 'XXXApp' 
:COLUMN [1] = [objectid]
:ROW [1:objectid] = [299]
:IF variable1 [299] = [0]
:IF variable1 [299] = [0]
:IF variable1 [299] = [0]

You see that variables 1 and 2 are declared as COLUMN. So they should the receive the values of query "select parentid,objectid ...". The values are correctly logged by ODBCQuery. That is not the point. My issue is that :if statements have no effect on Database Statements. I want to have a feature to be able to also run Database Statements selectively.

But your proposal implementing a virtual variable "ROWS" would be also very nice.

Second I found probably a bug regarding variables.

In my script variables 1 and 2 represent a so called "parentid" and "objectid". The value of "objectid" is copied from variable 2 into variable 3 (of type INPUT). But in the "insert into Objects ..." statement the value of variable 1 is inserted into the database table:

objectid objectname                parentid classname          

243      IGSVM04                   53       Flink              
299      XXXApp                    53       Flapp              

The record with objectid 299 should have a parentid of 243. Where a values interchanged? As far as I understand the script documentation the type of variable describes its purpose not its order of declaration.