[Cb5.0] SIMBA ODBC를 통한 SQL Server 연결된 서버에서의 Couchbase 원격 쿼리 호출

/*******************************************************************************************************************
-- Title : [Cb5.0] SIMBA ODBC를 통한 SQL Server 연결된 서버에서의 Couchbase 원격 쿼리 호출
-- Reference : simba.com
-- Key word : linked server 연결된 서버 4-partname 4 part name 오픈 쿼리 openquery n1ql N1QL 
*******************************************************************************************************************/

■ SIMBA Couchbase ODBC Driver 설정
    o dbrang.tistory.com/1349

■ CRUD Script

/********************************************************************
-- LINKED_CB 조회 (SELECT)
********************************************************************/
 
/*
-- 4-partname 호출 vs. OPENQUERY 호출
*/
SELECT TOP 10  *
FROM LINKED_CB.cbdefault.test.test;
 
SELECT *
FROM OPENQUERY(LINKED_CB, 'SELECT * FROM test.test LIMIT 10');
 
 
/*
-- OPENQUERY내 쿼리문 개체명 사용
*/
SELECT *
FROM OPENQUERY(LINKED_CB, 
'SELECT * FROM cbdefault.test.test LIMIT 10');                       -- 정상
 
SELECT *
FROM OPENQUERY(LINKED_CB, 'SELECT * FROM test.test LIMIT 10');       -- 정상
 
SELECT *
FROM OPENQUERY(LINKED_CB, 'SELECT * FROM test LIMIT 10');            -- 정상
 
 
/*
-- 출력 건수 제약 (TOP, LIMIT)
*/
SELECT TOP (10) *
FROM LINKED_CB.cbdefault.test.test;                                  -- LIMIT 사용하면 에러남
 
SELECT *
FROM OPENQUERY(LINKED_CB, 'SELECT * FROM test.test LIMIT 10');       -- LIMIT 가능
 
SELECT *
FROM OPENQUERY(LINKED_CB, 'SELECT TOP 10 * FROM test.test');         -- TOP n 가능
 
SELECT *
FROM OPENQUERY(LINKED_CB, 'SELECT TOP (10) * FROM test.test');       -- TOP (n) 불가
 
 
/*
-- OPENQUERY시 쿼리 종결자(세미콜론) 사용하면 에러남
*/
SELECT *
FROM OPENQUERY(LINKED_CB, 'SELECT * FROM test.test LIMIT 10;');      -- 에러
 
SELECT *
FROM OPENQUERY(LINKED_CB, 'SELECT * FROM test.test LIMIT 10');       -- 정상
 
 
/*
-- WHERE 조건문 
*/
SELECT *
FROM LINKED_CB.cbdefault.test.test
WHERE skey = '3515531000183KO-1';
 
SELECT *
FROM OPENQUERY(LINKED_CB, 
'SELECT * FROM test.test WHERE skey = ''3515531000183KO'''); 
 
SELECT *
FROM OPENQUERY(LINKED_CB, 
'SELECT * FROM test.test use keys [''3515531000183KO'']');            -- 에러/N1QL은 안되는 듯
 
 
/********************************************************************
-- LINKED_CB 저장 (INSERT)
********************************************************************/
/*
<OLE DB 공급자 "MSDASQL"이(가) 필요한 트랜잭션 인터페이스를 제공하지 않으므로 
요청한 작업을 수행할 수 없습니다>라는 에러 발생시..
    "연결된 서버 > 공급자 > MSDASQL > 속성"에서
     Inprocess 허용, 트랜잭션되지 않은 업데이트가 체크되어야 함.
*/

INSERT INTO LINKED_CB.cbdefault.test.test (skey, ab, bt)
VALUES ('3515531000183KO-4', 'ab ab ab', 'bt bt bt');
 
SELECT *
FROM LINKED_CB.cbdefault.test.test
WHERE skey = '3515531000183KO-4';
 
INSERT OPENQUERY(LINKED_CB, 'SELECT skey, ab, bt, cl FROM cbdefault.test.test')
VALUES ('3515531000183KO-5', 'ab ab ab', 'bt bt bt ', 'cl cl cl');
 
SELECT *
FROM OPENQUERY(LINKED_CB, 
'SELECT * FROM test.test WHERE skey LIKE ''3515531000183%'''); 
 
 
/********************************************************************
-- LINKED_CB 수정 (UPDATE)
********************************************************************/
 
UPDATE LINKED_CB.cbdefault.test.test
SET ab = 'ab ab ab ab ab ab ab'
WHERE skey = '3515531000183KO-2';
 
SELECT *
FROM LINKED_CB.cbdefault.test.test
WHERE skey = '3515531000183KO-2';
 
UPDATE OPENQUERY(LINKED_CB, 'SELECT * FROM test.test WHERE skey = ''3515531000183KO-4''')
SET bt = '{"a1":"1111", "a2":"2222"}'
 
SELECT *
FROM OPENQUERY(LINKED_CB, 'SELECT * FROM test.test WHERE skey = ''3515531000183KO-4'''); 
 
 
/********************************************************************
-- LINKED_CB 삭제 (DELETE)
********************************************************************/
SELECT *
FROM OPENQUERY(LINKED_CB, 'SELECT * FROM test.test WHERE skey LIKE ''3515531000183%'''); 
 
DELETE FROM LINKED_CB.cbdefault.test.test
WHERE skey = '3515531000183KO-5';
 
SELECT *
FROM OPENQUERY(LINKED_CB, 'SELECT * FROM test.test WHERE skey LIKE ''3515531000183%'''); 
 
DELETE FROM OPENQUERY(LINKED_CB, 'SELECT * FROM test.test WHERE skey = ''3515531000183KO-4'''); 
 
SELECT *
FROM OPENQUERY(LINKED_CB, 'SELECT * FROM test.test WHERE skey LIKE ''3515531000183%''');