[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 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
-EXITThis 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.
< !-- 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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
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.