全文检索功能

测试环境准备

--创建支持全文搜索（BTS）的数据表和索引
EXECUTE PROCEDURE IFX_ALLOW_NEWLINE(t);
CREATE TABLE boats(docid integer, xml_data lvarchar(4096)); 
INSERT INTO boats values(1, 
	<boat> 
		<skipper>Captain Jack</skipper> 
		<boatname>Black Pearl</boatname> 
	</boat> );
INSERT INTO boats values(2, 
	<boat> 
		<skipper>Captain Jack</skipper> 
		<boatname>The Queen Annes Revenge</boatname> 
	</boat> );
insert into boats values(3,"Sino-US leaders meeting on the sidelines of gathering in Japan expected to ease tensions

President Xi Jinpings upcoming trip to Japan for the G20 Summit will reinforce international consensus on upholding multilateralism and an open economy amid rising protectionism that looms large over the global economy, observers said.

They said Xis upcoming meeting with United States President Donald Trump on the sidelines of the summit could also de-escalate bilateral trade tensions between the worlds two largest economies.

Xi will be in Japan from Thursday to Saturday to attend the 14th G20 Summit in Osaka at the invitation of Japanese Prime Minister Shinzo Abe, Foreign Ministry spokesman Lu Kang said on Sunday.");
insert into boats values(4,In addition to the original Japanese version, an all-star cast-dubbed Mandarin version has also been released.);
insert into boats values(5,The sun is shining over the global economy
全球经济复苏可持续性存疑);
insert into boats values(6,The 太阳 is shining over the global economy
全球经济复苏可持续性存疑);
--xmltags支持对xml查询
CREATE INDEX boats_bts ON boats(xml_data bts_lvarchar_ops) USING bts(xmltags="(skipper,boatname)") IN btsSBS;
drop index boats_bts;
--analyzer支持全文检索
CREATE INDEX boats_bts ON boats(xml_data bts_lvarchar_ops) 
USING bts(analyzer="boatname:cjk") IN btsSBS;

执行全文检索

SELECT xml_data FROM boats WHERE bts_contains(xml_data, skipper:black);
SELECT xml_data FROM boats WHERE bts_contains(xml_data, Japanese);
SELECT xml_data FROM boats WHERE bts_contains(xml_data, over the global economy);
SELECT xml_data FROM boats WHERE bts_contains(xml_data, "They said Xis upcoming meeting with United States President Donald Trump on the sidelines of the summit could also de-escalate bilateral trade tensions");
SELECT xml_data FROM boats WHERE bts_contains(xml_data, "They said Xis upcoming meeting with United States President Donald Trump on the sidelines of the summit could also de-escalate bilateral trade tensions");
SELECT xml_data FROM boats WHERE bts_contains(xml_data, "太阳 is shining");
SELECT xml_data FROM boats WHERE bts_contains(xml_data, Japanese AND "Foreign Ministry spokesman Lu Kang");
SELECT xml_data FROM boats WHERE bts_contains(xml_data, "太阳 is shining" OR Japanese);
SELECT xml_data FROM boats WHERE bts_contains(xml_data, "全球经济" AND NOT "太阳");

SPL游标和动态SQL

除了ESQL/C支持游标和动态SQL外，SPL（存储过程和存储函数）也支持。

存储过程示例

DROP PROCEDURE selectBoat;

CREATE PROCEDURE selectBoat(id INTEGER)
RETURNING INTEGER, LVARCHAR(4096);
	DEFINE psql VARCHAR(250);
	DEFINE docid INTEGER;
	DEFINE data LVARCHAR(4096);
	LET psql = "select docid,xml_data from boats where docid < ?"; 
	PREPARE stmt FROM psql;
	DECLARE cust_cur cursor FOR stmt;
	OPEN cust_cur USING id;
	WHILE (1 = 1)
		FETCH cust_cur INTO docid, data;
		IF (SQLCODE != 100) THEN
			RETURN docid, data WITH RESUME;
		ELSE
			EXIT;
		END IF
	END WHILE
	CLOSE cust_cur;
	FREE cust_cur;
	FREE stmt;
END PROCEDURE;

EXECUTE PROCEDURE selectBoat(10);