Insert Records into a Database
You can use the ODBC output engine to insert information into a database through ODBC.
The ODBC output engine uses an XSL template to extract data from Media Server records. Your XSL template must produce a file which contains a list of transactions to run. Apart from the last, each transaction must end with the delimiter ==END==
, followed by a new line character.
Each transaction can contain one or more queries, separated by new line characters, and each query should have the following format:
- A statement to run.
- A list of alternating, tab-separated, column types and column values.
The following is an example post-XML file that contains five transactions, each with a single query.
insert into segment(uuid, sessionToken, startTime, endTime) VALUES (?, ?, ?, ?); string 67589a12-d7fe-44c6-915f-ad36b20e39da string MTYuMjguOTQuMTMxOjE0MDAwOlBST0NFU1M6MTQ2MDM3MzY4OTMxNzAxNDMyODM0MDcw bigint 1460373884855305 bigint 1460373899855305
==END==
insert into speech(segmentId, startTime, duration, text, confidence) VALUES (?, ?, ?, ?, ?); string 67589a12-d7fe-44c6-915f-ad36b20e39da bigint 1460373898073305 bigint 450000 string satellite double 0
==END==
insert into speech(segmentId, startTime, duration, text, confidence) VALUES (?, ?, ?, ?, ?); string 67589a12-d7fe-44c6-915f-ad36b20e39da bigint 1460373898523305 bigint 370000 string images double 0
==END==
insert into speech(segmentId, startTime, duration, text, confidence) VALUES (?, ?, ?, ?, ?); string 67589a12-d7fe-44c6-915f-ad36b20e39da bigint 1460373898893305 bigint 90000 string are double 0
==END==
insert into speech(segmentId, startTime, duration, text, confidence) VALUES (?, ?, ?, ?, ?); string 67589a12-d7fe-44c6-915f-ad36b20e39da bigint 1460373898983305 bigint 420000 string released double 0
TIP: Media Server only executes valid queries. A valid query must have a statement to run, must have the same number of column types and column values, and must not attempt to insert data into more than 100 columns. If a query results in an error, the entire transaction is rolled back.
To see an example XSL transformation that converts information into the correct format, see configurations/xsl/toODBC.xsl
in the Media Server installation folder.