Hi all,
I'm waiting to get my hands on some FOCUS docs, but in the meantime am trying to figure it out myself

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,

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

Thanks Tony. I guess it's just getting my head around it conceptually now

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?

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

Excellent! You guys have made my Friday a whole lot easier.

All working now - thanks!

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:

• http://techsupport.informationbuilders.com
  (From the top navigation bar, click Publications. Then select i-Base: Tech Doc Library.)
  
• www.informationbuilders.com
  (From the top navigation bar, click Services & Support. Then select Documentation.)
  
• www.iwaysoftware.com
  (From the top navigation bar, click Services & Support. Then select Bookstore.)
  

Hope this helps.

Regards,
Jennifer

Hi, I believe I caqn help you - what release of WF are you using; it does make a difference...

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?

You must place text values within single quotes, just like any SQL INSERT statement.

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.

-*

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

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

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.