SQLines SQL Converter - Command Line Reference

SQLines SQL Converter tool allows you to convert database schema (DDL), queries and DML statements, views, stored procedures, packages, functions and triggers between databases.

SQLines converts SQL statements located in one or more files, not from a database. You can run SQLines from a command line. Executables files are sqlines.exe and ./sqlines on Windows and Linux, respectively.

Note. Parameters are case-insensitive, and you can specify them in any order.

The following general parameters are available:

-s and -t option allow you to specify the source and target database types (SQL dialects), respectively.

Use the following values:

For example, specify the following command line to convert script.sql file from MySQL to Oracle:

The result will be script_out.sql file in the current directory.

When -stdin option is set, the converter reads the input from STDIN and sends the result to STDOUT.

• Send source SQL from another program

You can run the tool as follows:

The tool reads input from STDIN sent by another program, performs the conversion and outputs the result to STDOUT:

Note. If you use echo command on Windows, do not specify double-quotes.

• Typing source SQL

Run the tool as follows:

and start typing SQL code for conversion. When it's done, press Enter + Ctrl-D on Linux, or Enter + Ctrl-Z on Windows to start the conversion. You can see the converted result in STDOUT.

Some databases (SQL Server, Sybase i.e.) support 3-part identifiers: database.schema.name

  -- Table in schema HR in database ORG
  CREATE TABLE org.hr.dept (name VARCHAR(70));

When -remd option is set in the command line, the database name is removed:

Target script:

  -- Table is without ORG reference now
  CREATE TABLE hr.dept (name VARCHAR(70));

By default, the option is not set, so the database name is not removed. This option applies to 3-part identifiers only.

Often a SQL statement contains a schema reference:

  -- Table in schema HR 
  CREATE TABLE hr.dept (name VARCHAR(70));

When -rems option is set in the command line, the schema name is removed:

Target script:

  -- Table in the default schema 
  CREATE TABLE dept (name VARCHAR(70));

By default, the option is not set. Note that -smap and -omapf options override -rems option.

You can rename or remove the specified schemas using -smap option.

  -- Table in schema HR 
  CREATE TABLE hr.dept (name VARCHAR(70));
 
  -- Table in schema SALES 
  CREATE TABLE sales.product (name VARCHAR(70));

Let's rename schema HR and remove SALES in the target scripts:

Target script:

  -- Schema renamed to HR2 
  CREATE TABLE hr2.dept (name VARCHAR(70));
 
  -- Schema name SALES removed 
  CREATE TABLE product (name VARCHAR(70));

Note that -omapf option overrides -smap option.

You can transform the identifier using -ident option, by specifying words database, schema and object and put any delimiters or characters in the template.

  -- Table in schema HR 
  CREATE TABLE hr.dept (name VARCHAR(70));
 
  -- Table in schema SALES in database ORG
  CREATE TABLE org.sales.product (name VARCHAR(70));

Let's convert identifiers using template: database.schema_object i.e. merging schema and table name with underscore as the delimiter between them:

Target script:

  -- Schema merged with table name
  CREATE TABLE hr_dept (name VARCHAR(70));
 
  -- Schema merged with table name
  CREATE TABLE org.sales_product (name VARCHAR(70));

Note that -omapf option overrides -ident option. Also ident option is applied after -remd, -rems and -smap options.

You can rename the specified database objects (tables, views, functions, procedures etc.) using an object mapping file specified by -omapf option.

By default, the tool uses sqlines_omap.txt file.

  -- Tables in schema HR 
  CREATE TABLE hr.dept (name VARCHAR(70));
  CREATE TABLE hr.emp (name VARCHAR(70));
 
  -- Table in schema SALES 
  CREATE TABLE sales.product (name VARCHAR(70));

Mapping is specified in the text file in the following format:

-- Comment
hr.dept, hr_new.department

Let's use -omapf, -rems and -smap options as follows:

Target script:

  -- Tables were in source schema HR 
  CREATE TABLE hr_new.department (name VARCHAR(70));
  CREATE TABLE hr2.emp (name VARCHAR(70));
 
  -- Table was in source schema SALES 
  CREATE TABLE product (name VARCHAR(70));

You can see that:

• Table HR.DEPT was renamed to HR_NEW.DEPARTMENT according to the object mapping file. Note that -omapf option overrides -smap and -rems options.

• Table HR.EMP was renamed to HR2.EMP according to the schema name mapping specified by -smap option. Note that -smap option overrides -rems option.

• Table SALES.PRODUCT was changed to PRODUCT according to -rems option.

SQLines SQL Converter tries to resolve the column data types from the context, but it is not always possible.

You can specify a file containing information about table columns and their data types. This is helpful to convert PL/SQL %TYPE attributes to SQL Server or MySQL i.e.

  DECLARE 
     id dept.department_id%TYPE; 
 
  SELECT department_id INTO id 
    FROM dept 
    WHERE name = 'SALES';

Metadata is specified in the text file in the following format:

-- Syntax (one mapping per line):
-- table, column, data_type
dept, department_id, integer

You can use -meta option as follows:

Note that you have to specify the source data type in this file, not the target data type you want to get after the conversion. By default, the tool uses sqlines_meta.txt file.

SQLines SQL Converter provides the default conversion for data types, but you can redefine it.

You can provide your custom data type mapping using an data type mapping file specified by -dtmapf option. By default, the tool uses sqlines_dtmap.txt file.

  CREATE TABLE cities (
     id      INT, 
     name VARCHAR(70));

Mapping is specified in the text file in the following format:

-- Comment
-- source_datatype(length, scale), target_datatype
INT, BIGINT

We specified to map INT data type to BIGINT.

Target script:

  CREATE TABLE cities (
     id      BIGINT, 
     name VARCHAR(70));

Feel free to contact us at support@sqlines.com.