SQL Import Query

Construct a SQL query to retrieve content and metadata from columns in a database. Each row of data represents a document.

Each database document store can have only one SQL query. A single SQL query can import one or all of your database documents into a document store, provided that the documents are all in a single database and that no authentication or authorization constraints require you to make multiple queries. For example, to specify more than one user name and password, or more than one host, construct more than one SQL query, which means one database document store for each SQL query.

When constructing an import SQL statement, the following column aliases have specific meaning. These columns are not case sensitive:

Table 1. SQL Column Aliases
Column aliases	Description
DOC_REF	A unique token usually a primary-key column, by which you can refer to a document for updates and deletions.
DOC_CONTENT	The body of the document. DOC_CONTENT can be the content from the TEXT or VARCHAR fields, or if used in conjunction with a content type value, it can be any of the supported MIME types.
DOC_CONTENT_TYPE	The content type (or MIME type) of the document. When content is contained in the database in a binary format, DOC_CONTENT_TYPE provides the additional information required to decode it. For example, if document content is UTF-8 encoded, plain text, then the content type is `text/plain; charset=UTF-8`. Similarly, if the content field contains PDF bytes, the content type is `application/pdf`. When the document content is binary and no content type is specified, Sybase Search attempts to decode it as plain text using the JRE default character set.
DOC_LINK	A link to an external document on a file system. The link must be an absolute path, visible to Sybase Search. The document properties (where present) are extracted as metadata, and Sybase Search uses the text as the document’s body text.

Sybase Search supports the following SQL types:

tinyint
smallint
integer
bigint
real
float
double
char
varchar – you can define the maximum length of varchar.

Note:

Sybase Search treats all column names and aliases, other than those listed above, as metadata and saves the information with the document as its metadata. To index metadata, each column's name and type must be known by the metadata manager. This means you must configure the metadata in an XML file, and restart the container before indexing.You are not required to supply metadata; however, as a best practice, Sybase suggests that you should supply a document TITLE, which is shown on the document search results page.

Example of a SQL query

The following example of a SQL query shows how a recruitment agency might import the resumés of current candidates:

SELECT ID 
						AS DOC_REF,    																									/*INT*/
			   PROFILE AS DOC_CONTENT,       										/*VARCHAR*/       
						CV AS DOC_CONTENT_2           										/*BLOB*/
						CV_MIME AS DOC_CONTENT_TYPE_2,
			  	FIRST_NAME + ' ' + LAST_NAME AS TITLE,
						PREF_SALARY          																			/*FLOAT*/ 
FROM         
					CANDIDATES 
WHERE
					LIVE=1

The example shows how the primary key column ID is used as a document reference, and how the document content (body text) is composed from both VARCHAR text and document bytes in a BLOB column. In this example, the MIME type of each document is stored in the database. Also, a title is constructed from the first and last name of the candidate, and the preferred salary is saved as metadata.