Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Getting error in SQL pass thru although it runs fine in the sql window

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED]Getting error in SQL pass thru although it runs fine in the sql window
 Login/Join
 
Gold member
posted
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
 
Posts: 59 | Registered: June 26, 2015Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 186 | Location: Infobuild India | Registered: August 28, 2015Report This Post
Gold member
posted Hide Post
< !--
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
 
Posts: 59 | Registered: June 26, 2015Report This Post
Virtuoso
posted Hide Post
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 :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Gold member
posted Hide Post
quote:
ENGINE SQLMSS SET CURSORS CLIENT

Nope


WebFOCUS 8010,8204
Windows
 
Posts: 59 | Registered: June 26, 2015Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 146 | Registered: November 09, 2015Report This Post
Expert
posted Hide Post
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!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Master
posted Hide Post
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.  
 
Posts: 822 | Registered: April 23, 2003Report This Post
Gold member
posted Hide Post
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
 
Posts: 59 | Registered: June 26, 2015Report This Post
Gold member
posted Hide Post
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
 
Posts: 59 | Registered: June 26, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Getting error in SQL pass thru although it runs fine in the sql window

Copyright © 1996-2020 Information Builders