Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
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
Go
New
Search
Notify
Tools
Reply
  
[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: 57 | Registered: June 26, 2015Reply With QuoteReport 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: 1847 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport 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, 2015Reply With QuoteReport 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: 57 | Registered: June 26, 2015Reply With QuoteReport 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: 1663 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Gold member
posted Hide Post
quote:
ENGINE SQLMSS SET CURSORS CLIENT

Nope


WebFOCUS 8010,8204
Windows
 
Posts: 57 | Registered: June 26, 2015Reply With QuoteReport 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: 1847 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport 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, 2015Reply With QuoteReport 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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6272 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport 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: 806 | Registered: April 23, 2003Reply With QuoteReport 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: 57 | Registered: June 26, 2015Reply With QuoteReport 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: 57 | Registered: June 26, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.