Hi,

ve be reading through several manuals, searching the forum & the IB support site but feel im going round in circles in what im trying to achieve.

Im looking to create an sql tables on a specific server & within a specified database.

Id be REALLY grateful if anyone could give me an idea as to what I should be looking to do. A really basic procedure (i.e loading up some car info) would be great as I could pull it apart. hope im not asking to much but ive nearly pulled out all my hair now

thanks very muchly

As far as I can tell, what you're trying to do is not a normal WebFOCUS process, but it can be done, as long as the create table privilege is set up for the user id in control - be it the logged in user id or a hard-coded data-access userid.

Here is code that will replicate the WebFOCUS hierarchical database CAR to multiple MS SQL Server tables - one table per segment.

-SET &ECHO = 'ON';

SET HOLDFORMAT = ALPHA
SET HOLDLIST   = PRINTONLY

-*-- Extract data --------------------------------------------------------------
TABLE FILE CAR
PRINT
COMPUTE SQL/A200 = 'INSERT INTO dbo.SQLCAR_ORIGIN (COUNTRY) VALUES (' ||
'''' || COUNTRY || '''' ||
')';

BY COUNTRY NOPRINT
ON TABLE HOLD AS SORIGIN
END
-RUN

TABLE FILE CAR
PRINT
COMPUTE SQL/A200 = 'INSERT INTO dbo.SQLCAR_COMP (COUNTRY, CAR) VALUES (' ||
''''   || COUNTRY || '''' ||
', ''' || CAR || '''' ||
')';

BY COUNTRY NOPRINT
ON TABLE HOLD AS SCOMP
END
-RUN

TABLE FILE CAR
PRINT
COMPUTE SQL/A200 = 'INSERT INTO dbo.SQLCAR_CARREC (COUNTRY, CAR, MODEL) VALUES (' ||
''''   || COUNTRY || '''' ||
', ''' || CAR || '''' ||
', ''' || MODEL || '''' ||
')';

BY COUNTRY NOPRINT
BY CAR NOPRINT
ON TABLE HOLD AS SCARREC
END
-RUN

TABLE FILE CAR
PRINT
COMPUTE SQL/A200 = 'INSERT INTO dbo.SQLCAR_WARANT (COUNTRY, CAR, WARRANTY) VALUES (' ||
''''   || COUNTRY || '''' ||
', ''' || CAR || '''' ||
', ''' || WARRANTY || '''' ||
')';

BY COUNTRY NOPRINT
BY CAR NOPRINT
ON TABLE HOLD AS SWARANT
END
-RUN

TABLE FILE CAR
PRINT
COMPUTE SQL/A200 = 'INSERT INTO dbo.SQLCAR_EQUIP (COUNTRY, CAR, STANDARD) VALUES (' ||
''''   || COUNTRY || '''' ||
', ''' || CAR || '''' ||
', ''' || STANDARD || '''' ||
')';

BY COUNTRY NOPRINT
BY CAR NOPRINT
ON TABLE HOLD AS SEQUIP
END
-RUN

TABLE FILE CAR
PRINT
COMPUTE SQL/A300 = 'INSERT INTO dbo.SQLCAR_BODY (COUNTRY, CAR, MODEL, BODYTYPE, SEATS, DEALER_COST, RETAIL_COST, SALES) VALUES (' ||
''''   || COUNTRY || '''' ||
', ''' || CAR || '''' ||
', ''' || MODEL || '''' ||
', ''' || BODYTYPE || '''' ||
', '   || EDIT(SEATS) || '' ||
', '   || FTOA(DEALER_COST, '(D7c)', 'A14') || '' ||
', '   || FTOA(RETAIL_COST, '(D7c)', 'A14') || '' ||
', '   || EDIT(SALES) || '' ||
')';

BY COUNTRY NOPRINT
BY CAR NOPRINT
BY MODEL NOPRINT
ON TABLE HOLD AS SBODY
END
-RUN

TABLE FILE CAR
PRINT
COMPUTE SQL/A400 = 'INSERT INTO dbo.SQLCAR_SPECS (COUNTRY, CAR, MODEL, BODYTYPE, LENGTH, WIDTH, HEIGHT, WEIGHT, WHEELBASE, FUEL_CAP, BHP, RPM, MPG, ACCEL) VALUES (' ||
''''   || COUNTRY || '''' ||
', ''' || CAR || '''' ||
', ''' || MODEL || '''' ||
', ''' || BODYTYPE || '''' ||
', '   || FTOA(LENGTH, '(D5c)', 'A10') || '' ||
', '   || FTOA(WIDTH, '(D5c)', 'A10') || '' ||
', '   || FTOA(HEIGHT, '(D5c)', 'A10') || '' ||
', '   || FTOA(WEIGHT, '(D6c)', 'A12') || '' ||
', '   || FTOA(WHEELBASE, '(D6.1c)', 'A14') || '' ||
', '   || FTOA(FUEL_CAP, '(D6.1c)', 'A14') || '' ||
', '   || FTOA(BHP, '(D6c)', 'A12') || '' ||
', '   || EDIT(RPM) || '' ||
', '   || FTOA(MPG, '(D6c)', 'A12') || '' ||
', '   || FTOA(ACCEL, '(D6c)', 'A12') || '' ||
')';

BY COUNTRY NOPRINT
BY CAR NOPRINT
BY MODEL NOPRINT
ON TABLE HOLD AS SSPECS
END
-RUN

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

-*-- Delete tables -------------------------------------------------------------
SQL
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.SQLCAR_ORIGIN') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.SQLCAR_ORIGIN
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.SQLCAR_COMP') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.SQLCAR_COMP
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.SQLCAR_CARREC') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.SQLCAR_CARREC
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.SQLCAR_WARANT') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.SQLCAR_WARANT
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.SQLCAR_EQUIP') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.SQLCAR_EQUIP
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.SQLCAR_BODY') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.SQLCAR_BODY
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.SQLCAR_SPECS') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.SQLCAR_SPECS
END
-RUN

-*-- Create tables -------------------------------------------------------------
SQL
CREATE TABLE dbo.SQLCAR_ORIGIN (
COUNTRY char (10)
)

CREATE TABLE dbo.SQLCAR_COMP (
COUNTRY char (10),
CAR char (16)
)

CREATE TABLE dbo.SQLCAR_CARREC (
COUNTRY char (10),
CAR char (16),
MODEL char (24)
)

CREATE TABLE dbo.SQLCAR_WARANT (
COUNTRY char (10),
CAR char (16),
WARRANTY char (40)
)

CREATE TABLE dbo.SQLCAR_EQUIP (
COUNTRY char (10),
CAR char (16),
STANDARD char (40)
)

CREATE TABLE dbo.SQLCAR_BODY (
COUNTRY char (10),
CAR char (16),
MODEL char (24),
BODYTYPE char (12),
SEATS numeric (4),
DEALER_COST numeric (8),
RETAIL_COST numeric (8),
SALES numeric (8)
)

CREATE TABLE dbo.SQLCAR_SPECS (
COUNTRY char (10),
CAR char (16),
MODEL char (24),
BODYTYPE char (12),
LENGTH  numeric (6),
WIDTH  numeric (6),
HEIGHT  numeric (6),
WEIGHT  numeric (6),
WHEELBASE  numeric (6,1),
FUEL_CAP  numeric (6,1),
BHP  numeric (6),
RPM  numeric (6),
MPG  numeric (6),
ACCEL  numeric (6)
)
END
-RUN

-*-- Load tables ---------------------------------------------------------------
SQL
-INCLUDE SORIGIN
-INCLUDE SCOMP
-INCLUDE SCARREC
-INCLUDE SWARANT
-INCLUDE SEQUIP
-INCLUDE SBODY
-INCLUDE SSPECS
END
-RUN

I didn't create any keys or secondary indexes. As well, you could use Dialogue Manager to further automate the column names and definitions.

This a report that runs against the SQL tables and against the FOCUS DB:

-SET &ECHO='ON';

-*-- Set up SQL tracing --------------------------------------------------------

-*-- Deactivate SQL tracing --------------------------------
SET TRACEOFF = ALL

-*-- Enable Trace for the SQL Translator -------------------
SET TRACEON = SQLTRANS

-*-- Show SQL statements -----------------------------------
SET TRACEON = STMTRACE//CLIENT

-*-- Show Optimization information -------------------------
SET TRACEON = SQLAGGR//CLIENT

-*-- Disable the trace stamp (Date/Time etc) ---------------
SET TRACESTAMP = OFF

-*-- Set trace line wrapping - # of characters -------------
SET TRACEWRAP = 78

-*-- Activate SQL tracing ----------------------------------
SET TRACEUSER = ON
-RUN

-*-- Run SQL report ------------------------------------------------------------

JOIN CLEAR *
JOIN SQLCAR_ORIGIN.COUNTRY IN SQLCAR_ORIGIN TO ALL SQLCAR_COMP.COUNTRY   IN SQLCAR_COMP   AS J1
JOIN SQLCAR_COMP.CAR       IN SQLCAR_ORIGIN TO ALL SQLCAR_CARREC.CAR     IN SQLCAR_CARREC AS J2
JOIN SQLCAR_CARREC.MODEL   IN SQLCAR_ORIGIN TO ALL SQLCAR_BODY.MODEL     IN SQLCAR_BODY   AS J3
JOIN SQLCAR_BODY.BODYTYPE  IN SQLCAR_ORIGIN TO ALL SQLCAR_SPECS.BODYTYPE IN SQLCAR_SPECS  AS J4
-RUN

TABLE FILE SQLCAR_ORIGIN
SUM
MIN.LENGTH
MIN.WIDTH
MIN.HEIGHT

SEATS
DEALER_COST
RETAIL_COST
SALES

BY COUNTRY
BY CAR
BY MODEL
BY BODYTYPE
END
-RUN

-*-- Run non-SQL Report --------------------------------------------------------

TABLE FILE CAR
SUM
LENGTH
WIDTH
HEIGHT

SEATS
DEALER_COST
RETAIL_COST
SALES

BY COUNTRY
BY CAR
BY MODEL
BY BODYTYPE
END
-RUN

I, alas, no longer have use for a fine tooth comb, so I hope someone else has, just to verify this code.

The FURminator may be a good idea.

Many thanks for that Francis...certainly plenty to work with there.

Below is the code we use to create a MSSQL table, then I use dialogue manager to branch around the create to use the fex as the load job from here on out. I have to make four passes at the data because in my flat file each store type is a separate field for each record and I want a separate record for each store type:
[CODE
]-* File create_gmdisplay_table.fex
-SET &ECHO=ALL;
SET ASNAMES = ON
-* Truncate the convenience display_data. This is faster than deleting the rows.
ENGINE SQLMSS SET DEFAULT_CONNECTION Missouri
-RUN

SQL SQLMSS
-*-GOTO SKIP_CREATE
CREATE TABLE DS4WMaster.IMPORTER.gm_display_data
(
CV_DISP_PER CHAR(2) NOT NULL,
DP_TYPE CHAR(3) NOT NULL,
ITEM_UPC CHAR(12) NOT NULL,
DPYEAR CHAR(4) NOT NULL,
BEGINDATE CHAR(8) NOT NULL,
ENDDATE CHAR(8) NOT NULL,
STORETYPE CHAR(2) NOT NULL,
CURR_FLAG CHAR(1) NOT NULL
)
-SKIP_CREATE
truncate table DS4WMaster.IMPORTER.gm_display_data;

commit;
END
-RUN
APP FI GMCONVDISPLAY DISK /webfocus/gm_convdisplay.csv
-RUN

DEFINE FILE GMCONVDISPLAY
TDATE/YYMD=&YYMD;
BDATE/I8YYMD=EDIT(BEG_DATE);
BDATE1/YYMD=BDATE;
BEGDATE/HYYMDs=HDTTM(BDATE1, 8, 'HYYMDs');
EDATE/I8YYMD=EDIT(END_DATE);
EDATE1/YYMD=EDATE;
ENDDATE/HYYMDs=HDTTM(EDATE1, 8, 'HYYMDs');
CUR/A1=IF TDATE GE BDATE1 AND TDATE LE EDATE1 THEN '1' ELSE '0';
END
TABLE FILE GMCONVDISPLAY
PRINT DISPLAY_PERIOD AS 'CV_DISP_PER'
DISPLAY_TYPE AS 'DP_TYPE'
COMPUTE ITEM_UPC/A12=EDIT(ITEMUPC,'$999999999999');
DPYEAR
BEG_DATE AS 'BEGINDATE'
END_DATE AS 'ENDDATE'
COMPUTE STORETYPE/A2 = 'AF';
CUR AS 'CURR_FLAG'
WHERE AAFES EQ '1'
ON TABLE HOLD AS HLDDISP_AF FORMAT FOCUS
END
-RUN
TABLE FILE GMCONVDISPLAY
PRINT DISPLAY_PERIOD AS 'CV_DISP_PER'
DISPLAY_TYPE AS 'DP_TYPE'
COMPUTE ITEM_UPC/A12=EDIT(ITEMUPC,'$999999999999');
DPYEAR
BEG_DATE AS 'BEGINDATE'
END_DATE AS 'ENDDATE'
COMPUTE STORETYPE/A2 = 'CG';
CUR AS 'CURR_FLAG'
WHERE CG EQ '1'
ON TABLE HOLD AS HLDDISP_CG FORMAT FOCUS
END
-RUN
TABLE FILE GMCONVDISPLAY
PRINT DISPLAY_PERIOD AS 'CV_DISP_PER'
DISPLAY_TYPE AS 'DP_TYPE'
COMPUTE ITEM_UPC/A12=EDIT(ITEMUPC,'$999999999999');
DPYEAR
BEG_DATE AS 'BEGINDATE'
END_DATE AS 'ENDDATE'
COMPUTE STORETYPE/A2 = 'MC';
CUR AS 'CURR_FLAG'
WHERE MC EQ '1'
ON TABLE HOLD AS HLDDISP_MC FORMAT FOCUS
END
-RUN
TABLE FILE GMCONVDISPLAY
PRINT DISPLAY_PERIOD AS 'CV_DISP_PER'
DISPLAY_TYPE AS 'DP_TYPE'
COMPUTE ITEM_UPC/A12=EDIT(ITEMUPC,'$999999999999');
DPYEAR
BEG_DATE AS 'BEGINDATE'
END_DATE AS 'ENDDATE'
COMPUTE STORETYPE/A2 = 'NX';
CUR AS 'CURR_FLAG'
WHERE NX EQ '1'
ON TABLE HOLD AS HLDDISP_NX FORMAT FOCUS
END
-RUN
USE
HLDDISP_AF AS HLDDISP_AF
HLDDISP_CG AS HLDDISP_AF
HLDDISP_MC AS HLDDISP_AF
HLDDISP_NX AS HLDDISP_AF
END
TABLE FILE HLDDISP_AF
PRINT CURR_FLAG
BY CV_DISP_PER
BY DP_TYPE
BY ITEM_UPC
BY DPYEAR
BY BEGINDATE
BY ENDDATE
BY STORETYPE
ON TABLE HOLD AS HLDGMDATA
END
-RUN

MODIFY FILE GM_DISPLAY_DATA
FIXFORM FROM HLDGMDATA
MATCH *
ON MATCH REJECT
ON NOMATCH INCLUDE
DATA ON HLDGMDATA
END
[/CODE]

Hope this helps.

As you've already seen, this can be done via WebFocus, and there is another tool that was specifically created for this purpose - Data Migrator. It is a separate product so cost and licensing could make using WebFocus the better option for you.

Good Luck.

Jessica Bottone

Thanks for your response too Jessica.

Ive so far used the example Francis kindly provided.

Was feeling great when I got a test sample of 100 records (only 1 x a16 field) into a table. Then I tried increasing this number & it started crashing the agent.

So from being really happy...im hitting the buffers again.

I had read some things about migration. However, dont think I will be able to wangle any funds from the company at the moment.