[SOLVED] Insert rows from reporting server into db2/sql tables.

[Justin Thomas 2]

Is there a way to access the records of a hold file on the reporting server (either .foc or .ftm) to insert into a db2 or sql table using passthru?

[David Beagan]

You could do something like this:

TABLE FILE focus_hold_file

PRINT *

ON TABLE HOLD AS DB2_TA LE FORMAT DB2

END

[Justin Thomas 2]

We generally use syntax similar to that (along with DROP) to write hold files to DB2 when we need to. I would like to be insert records from the hold file into the DB2 without modifying existing rows. As a workaround, I have written new records to another DB2 temp table and then used insert in an sql passthru. However, I would like to eliminate that step, and just insert directly from the hold file.

[Martin Yergeau]

You can also use the WebFOCUS MODIFY feature

Here a refence guide about MODIFY feature : TIBCO FOCUS® Maintaining Databases Release 8207.27.0 and Higher

[Justin Thomas 2]

I have only used MODIFY a couple of times, and wasn't sure if it would work between files and tables. Thanks Martin!

[David Beagan]

I created a table in SQL Server using SQL passthru:

-SET &Engine = 'MSODBC';-SET &Connection = 'mss2022';  ENGINE &Engine SET DEFAULT_CONNECTION &Connection SQL &Engine CREATE TABLE ggsql ( SEQ_NO INTEGER , REGION CHAR(11) , ST CHAR(02) , CITY CHAR(20) , CATEGORY CHAR(11) , UNITS INTEGER , DOLLARS INTEGER ); END

Then I used the ggsales FOCUS data table to insert rows into the table I just created:

-SET &Engine = 'MSODBC';-SET &Connection = 'mss2022';  ENGINE &Engine SET DEFAULT_CONNECTION &Connection -REPEAT ENDREPEAT 20 TIMES-READFILE GGSALES SQL &Engine INSERT INTO ggsql ( SEQ_NO, REGION, ST, CITY, CATEGORY, UNITS, DOLLARS) VALUES (&SEQ_NO,'&REGION','&ST','&CITY','&CATEGORY',&UNITS,&DOLLARS); END-ENDREPEAT

Since it uses SQL Passthru there is no need to have metadata for the ggsql table. If you have metadata for the table you are inserting rows into, then you could use MODIFY FILE as an alternative.

[David Beagan]

I was able to create a synonym for the SQL Server table and then create a hold file and use MODIFY FILE to load it into the SQL Server table.

CREATE SYNONYM ggsql FOR dbo.ggsql DBMS MSODBC END  TABLE FILE ggsales PRINT SEQ_NO REGION ST CITY CATEGORY UNITS DOLLARS ON TABLE HOLD AS ggdata FORMAT ALPHA WHERE RECORDLIMIT IS 20 END  MODIFY FILE ggsql SQL SET LOADONLY FIXFORM FROM ggdata MATCH SEQ_NO ON MATCH REJECT ON NOMATCH INCLUDE DATA ON ggdata END

 Hopefully this gives you another option.

[Justin Thomas 2]

Thank you, David. Look forward to giving this a try!

[Justin Thomas 2]

Thank you, David! We have used -READ to populate parameters with values, but with some struggles. Where can I find out more about -READFILE?

[David Beagan]

Some doc for -READFILE at this link:

Developing Reporting Applications, pg 345