Focal Point
[CLOSED]Getting error in SQL pass thru although it runs fine in the sql window

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/4627024096

June 23, 2018, 06:18 AM
Kartik Katyal
[CLOSED]Getting error in SQL pass thru although it runs fine in the sql window
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 DescriptionFrownerFOC1400) 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,


WebFOCUS 8010,8204
Windows
June 23, 2018, 09:20 AM
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?


WebFOCUS 8206, Unix, Windows
June 23, 2018, 11:51 PM
Chaudhary
you can create a sql procedure and then run in sql passthrough.


WF Production :- WF:8.0.0.4, 8.1.05 App-studio/Developer Studio(8.1.x) ,
8.2.0.1M , 8.2.0.2 (App-Studio8.2.x),
InfoAssist/+, InfoDiscovery
Output format:-AHTML, PDF, Excel, HTML
Platform:-Windows 7, 8,10
June 25, 2018, 12:10 AM
Kartik Katyal
< !--
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?



WebFOCUS 8010,8204
Windows
June 25, 2018, 07:08 AM
Wep5622
Perhaps with:
ENGINE SQLMSS SET CURSORS CLIENT



WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
June 25, 2018, 07:42 AM
Kartik Katyal
quote:
ENGINE SQLMSS SET CURSORS CLIENT

Nope


WebFOCUS 8010,8204
Windows
June 25, 2018, 10:25 AM
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?


WebFOCUS 8206, Unix, Windows
June 25, 2018, 04:03 PM
jcannavo
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';



JC
WebFOCUS Dev Studio / App Studio
8.2.01
Windows 7
June 25, 2018, 05:14 PM
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.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

June 25, 2018, 09:12 PM
David Briars
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.  

June 26, 2018, 02:09 AM
Kartik Katyal
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?



WebFOCUS 8010,8204
Windows
June 26, 2018, 02:31 AM
Kartik Katyal
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.



WebFOCUS 8010,8204
Windows