Hello,
I am trying to execute a sql stored procedure from a focexec.

Here is the code I have:

SET SQLENGINE = SQLMSS
ENGINE SQLMSS SET DEFAULT_CONNECTION &DCSDB;
ENGINE SQLMSS SET CONVERSION LONGCHAR ALPHA
END
-IF &FOCERRNUM NE 0 THEN GOTO CONNERR;
SQL SQLMSS
EX dbo.sqlPrc '&Parm1', '&Parm2', '&Parm3', '&Parm4';
-GOTO NOERR
-CONNERR
TYPE CONNERR
-GOTO EXIT
-NOERR
-TYPE NOERR
-EXIT

&DCSDB is passed to the focexec from my maintain environment and should be DevDb, TestDb, or ProdDb. While testing the focexec from developer studio, I accidentally typed DeDb, and received the following messages in the output window:

(FOC1671) SET SERVER DeDb COMMAND FOR SQLMSS INTERFACE OUT OF SEQUENCE
(FOC1671) SET SERVER DeDb COMMAND FOR SQLMSS INTERFACE OUT OF SEQUENCE
NOERR

If I add, the following code below the -NOERR label. I receive output from my sql proc, even though the connection string is incorrect.

TABLE FILE SQLOUT
PRINT *
ON TABLE PCHOLD AS HOLDSQL
END
TABLE FILE HOLDSQL
PRINT *
END

Essentially, the proc runs against my default db (which currently is my DevDb, thankfully). The problem I have is when I move it to my production server, I need it to fail, if the connection is not valid as opposed to choosing the default connection on my prod server.

If I pass the correct connection name, everything works correctly.
I have tried adding:
ENGINE SQLMSS END SESSION
-RUN
before the SET SQLENGINE = SQLMSS line. to no avail.

I have also tried -RUN and checking &FOCERRNUM and &RETCODE after each SQLMSS command.

Since WebFOCUS is returning (FOC1671), why doesn't checkign &FOCERRNUM work? Is there a way for me to validate the connection before continuing to the SQL SQLMSS EX command?

Thank you for any assistance.

Deana

This message has been edited. Last edited by: Deana,

The error message mentions DeDb, not DevDb...

Francis,

I appreciate your response. I know that the wrong connection was passed in and that is why I received the FOC1671 errors. I just don’t understand how to check for those errors and goto to –CONNERR when the connection passed in is invalid.

I added SQL SQLMSS ? after the END statement for setting the SQL commands. When I pass in DeDb or TetDb (instead of TestDb) or ProDb (instead of ProdDb), the output is as follows:

(FOC1671) SET SERVER ProDb COMMAND FOR SQLMSS INTERFACE OUT OF SEQUENCE
(FOC1671) SET SERVER ProDb COMMAND FOR SQLMSS INTERFACE OUT OF SEQUENCE
(FOC1450) CURRENT SQLMSS INTERFACE SETTINGS ARE :
(FOC1656) DEFAULT SERVER NAME - : DevDb sqlServerName

The remainder of the code is then executed without error using the DevDB connection. If I pass in ProdDb, the DEFAULT SERVER NAME does get set to ProdDB correctly.

What I need to be able to do is abort processing if the Connection passed in (ProDb) does not match the DEFAULT SERVER NAME (DevDb) after the SET command. I have tried to capture the FOC1671 errors and branch to -CONNERR when this occurs, but &FOCERRNUM and &RETCODE always equals 0, regardless of when I check for them (after each ENGINE SQLMSS command or after the END statement).

Thank you,
Deana

You might be able to simply check for an error:

SQL code...
-RUN
-IF &FOCERRNUM NE 0 GOTO FOCUS_ERROR;

The -RUN is important.

I have tried -RUN and then check for &FOCERRNUM after each ENGINE SQLMSS SET command, as well as, after the END command. The sql proc is still executed on DevDb. I have the following code:


SET SQLENGINE = SQLMSS
ENGINE SQLMSS SET DEFAULT_CONNECTION &DCSDB;
-RUN
-TYPE 1st FOC &FOCERRNUM RC &RETCODE
-IF &FOCERRNUM NE 0 THEN GOTO CONNERR;
ENGINE SQLMSS SET CONVERSION LONGCHAR ALPHA
-RUN
-TYPE 2nd FOC &FOCERRNUM RC &RETCODE
-IF &FOCERRNUM NE 0 THEN GOTO CONNERR;
END
-RUN
-TYPE 3rd FOC &FOCERRNUM RC &RETCODE
-*SQL SQLMSS ?

The results when I pass in an invalid connection (badconn) are:

(FOC1671) SET SERVER badconn COMMAND FOR SQLMSS INTERFACE OUT OF SEQUENCE
(FOC1671) SET SERVER badconn COMMAND FOR SQLMSS INTERFACE OUT OF SEQUENCE
1st FOC 0 RC 0
2nd FOC 0 RC 0
3rd FOC 0 RC 0
(FOC1450) CURRENT SQLMSS INTERFACE SETTINGS ARE :
(FOC1656) DEFAULT SERVER NAME - : DevDb sqlServerName

Since I don't seem to be able to capture the &FOCERRNUM, is there a way to put the DEFAULT_CONNECTION name into an amper variable that I can then compare to the connection passed in?

For example:
-SET &DEFCONN = SQLMSS.DEFAULT_CONNECTION;
-IF &DEFCONN <> &DCSDB THEN GOTO CONNERR;

I have tried to figure out a way to do this, but have not figured it out yet.

Thanks again,
Deana

Yes, it looks like the error codes are not set when these commands are executed. Very unfortunate.

Here's one idea: after setting the connections, run a select statement on a system table, qualifying the table with the database name - in my example below, my WF data connection has the same name as the database - so I can use the variable in the select statement:

-SET &DCSDB = 'geminis'

SET SQLENGINE = SQLMSS
ENGINE SQLMSS SET DEFAULT_CONNECTION &DCSDB;
-RUN

-TYPE &FOCERRNUM &RETCODE

SQL SELECT TOP 1 * FROM &DCSDB...DBO.SYSCOLUMNS;
TABLE ON TABLE HOLD
END
-RUN

-TYPE &FOCERRNUM &RETCODE

Note: the DEFAULT_CONNECTION command is case sensitive for the data adapter name, but the select statement is not case sensitive.

Hope this helps.

Francis,

Thanks for the suggestion. I tried this and realized &DCSDB is a poor name for the variable because it implies it contains the database name. &DCSDB...dbo. SYSCOLUMNS references a database that does not exist. I am renaming the variable to &CONN since it is actually the Connection name. I did figure out a similar workaround.
I created a 'constants' table in the database. This table has name and value fields. After setting the DEFAULT_CONNECTION, I execute a proc that will return the value for the 'connection'. I can then verify &CONN matches the value returned from the db, as well as, check for &FOCERRNUM, &RETCODE and &RECORDS.

Here is my WebFOCUS code:

SET SQLENGINE = SQLMSS
ENGINE SQLMSS SET DEFAULT_CONNECTION &CONN;
ENGINE SQLMSS SET CONVERSION LONGCHAR ALPHA
END
-RUN
SQL SQLMSS
EX dbo.storedProc;
TABLE FILE SQLOUT
PRINT ConstantValue
WHERE ConstantValue = '&CONN'
ON TABLE HOLD AS HOLDCONN
END
-IF &FOCERRNUM NE 0 OR &RETCODE NE 0 THEN GOTO UNKERR;
-IF &RECORDS EQ 0 THEN GOTO CONNERR;

WHERE ConstantValue = '&CONN' is case sensitive. DevDb is not equal to DevDB. I have debated whether I should compare the uppercase of both ConstantValue and &CONN since my connections are uniquely named regardless of case. For now, I think am going to leave it as is since it works and is more specific. I would rather abort processing with the correct connection, than continue processing with the wrong connection.

I appreciate your assistance. Sometimes, I get stuck heading done one path to the point of not being able to see other options. Your suggestions helped redirect me.

Thank you,
Deana