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] SQL INSERT command
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] SQL INSERT command
 Login/Join
 
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, 2007Reply With QuoteReport This Post
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.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5601 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
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, 2006Reply With QuoteReport This Post
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, 2007Reply With QuoteReport 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
 
Reply With QuoteReport This Post
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, 2006Reply With QuoteReport This Post
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, 2009Reply With QuoteReport This Post
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, 2005Reply With QuoteReport 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.

-*
 
Reply With QuoteReport This Post
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, 2009Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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, 2009Reply With QuoteReport This Post
  Powered by Social Strata  
 

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

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.