-- 1. ソースDBでテストテーブル作成
drop table if exists tab1;
create table tab1(
col1 BIGINT not null
, col2 DECIMAL(5,2)
, col3 CHAR(100)
, col4 VARCHAR(100)
, col5 DATETIME
, col6 BINARY(3)
, col7 VARBINARY(3)
);
-- 2. BV作成
select creation_vql
from GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW()
where data_source_name = 'ds01'
and catalog_name = 'test'
and table_name = 'tab1'
and base_view_name = 'b_tab1'
and folder ='/02bv'
and database_name = 'db01'
;
CREATE OR REPLACE WRAPPER JDBC b_tab1
FOLDER = '/02bv'
DATASOURCENAME=db01.ds01
CATALOGNAME='test'
RELATIONNAME='tab1'
OUTPUTSCHEMA (
col1 = 'col1' :'java.lang.Long' (OPT) (sourcetyperadix='10', sourcetypesize='19', description='', sourcetypeid='-5', sourcetypename='BIGINT') NOT NULL SORTABLE,
col2 = 'col2' :'java.math.BigDecimal' (OPT) (sourcetypedecimals='2', sourcetyperadix='10', sourcetypesize='5', description='', sourcetypeid='3', sourcetypename='DECIMAL') SORTABLE,
col3 = 'col3' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='100', description='', sourcetypeid='1', sourcetypename='CHAR') SORTABLE,
col4 = 'col4' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='100', description='', sourcetypeid='12', sourcetypename='VARCHAR') SORTABLE,
col5 = 'col5' :'java.time.LocalDateTime' (OPT) (sourcetyperadix='10', sourcetypesize='19', description='', sourcetypeid='93', sourcetypename='DATETIME') SORTABLE,
col6 = 'col6' :'java.sql.Blob' (OPT) (sourcetyperadix='10', sourcetypesize='3', description='', sourcetypeid='-2', sourcetypename='BINARY') NOT SORTABLE,
col7 = 'col7' :'java.sql.Blob' (OPT) (sourcetyperadix='10', sourcetypesize='3', description='', sourcetypeid='-3', sourcetypename='VARBINARY') NOT SORTABLE
);
CREATE OR REPLACE TABLE b_tab1 I18N jp (
col1:long (notnull, sourcetyperadix = '10', sourcetypesize = '19', description = '', sourcetypeid = '-5'),
col2:decimal (sourcetypedecimals = '2', sourcetyperadix = '10', sourcetypesize = '5', description = '', sourcetypeid = '3'),
col3:text (sourcetyperadix = '10', sourcetypesize = '100', description = '', sourcetypeid = '1'),
col4:text (sourcetyperadix = '10', sourcetypesize = '100', description = '', sourcetypeid = '12'),
col5:timestamp (sourcetyperadix = '10', sourcetypesize = '19', description = '', sourcetypeid = '93'),
col6:blob (sourcetyperadix = '10', sourcetypesize = '3', description = '', sourcetypeid = '-2'),
col7:blob (sourcetyperadix = '10', sourcetypesize = '3', description = '', sourcetypeid = '-3')
)
FOLDER = '/02bv'
CACHE OFF
TIMETOLIVEINCACHE DEFAULT
ADD SEARCHMETHOD b_tab1(
I18N jp
CONSTRAINTS (
ADD col1 (ANY) OPT ANY
ADD col2 (ANY) OPT ANY
ADD col3 (ANY) OPT ANY
ADD col4 (ANY) OPT ANY
ADD col5 (ANY) OPT ANY
ADD col6 NOS ZERO ()
ADD col7 NOS ZERO ()
)
OUTPUTLIST (col1, col2, col3, col4, col5, col6, col7
)
WRAPPER (jdbc b_tab1)
);
-- 3. BVカラム情報取得
select
database_name
, view_name
, ordinal_position
, column_name
, column_vdp_type
, column_sql_type
, column_sql_type_code
, column_size
, column_decimals
, column_radix
, column_is_primary_key
, column_is_nullable
, column_remarks
, column_is_autoincrement
, column_is_generated
, column_vdp_type_database
from GET_VIEW_COLUMNS('db01','b_tab1')
order by ordinal_position
;
-- 4. IFV作成
CONNECT DATABASE db01;
CREATE OR REPLACE FOLDER '/03iv';
CREATE OR REPLACE FOLDER '/03iv/111';
CREATE OR REPLACE FOLDER '/03iv/111/222';
CREATE OR REPLACE INTERFACE VIEW i_tab1 (
col1:long ,
col2:decimal ,
col3:text ,
col4:text ,
col5:timestamp ,
col6:blob ,
col7:blob
)
SET IMPLEMENTATION db01.b_tab1
FOLDER = '/03iv/111/222';
