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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SQL INSERT command

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] SQL INSERT command
 Login/Join
 
DarrenH
Member
posted
Hi all,
I'm waiting to get my hands on some FOCUS docs, but in the meantime am trying to figure it out myself Smiler

I have a requirement to write a simple stored procedure to insert a few static values into a SQL table.

I guess at the lowest level this should be the only code I need:

-*-- Set up the SQL Environment ----------------------------
SET SQLENGINE = SQLMSS
SQL SET SERVER MYSERVER
-RUN


-*-- Insert row ----------
SQL INSERT MYDB.dbo.MYTABLE (FIELD1, FIELD2)
VALUES('1','2')
END
-RUN

BUT - I am getting an 'INTERFACE OUT OF SEQUENCE' error.
What is the correct syntax for inserting values into a table?

Many thanks!

This message has been edited. Last edited by: Kerry,


Prod: Service Manager 5.5 - DataMigrator 7.6 - Win2K
Test: Service Manager 5.5 - DataMigrator 7.6 - Win2K
Local: DevStudio 5.5.3 - Management Console
- Windows XP SP2
 
Posts: 21 | Location: Cape Town, South Africa | Registered: January 15, 2007Report This Post
Tony A
Expert
posted Hide Post
Darren,

When using SQL passthru you need to process the SQL by getting WF to use it. Think of the passthru as the setting up of a temporary view. The view is there but you don't get the data unless you execute it.

Try this -
-*-- Set up the SQL Environment ----------------------------
SET SQLENGINE = SQLMSS
SQL SET SERVER MYSERVER
-RUN

-*-- Insert row ----------
SQL
  INSERT INTO MYDB.dbo.MYTABLE (FIELD1, FIELD2) VALUES('1','2')
;
TABLE FILE SQLOUT
PRINT *
END
-RUN


Good luck

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
DarrenH
Member
posted Hide Post
Thanks Tony. I guess it's just getting my head around it conceptually now Smiler

That *seems* to work in that it doesn't return any errors, but it doesn't actually populate my table. I have double-checked my synonym definition for the destination and it's all okay. When I run the code I get a return of
"(FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND: 1/INSERT" which I guess means this should have worked?


Prod: Service Manager 5.5 - DataMigrator 7.6 - Win2K
Test: Service Manager 5.5 - DataMigrator 7.6 - Win2K
Local: DevStudio 5.5.3 - Management Console
- Windows XP SP2
 
Posts: 21 | Location: Cape Town, South Africa | Registered: January 15, 2007Report This Post
hammo1j
Master
posted Hide Post
Darren

Yes that's wf reporting back its done its stuff.

Regarding the syntax of the INSERT in wf - you dont need the table since no answerset is returned unlike a select.

However the semi colon and END are essential...

SQL
INSERT INTO MYDB.dbo.MYTABLE (FIELD1, FIELD2) VALUES('1','2')
;
END

John


Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
DarrenH
Member
posted Hide Post
Excellent! You guys have made my Friday a whole lot easier.

All working now - thanks!


Prod: Service Manager 5.5 - DataMigrator 7.6 - Win2K
Test: Service Manager 5.5 - DataMigrator 7.6 - Win2K
Local: DevStudio 5.5.3 - Management Console
- Windows XP SP2
 
Posts: 21 | Location: Cape Town, South Africa | Registered: January 15, 2007Report This Post
<DocServices>
posted
Hi Darren,

If you have an InfoResponse ID, log on to the Tech Support Web site and then access i-Base. By logging in first, you can download the FOCUS manuals in PDF format and/or view the HTMLHelp version.

You can access i-Base via the following Web sites:


Hope this helps.

Regards,
Jennifer
 
Report This Post
ira
Platinum Member
posted Hide Post
Hi, I believe I caqn help you - what release of WF are you using; it does make a difference...


aix-533,websphere 5.1.1,apache-2.0,
wf 538(d), 537 (p),
==============
7.6.11 (t) aix 5312
websphere 6.1.19
apache 2.0
 
Posts: 195 | Registered: October 27, 2006Report This Post
Ted Michalski
Platinum Member
posted Hide Post
On the same subject, I have the following code:

-SET &MONTH = EDIT(&DATEMtDwY,'999$-$$$99'); (which returns Sep-10)

ENGINE SQLMSS SET DEFAULT_CONNECTION XXXX
-RUN
SQL SQLMSS
INSERT INTO XXXX.dbo.EVAL.XXX_XXX_XXX
VALUES
( 1
, 50
, 1
, NULL
, &MONTH
, NULL
, NULL
, CURRENT_TIMESTAMP
, 'ID') ;
END


And I get the following error message:
: Microsoft OLE DB Provider for SQL Server: [42000] The name "Sep" is not: permitted in this context. Valid expressions are constants, constant exp: ressions, and (in some contexts) variables. Column names are not permitt: ed.

Why would it think Sep is a column name?


7.7.02
Windows
EXCEL, PDF, CSV, TEXT
 
Posts: 106 | Registered: June 25, 2009Report This Post
Francis Mariani
Expert
posted Hide Post
You must place text values within single quotes, just like any SQL INSERT statement.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<JG>
posted
quote:
-SET &MONTH = EDIT(&DATEMtDwY,'999$-$$$99'); (which returns Sep-10)

If you want to add comments to code make sure that you tell WebFOCUS they are comments.
Otherwise it will try to execute what it thinks is code.

-*
 
Report This Post
Ted Michalski
Platinum Member
posted Hide Post
Thanks Francis. I tried this also and it gave me the same error:

-SET &MONTH = EDIT(&DATEMtDwY,'999$-$$$99');
-SET &MONTHB = ''||&MONTH || '' ;


ENGINE SQLMSS SET DEFAULT_CONNECTION XXX
-RUN
SQL SQLMSS
INSERT INTO XXX.dbo.EVAL.XX_XXX_XX
VALUES
( 1
, 50
, 1
, NULL
, NULL
, &MONTHB
, NULL
, CURRENT_TIMESTAMP
, 'FCGSRPPR') ;
END


7.7.02
Windows
EXCEL, PDF, CSV, TEXT
 
Posts: 106 | Registered: June 25, 2009Report This Post
Francis Mariani
Expert
posted Hide Post
The SQL statement should look like this:

SQL SQLMSS
INSERT INTO XXXX.dbo.EVAL.XXX_XXX_XXX
VALUES
( 1
, 50
, 1
, NULL
, '&MONTH'
, NULL
, NULL
, CURRENT_TIMESTAMP
, 'ID') ;
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Ted Michalski
Platinum Member
posted Hide Post
Oh, I thought that WF would think I was trying to put literally "&MONTH" into the text field. Thansk again. I'll give this a try.


7.7.02
Windows
EXCEL, PDF, CSV, TEXT
 
Posts: 106 | Registered: June 25, 2009Report 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] SQL INSERT command

Copyright © 1996-2020 Information Builders