Document Type | Technical Information
Category | Interface/Integration
Document Number | TIITI032
Overview
Once the Tibero and MS-SQL Gateway setup is complete, you can actually create and use the DBLink.
This article explains how to verify Gateway connection, create DBLink syntax, and how to query data and perform DML tests using DBLink.
Method
Verify Gateway Connection
Verify direct connection to MS-SQL through Gateway
- Once the Gateway is ready, you can check if it runs properly before creating DBLink by using tbSQL.
- You can connect to the MS-SQL account and perform DML operations such as select.
$ tbsql tibero/tmax@msgate tbSQL 5 Copyright (c) 2008, 2009, 2011, 2012 Tibero Corporation. All rights reserved. Connected to SQL SERVER GATEWAY using msgate. SQL> select * from t1; id ------------------------------------------------------ qwe 1 row selected.
Note
If it does not execute as above, check for environmental issues such as firewall settings on the MS-SQL installation machine and whether the TCP/IP interface is enabled.
Create DBLink
Connect with the account that will use the DBLink and create it.
$ tbsql tibero/tmax SQL>CREATE DATABASE LINK mslink CONNECT TO mssql IDENTIFIED BY 'mssql' USING 'msgate'; Database link 'MSLINK' created.
Specify the DBLink name (mslink), user account (mssql), password, and the DSN in the USING clause (msgate) according to your environment.
Data Query and DML Test
Use the MS-SQL DBLink to query data and perform DML tests.
SQL> select * from t1@mslink;
id
-------------------------------------------------------
qwe
1 row selected.
SQL> insert into t1@mslink values ('100');
1 row inserted.
SQL> select * from t1@mslink;
id
-------------------------------------------------------
qwe
100
2 rows selected.If you do not know the name of the business table, you can query the list of tables using the following method.
SQL> select * from information_schema.tables@mslink;