Hi,
I am executing an IF statement before my Insert due to which I am getting an error in Sql pass thru and can't understand why.
Error DescriptionFOC1400) SQLCODE IS 102 (HEX: 00000066) XOPEN: 42000
: Microsoft OLE DB Provider for SQL Server: [42000] Incorrect syntax near
: ')'.
(FOC1414) EXECUTE IMMEDIATE ERROR.

My Code:

-SET &ECHO = ALL;

ENGINE SQLMSS SET DEFAULT_CONNECTION InternationalRealTimeReporting

SQL SQLMSS

IF NOT EXISTS(SELECT *
FROM userinput.dbo.operausagedetails
Where userid = '&IBIMR_user'
and FexName = '&CALLING'
and Datediff(s,lastuseddatetime, Getdate()) < 80)

BEGIN
INSERT INTO userinput.dbo.operausagedetails
(userid,
fexname,
lastuseddatetime)
VALUES ( '&IBIMR_user',
'&CALLING',
Getdate());
END

END
-RUN

-EXIT

This message has been edited. Last edited by: Kartik Katyal,

Can you show us what the echo of your code looks like? I’m wondering what the variable substitutions are when this is sent to the server. Alternatively, can you see if hard coding the variables works so we can eliminate them as the cause of the problem?

you can create a sql procedure and then run in sql passthrough.

< !--
ENGINE SQLMSS SET DEFAULT_CONNECTION InternationalRealTimeReporting
SQL SQLMSS
IF NOT EXISTS(SELECT *
FROM userinput.dbo.operausagedetails
Where userid = 'kkaty4'
and FexName = 'abc'
and Datediff(s,Getdate(),'2018-06-25 00:00:00.000' ) < 80)
BEGIN
INSERT INTO USERINPUT.DBO.OPERAUSAGEDETAILS (USERID,FEXNAME,LASTUSEDDATETIME)
VALUES ( 'kkaty4','abc',GETDATE());
END
END
-RUN
(FOC1400) SQLCODE IS 102 (HEX: 00000066) XOPEN: 42000
: Microsoft OLE DB Provider for SQL Server: [42000] Incorrect syntax near
: ')'.
(FOC1414) EXECUTE IMMEDIATE ERROR.
-EXIT
Doesn't work with hard coding as well.

quote:

Originally posted by BabakNYC:
Can you show us what the echo of your code looks like? I’m wondering what the variable substitutions are when this is sent to the server. Alternatively, can you see if hard coding the variables works so we can eliminate them as the cause of the problem?

Perhaps with:

ENGINE SQLMSS SET CURSORS CLIENT

quote:

ENGINE SQLMSS SET CURSORS CLIENT

Nope

Can you test just an INSERT with hard coded values using passthru? Are you able to insert data into the same table using ON TABLE HOLD FORMAT MSSQL?

I second Chaudhary's idea. Create stored proc in the DB to handle what needs to be handled and just call into that...

-SET &var1_int = &INPUTVAR1;
-SET &var2_char = &INPUTVAR2;
-SET &method = &INPUTMETHOD;
-*method can be: "insert", "update", or "delete" which stored proc will handle

ENGINE SQLMSS SET DEFAULT_CONNECTION sqlservername
SQL SQLMSS EX myDB.dbo.InsertUpdateDelete &var1_int,'&var2_char','&method';

Just an observation,

With SQL passthrough, you will need to have any SQL END statements on a line with other code, unless WebFOCUS will interpret it and a FOCUS END statement.

quote:

: Microsoft OLE DB Provider for SQL Server: [42000] Incorrect syntax near: ')'.

Looks like you have a ')' without a matching '('. (WHERE clause third predicate?)

I've never tried an 'IF [NOT] EXISTS' from WF PT before, but it looks like it should work.

-SET &BEID = 274;
-*-SET &BEID = 999;

ENGINE SQLMSS SET DEFAULT_CONNECTION CON01
SQL SQLMSS 
IF EXISTS
    (
    SELECT 1
    FROM [AdventureWorks2014].[Sales].[vSalesPerson]
    WHERE BusinessEntityID = '&BEID'
    )

    BEGIN
        RAISERROR('Found record!', 16, 1) 
   END
ELSE
    BEGIN
        RAISERROR('Did not find record!', 16, 1)  
    END  
END 

Gives:

(FOC1400) SQLCODE IS 50000 (HEX: 0000C350) XOPEN: 42000
 : Microsoft SQL Server Native Client 11.0: [42000] Found record!
 (FOC1414) EXECUTE IMMEDIATE ERROR.  

That's working..It's got something to do with If EXISTS it seems..

quote:

Originally posted by BabakNYC:
Can you test just an INSERT with hard coded values using passthru? Are you able to insert data into the same table using ON TABLE HOLD FORMAT MSSQL?

Thanks Waz..that worked.

quote:

Originally posted by Waz:
Just an observation,

With SQL passthrough, you will need to have any SQL END statements on a line with other code, unless WebFOCUS will interpret it and a FOCUS END statement.