dmtolpeko/sqlines

SQLines Open Source Database Migration Tools

sqlines's Issues

where can I find the sqlines.exe

where can I find the sqlines.exe?

MSSQL to MariaDb migration Problem

Constraint queries of generated file for migrating from MsSql to MariaDb is wrong.

Does sqlines support view migration from oracle to mysql?

Oracle to MSSQL: Failed to transfer extended property containing single quotes

I'm trying to migrate an Oracle table which has a COMMENT on it which contains a single quote ( ' ). These quotes need to be escaped by repeating the quote, like:

Oracle:
COMMENT ON TABLE MyUser.MyTable IS 'This is my ''quoted'' comment';

SQLines translates this to

MSSQL:
EXECUTE sp_addextendedproperty 'Comment', 'This is my 'quoted' comment', 'user', MySchema, 'table', MyTable;

This fails because the value in the MSSQL statement is not escaped.

issues transferring MS-SQL tables containing periods

I've noticed that the tool doesn't seem to notice when a MS-SQL table contains a period.

Audit.ExemptionStatus is transferred as ExemptionStatus to MariaDB.
Audit.RecoveryInfoType is transferred as RecoveryInfoType to MariaDB.

Not only is this inaccurate and losses critical information, it can cause a failure to transfer data when you have another table called Hardware.ExemptionStatus. It only sees "ExceptionStatus" and fails b/c it already has a table called that...

Yes, using periods in table names isn't a good idea, but there's plenty of applications which do this and so it needs to be taken into account. I don't have the option to rename them all.

My expectation would be to convert the period to an underscore or another acceptable character to MariaDB/MySQL.

sqlines -stdin cannot work

]I have tried in Ubuntu 18 and WIN10, when I use -stdin in the command line, the process will keep running and without anything return back. I have seen details Command line Reference, while the 'how to use' in command line shows different thing, it does not mention that -stdin can be used. I am confusing now, please tell me how to use -stdin=''! thanks a lot!

Oracle to Mysql datatype

Hello, i' m trying to convert a simple ALTER TABLE from oracle to Mysql. VARCHAR2 datatype seems to be not allowed in Mysql but the tool wont convert it to VARCHAR

Any plan to support Vertica database?

Since there’re many vertica database users, Vertica database need to be supported. Thanks.

Oracle to SQL Server trunc doesn't translate left hand side

Hi, thanks for this library! I'm testing out some Oracle to MSSQL translations and given the following input:

select * from mytable where trunc(mydate) = trunc(sysdate)

I get the output:

select * from mytable where trunc(mydate) = convert(datetime, convert(date,getdate()))

So the right hand side is correct but I would expect trunc(mydate) to translate to convert(datetime, convert(date, mydate)) for a final output of:

select * from mytable where convert(datetime, convert(date,mydate)) = convert(datetime, convert(date,getdate()))

Informix ELIF not converted toward PostgreSQL

PostgreSQL plpgsql does not support ELIF, but such construct is kept unconverted when going from Informix to PostgreSQL.

host with ipv6 not support

sqldata -sd="oracle, ...." -td="mysql, xxx/xxx@[2002:ac1f:91c5:1::bd59]:3306,mysql" -t=table

error occur

MySQL - Error: Unknown MySQL server host '[2002' (2) (Failed, 2 ms)

Unable to Build on Ubuntu

While running build_all64.sh in sqlparser directory. I'm getting these lines of error:

root@163fee1d569a:/Data/sqlines/sqlparser# ./build_all64.sh
datatypes.cpp: In member function 'bool SqlParser::ParseDatetimeType(Token*)':
datatypes.cpp:1715:12: warning: variable 'first_month' set but not used [-Wunused-but-set-variable]
Token first_month = NULL;
^
datatypes.cpp:1716:12: warning: variable 'first_day' set but not used [-Wunused-but-set-variable]
Token first_day = NULL;
^
datatypes.cpp:1717:12: warning: variable 'first_hour' set but not used [-Wunused-but-set-variable]
Token first_hour = NULL;
^
datatypes.cpp:1718:12: warning: variable 'first_minute' set but not used [-Wunused-but-set-variable]
Token first_minute = NULL;
^
datatypes.cpp:1719:12: warning: variable 'first_second' set but not used [-Wunused-but-set-variable]
Token first_second = NULL;
^
datatypes.cpp:1720:12: warning: variable 'first_fraction' set but not used [-Wunused-but-set-variable]
Token first_fraction = NULL;
^
datatypes.cpp:1725:12: warning: variable 'second_year' set but not used [-Wunused-but-set-variable]
Token second_year = NULL;
^
datatypes.cpp:1726:12: warning: variable 'second_month' set but not used [-Wunused-but-set-variable]
Token second_month = NULL;
^
datatypes.cpp: In member function 'bool SqlParser::ParseDecfloatType(Token)':
datatypes.cpp:2163:12: warning: variable 'precision' set but not used [-Wunused-but-set-variable]
Token precision = NULL;
^
datatypes.cpp: In member function 'bool SqlParser::ParseIntervalType(Token)':
datatypes.cpp:2738:12: warning: variable 'first_month' set but not used [-Wunused-but-set-variable]
Token first_month = NULL;
^
datatypes.cpp:2739:12: warning: variable 'first_day' set but not used [-Wunused-but-set-variable]
Token first_day = NULL;
^
datatypes.cpp:2740:12: warning: variable 'first_hour' set but not used [-Wunused-but-set-variable]
Token first_hour = NULL;
^
datatypes.cpp:2741:12: warning: variable 'first_minute' set but not used [-Wunused-but-set-variable]
Token first_minute = NULL;
^
datatypes.cpp:2742:12: warning: variable 'first_second' set but not used [-Wunused-but-set-variable]
Token first_second = NULL;
^
datatypes.cpp:2743:12: warning: variable 'first_fraction' set but not used [-Wunused-but-set-variable]
Token first_fraction = NULL;
^
datatypes.cpp: In member function 'bool SqlParser::ParseUnicodeType(Token)':
datatypes.cpp:5766:12: warning: variable 'size' set but not used [-Wunused-but-set-variable]
Token size = NULL;
^
datatypes.cpp:5767:12: warning: variable 'close' set but not used [-Wunused-but-set-variable]
Token close = NULL;
^
datatypes.cpp: In member function 'bool SqlParser::ParseVarchar2Type(Token, int)':
datatypes.cpp:6534:12: warning: variable 'close' set but not used [-Wunused-but-set-variable]
Token close = NULL;
^
db2.cpp: In member function 'bool SqlParser::ParseDb2GeneratedClause(Token, Token, Token, Token, Token, Token, Token, bool)':
db2.cpp:60:8: warning: variable 'exists' set but not used [-Wunused-but-set-variable]
bool exists = false;
^
functions.cpp: In member function 'bool SqlParser::ParseFunctionChr(Token, Token)':
functions.cpp:2444:9: warning: variable 'nchar' set but not used [-Wunused-but-set-variable]
Token nchar = NULL;
^
functions.cpp: In member function 'bool SqlParser::ParseFunctionConvert(Token, Token)':
functions.cpp:2792:10: warning: variable 'source_charset' set but not used [-Wunused-but-set-variable]
Token source_charset = NULL;
^
functions.cpp: In member function 'bool SqlParser::ParseFunctionConvertVarchar(Token, Token*, Token*)':
functions.cpp:3001:9: warning: variable 'close_bracket' set but not used [-Wunused-but-set-variable]
Token close_bracket = NULL;
^
functions.cpp: In member function 'bool SqlParser::ParseFunctionCsconvert(Token, Token*)':
functions.cpp:3221:9: warning: variable 'source_charset' set but not used [-Wunused-but-set-variable]
Token source_charset = NULL;
^
functions.cpp: In member function 'bool SqlParser::ParseFunctionLocate(Token, Token*)':
functions.cpp:7708:9: warning: variable 'unit' set but not used [-Wunused-but-set-variable]
Token unit = NULL;
^
functions.cpp: In member function 'bool SqlParser::ParseFunctionPatindex(Token, Token*)':
functions.cpp:9339:9: warning: variable 'usng' set but not used [-Wunused-but-set-variable]
Token usng = NULL;
^
functions.cpp: In member function 'bool SqlParser::ParseFunctionSubstring(Token, Token*)':
functions.cpp:11089:9: warning: variable 'comma' set but not used [-Wunused-but-set-variable]
Token comma = NULL;
^
functions.cpp: In member function 'bool SqlParser::ParseFunctionXmlforest(Token, Token*)':
functions.cpp:13713:9: warning: variable 'alias' set but not used [-Wunused-but-set-variable]
Token alias = NULL;
^
informix.cpp: In member function 'void SqlParser::InformixConvertReturning(Token, Token*)':
informix.cpp:279:11: warning: variable 'start' set but not used [-Wunused-but-set-variable]
Token start = NULL;
^
informix.cpp: In member function 'bool SqlParser::ParseInformixUpdateStatistics(Token)':
informix.cpp:554:9: warning: variable 'high' set but not used [-Wunused-but-set-variable]
Token high = NULL;
^
language.cpp: In member function 'bool SqlParser::ParseKeyConstraint(Token, Token*, Token*, ListW&, bool, ListWM*)':
language.cpp:1622:7: warning: variable 'unique_index' set but not used [-Wunused-but-set-variable]
bool unique_index = false;
^
oracle.cpp: In member function 'bool SqlParser::ParseOraclePartitions(Token*)':
oracle.cpp:373:11: warning: variable 'template_' set but not used [-Wunused-but-set-variable]
Token template_ = NULL;
^
oracle.cpp: In member function 'bool SqlParser::ParseOraclePartitionsBy(Token)':
oracle.cpp:426:9: warning: variable 'list' set but not used [-Wunused-but-set-variable]
Token list = NULL;
^
oracle.cpp: In member function 'bool SqlParser::RecognizeOracleDateFormat(Token, TokenStr&)':
oracle.cpp:1080:10: warning: variable 'dmy' set but not used [-Wunused-but-set-variable]
bool dmy = false;
^
postgresql.cpp: In member function 'bool SqlParser::ParsePostgresBodyEnd()':
postgresql.cpp:121:9: warning: variable 'exists' set but not used [-Wunused-but-set-variable]
bool exists = false;
^
select.cpp: In member function 'bool SqlParser::ParseJoinClause(Token*, Token*, bool, ListW*)':
select.cpp:970:9: warning: variable 'fourth' set but not used [-Wunused-but-set-variable]
Token fourth = NULL;
^
select.cpp: In member function 'bool SqlParser::ParseValuesStatement(Token, int*)':
select.cpp:1490:35: warning: operation on '* result_sets' may be undefined [-Wsequence-point]
result_sets = (result_sets)++;
^
storage.cpp: In member function 'bool SqlParser::ParseTempTableOptions(Token, Token*, Token**, bool*)':
storage.cpp:53:11: warning: variable 'preserve' set but not used [-Wunused-but-set-variable]
Token preserve = NULL;
^
statements.cpp: In member function 'bool SqlParser::ParseCreateStatement(Token, int*, bool*)':
statements.cpp:341:9: warning: variable 'external' set but not used [-Wunused-but-set-variable]
Token external = NULL;
^
statements.cpp: In member function 'bool SqlParser::ParseCaseStatement(Token, bool)':
statements.cpp:1058:9: warning: variable 'end_case' set but not used [-Wunused-but-set-variable]
Token end_case = NULL;
^
statements.cpp: In member function 'bool SqlParser::ParseCreateAuxiliary(Token, Token*)':
statements.cpp:1701:9: warning: variable 'tablespace' set but not used [-Wunused-but-set-variable]
Token tablespace = NULL;
^
statements.cpp:1708:9: warning: variable 'stores_table' set but not used [-Wunused-but-set-variable]
Token stores_table = NULL;
^
statements.cpp:1715:9: warning: variable 'col' set but not used [-Wunused-but-set-variable]
Token col = NULL;
^
statements.cpp: In member function 'bool SqlParser::ParseCreateFunction(Token, Token, Token, Token*)':
statements.cpp:1858:10: warning: variable 'name' set but not used [-Wunused-but-set-variable]
Token name = NULL;
^
statements.cpp: In member function 'bool SqlParser::ParseCreateTrigger(Token, Token*, Token*)':
statements.cpp:2605:9: warning: variable 'statement' set but not used [-Wunused-but-set-variable]
Token statement = NULL;
^
statements.cpp: In member function 'bool SqlParser::ParseExecuteStatement(Token)':
statements.cpp:3494:10: warning: variable 'stmt_id' set but not used [-Wunused-but-set-variable]
Token stmt_id = NULL;
^
statements.cpp: In member function 'bool SqlParser::ParseCreateSequence(Token, Token*)':
statements.cpp:3998:8: warning: variable 'exists' set but not used [-Wunused-but-set-variable]
bool exists = false;
^
statements.cpp: In member function 'bool SqlParser::ParseCreateStogroup(Token*, Token*)':
statements.cpp:4151:7: warning: variable 'exists' set but not used [-Wunused-but-set-variable]
bool exists = false;
^
statements.cpp: In member function 'bool SqlParser::ParseDeclareCursor(Token*, Token*, Token*)':
statements.cpp:4455:12: warning: variable 'cut_start' set but not used [-Wunused-but-set-variable]
Token cut_start = for_;
^
statements.cpp: In member function 'bool SqlParser::ParseForStatement(Token, int)':
statements.cpp:5265:7: warning: variable 'select_loop' set but not used [-Wunused-but-set-variable]
bool select_loop = false;
^
statements.cpp: In member function 'bool SqlParser::ParseGrantStatement(Token*)':
statements.cpp:6554:10: warning: variable 'function' set but not used [-Wunused-but-set-variable]
Token function = NULL;
^
statements.cpp: In member function 'bool SqlParser::ParseRaiseStatement(Token)':
statements.cpp:7406:9: warning: variable 'error_code' set but not used [-Wunused-but-set-variable]
Token error_code = NULL;
^
statements.cpp:7407:9: warning: variable 'isam_error' set but not used [-Wunused-but-set-variable]
Token isam_error = NULL;
^
statements.cpp:7408:9: warning: variable 'text' set but not used [-Wunused-but-set-variable]
Token text = NULL;
^
statements.cpp: In member function 'bool SqlParser::ParseSignalStatement(Token)':
statements.cpp:8170:9: warning: variable 'message' set but not used [-Wunused-but-set-variable]
Token message = NULL;
^
statements.cpp:8171:9: warning: variable 'close' set but not used [-Wunused-but-set-variable]
Token close = NULL;
^
statements.cpp: In member function 'bool SqlParser::ParseUpdateStatement(Token)':
statements.cpp:8329:12: warning: variable 'alias' set but not used [-Wunused-but-set-variable]
Token alias = NULL;
^
statements.cpp: In member function 'bool SqlParser::ParseProcedureParameters(Token, int, Token)':
statements.cpp:8595:9: warning: variable 'first' set but not used [-Wunused-but-set-variable]
Token first = NULL;
^
statements.cpp: In member function 'bool SqlParser::ParseProcedureOptions(Token)':
statements.cpp:9594:11: warning: variable 'ext_name' set but not used [-Wunused-but-set-variable]
Token ext_name = NULL;
^
stats.cpp: In member function 'void Stats::LogFuncCall(Token, Token*, std::string&)':
stats.cpp:41:82: warning: field precision specifier '.*' expects argument of type 'int', but argument 5 has type 'size_t {aka long unsigned int}' [-Wformat=]
fprintf(file, "%s,%d,%.*s,", cur_file.c_str(), name->line, name->len, name->str);

@dmtolpeko Can you please help?
^

Informix to PostgreSQL: FUNCTION RETURNING unhandled

This valid Informix function does not convert correctly to PostgreSQL:

CREATE FUNCTION test_returning()
RETURNING VARCHAR(3), VARCHAR(3) ;
   RETURN '0','1';
END FUNCTION

The output shows some memory issue too, as it has mungled text:

CREATE OR REPLACE FUNCTION test_returning()
RETURNING VARCHAR(3), VARCHAR(3) ; AS $$

BEGIN
   RETURN '0','1';
END; 
$$ LANGUAGE plpgsql;
AGE plpgsql;
GE plpgsql;
   
END CREATE FUNCTION;
LANGUAGE plpgsql;

Oracle to MSSQL 2016: Failed to create extended property

I'm trying to migrate an entire Oracle database to SQL Server, and I'm running into several issues. I will log them separately here.

I'm trying to migrate an Oracle table which has a COMMENT on it, like:

COMMENT ON TABLE MyUser.MyTable IS 'This is my comment';

Migrating this table fails when it tries to convert the comment. The error is "Object is invalid. Extended properties are not permitted on 'MyUser.MyTable', or the object does not exist".

The failing SQL that SQLines generated is:

EXECUTE sp_addextendedproperty 'Comment', 'This is my comment', 'user', MyUser, 'table', [MyTable];

The string 'user' in this command is wrong, and should be changed to 'schema' to make it work:

EXECUTE sp_addextendedproperty 'Comment', 'This is my comment', 'schema', MyUser, 'table', [MyTable];

Unable to build sqldata binary on Linux

sqldata/build_all64.sh gives errors because of missing header files. Would you share the dependencies?

THIS PROJECT IS DEAD, any active fork ?

or so it looks by lack of feedback or any other action against any of the old contributions.
I file this ticket aiming to gather interest in an active fork...

Oracle CLOB to MySQL LONGTEXT poor performance

Migrating from Oracle to MySQL. Table contains CLOB data. Performance is very poor.

For 12Gb data, mostly CLOB, migration takes well over an hour. Performance is not predictable, sometimes it is much slower.

The tool displays extremely asymmetric read and write times:

     Rows read:     742680 (602336 rows/sec)
     Rows written:  734428 (1399 rows/sec, 1.7 GB, 3.4 MB/sec)
     Transfer time: 8 min 47 sec (1.2 sec read, 8 min 45 sec write)

Partial trace:

2020-02-13 08:57:48.699 OCI Fetch() Entered
2020-02-13 08:57:48.699 MySQL/C LOAD DATA INFILE Read callback() Entered, buffer size is 8192 bytes
2020-02-13 08:57:48.700 MySQL/C LOAD DATA INFILE Read callback() Waiting for data
2020-02-13 08:57:48.763 OCI Fetch() Left, retcode 0
2020-02-13 08:57:48.875 MySQL/C TransferRows() Entered
2020-02-13 08:57:48.876 MySQL/C LOAD DATA INFILE Read callback() Data arrived
2020-02-13 08:57:48.882 MySQL/C LOAD DATA INFILE Read callback() Left - middle of batch, 8192 bytes chunk, 1885681617 bytes all
2020-02-13 08:57:48.882 MySQL/C LOAD DATA INFILE Read callback() Entered, buffer size is 8192 bytes
2020-02-13 08:57:48.886 MySQL/C LOAD DATA INFILE Read callback() Left - middle of batch, 8192 bytes chunk, 1885689809 bytes all
2020-02-13 08:57:48.887 MySQL/C LOAD DATA INFILE Read callback() Entered, buffer size is 8192 bytes
2020-02-13 08:57:48.889 MySQL/C LOAD DATA INFILE Read callback() Left - middle of batch, 8192 bytes chunk, 1885698001 bytes all
2020-02-13 08:57:48.891 MySQL/C LOAD DATA INFILE Read callback() Entered, buffer size is 8192 bytes
2020-02-13 08:57:48.894 MySQL/C LOAD DATA INFILE Read callback() Left - middle of batch, 8192 bytes chunk, 1885706193 bytes all
2020-02-13 08:57:48.895 MySQL/C LOAD DATA INFILE Read callback() Entered, buffer size is 8192 bytes
2020-02-13 08:57:48.896 MySQL/C LOAD DATA INFILE Read callback() Left - middle of batch, 8192 bytes chunk, 1885714385 bytes all
2020-02-13 08:57:48.897 MySQL/C LOAD DATA INFILE Read callback() Entered, buffer size is 8192 bytes

support Java Native Interface

support Java Native Interface

SQL script conversion consumes all memory in sqlines

sqlines runs until Mac OS kills the process after consuming all memory.

This SQL script reproduces the problem:

https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/AutoGuessingForeignKeyConstraints.sql

./sqlines -s=sql -t=postgresql -in=AutoGuessingForeignKeyConstraints.sql

sqlines exit code does not reflect success/failure

sqlines exits with code 244 when it succeeds, scripts would expect a 0 instead

Oracle to MySQL data migration fails for a specific dataset

Hi,

We are experiencing an issue when migrating an Oracle database to MySQL: All migration went smoothly but a single table errors out. We could pinpoint the issue to the data transfer itself, DDL updates seem fine and the table is created properly in MySQL.

We are looking for other ways to detect the issue.

This is what we have at this point:

Sqlines version:

./sqldata 

SQLines Data 3.1.771 x86_64 Linux - Database Migration Tool.
Copyright (c) 2017 SQLines. All Rights Reserved.

When performing the migration for the single problematic table:

-- with 'oracle.PERMITREQUEST' the problematic table
./sqldata -sd=<oracle-connectionstring> -td=<mysql-connectionstring> -t=oracle.PERMITREQUEST -smap=oracle_schema:mysql_schema -ss=1

This outputs:

Connecting to databases (324 ms)
  Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production (Ok, 324 ms)
  MySQL 5.7.17 MySQL Community Server (GPL) x86_64 Linux (Ok, 26 ms)

Reading the database schema (1 table, 223 ms)

Transferring database (1 session):

  MOBILE_STAGING.PERMITREQUEST - Started (1 of 1, session 1)
  MOBILE_STAGING.PERMITREQUEST - Open cursor (171 rows read, 193 ms, session 1)
  MOBILE_STAGING.PERMITREQUEST - Drop target table (112 ms, session 1)
  MOBILE_STAGING.PERMITREQUEST - Create target table (15 ms, session 1)
  MOBILE_STAGING.PERMITREQUEST - In progress (session 1)
     Rows read:     8379 (2603 rows/sec)
     Rows written:  8208 (15724 rows/sec, 5.8 MB, 11.1 MB/sec)
     Transfer time: 3.5 sec (3.2 sec read, 522 ms write)
  MOBILE_STAGING.PERMITREQUEST - In progress (session 1)
     Rows read:     20007 (3316 rows/sec)
     Rows written:  19836 (13871 rows/sec, 13.5 MB, 9.4 MB/sec)
     Transfer time: 6.7 sec (6.0 sec read, 1.4 sec write)
  MOBILE_STAGING.PERMITREQUEST - In progress (session 1)
     Rows read:     34542 (3945 rows/sec)
     Rows written:  34371 (13044 rows/sec, 23.5 MB, 8.9 MB/sec)
     Transfer time: 9.8 sec (8.8 sec read, 2.6 sec write)
  MOBILE_STAGING.PERMITREQUEST - In progress (session 1)
     Rows read:     45657 (3931 rows/sec)
     Rows written:  45486 (13116 rows/sec, 31.2 MB, 9.0 MB/sec)
     Transfer time: 12.9 sec (11.6 sec read, 3.5 sec write)
  MOBILE_STAGING.PERMITREQUEST - Data transfer failed
	
     Rows read:     52155 (4118 rows/sec)
     Rows written:  51984 (13367 rows/sec, 35.7 MB, 9.2 MB/sec)
     Transfer time: 14.2 sec (12.7 sec read, 3.9 sec write)

Failed tables (1 table):

    1. MOBILE_STAGING.PERMITREQUEST
MOBILE_STAGING.PERMITREQUEST

Please contact us at [email protected] for any assistance.

Summary:

  Tables:        1 (0 Ok, 1 failed)
  Target DDL:    2 (2 Ok, 0 failed)
  Rows read:     52155
  Rows written:  51984 (171 row difference)
  Transfer time: 14.2 sec (3669 rows/sec, 35.7 MB, 2.5 MB/sec)

With tracing enabled, we don't see much useful details in the trace-file, except for the fact that the data doesn't seem to be fetched correctly ("Data arrived" leads to "Left with EOF condition"):
(see complete trace file here: sqldata.trc.zip)

// ...
2018:01:24 16:46:43.494 MySQL/C LOAD DATA INFILE Read callback() Left - middle of batch, 8175 bytes chunk, 37515830 bytes all
2018:01:24 16:46:43.494 MySQL/C LOAD DATA INFILE Read callback() Entered, buffer size is 8192 bytes
2018:01:24 16:46:43.495 MySQL/C LOAD DATA INFILE Read callback() Left - middle of batch, 8192 bytes chunk, 37524022 bytes all
2018:01:24 16:46:43.495 MySQL/C LOAD DATA INFILE Read callback() Entered, buffer size is 8192 bytes
2018:01:24 16:46:43.495 MySQL/C LOAD DATA INFILE Read callback() Left - middle of batch, 8192 bytes chunk, 37532214 bytes all
2018:01:24 16:46:43.495 MySQL/C LOAD DATA INFILE Read callback() Entered, buffer size is 8192 bytes
2018:01:24 16:46:43.496 MySQL/C LOAD DATA INFILE Read callback() Left - middle of batch, 8192 bytes chunk, 37540406 bytes all
2018:01:24 16:46:43.496 MySQL/C LOAD DATA INFILE Read callback() Entered, buffer size is 8192 bytes
2018:01:24 16:46:43.496 MySQL/C LOAD DATA INFILE Read callback() Left - Batch fully loaded, 3651 bytes last chunk, 126501 bytes batch, 37544057 bytes all
2018:01:24 16:46:43.496 MySQL/C TransferRows() Left
2018:01:24 16:46:43.496 MySQL/C LOAD DATA INFILE Read callback() Entered, buffer size is 8192 bytes
2018:01:24 16:46:43.496 MySQL/C LOAD DATA INFILE Read callback() Waiting for data
2018:01:24 16:46:43.535 OCI Fetch() Left
2018:01:24 16:46:43.536 MySQL/C CloseBulkTransfer() Entered
2018:01:24 16:46:43.536 MySQL/C LOAD DATA INFILE Read callback() Data arrived
2018:01:24 16:46:43.536 MySQL/C LOAD DATA INFILE Read callback() Left with EOF condition
2018:01:24 16:46:43.536 MySQL/C LOAD DATA INFILE - End callback()
2018:01:24 16:46:43.558 MySQL/C StartLoadDataInfileS() Left
2018:01:24 16:46:43.559 MySQL/C CloseBulkTransfer() Left
2018:01:24 16:46:43.559 OCI CloseCursor() Entered
2018:01:24 16:46:43.561 OCI CloseCursor() Left

In Oracle, this is the table definition for the table:

create table PERMITREQUEST
(
	ID NUMBER not null
		constraint PERMITREQUEST_PK
			primary key,
	PARTNER NUMBER not null,
	TYPE_ID NUMBER not null,
	SOURCEPERMIT NUMBER,
	DATESTART DATE,
	DATEEND DATE,
	CHARGE NUMBER,
	CUSTOMERS CLOB,
	LICENSEPLATES CLOB,
	ZONES CLOB,
	SETTINGS CLOB,
	DURATION VARCHAR2(255 char),
	GROUP_ID NUMBER,
	ACTION VARCHAR2(255 char),
	PERMIT NUMBER,
	ZONETYPES CLOB,
	CREATEDON DATE,
	CREATED_BY_CUSTOMER NUMBER,
	CREATED_BY_USER NUMBER,
	SOURCE VARCHAR2(20 char),
	CREATED_BY_ELOKET_USER NUMBER,
	PAYMENT_METHOD VARCHAR2(255 char),
	STATUS VARCHAR2(255 char),
	SEQUENCE VARCHAR2(30 char),
	SEQUENCE_ NUMBER(10),
	APPROVAL_MESSAGE CLOB,
	APPROVED VARCHAR2(255 char),
	CHARGE_AMOUNT NUMBER,
	CHARGE_CURRENCY VARCHAR2(5 char),
	CREATEDBY_ID NUMBER,
	CREATEDBY_NAME VARCHAR2(255 char),
	CREATEDBY_TYPE VARCHAR2(255 char),
	DELETED CHAR(1 char) default 'N',
	GOAL VARCHAR2(255 char),
	APPROVEDBY_ID NUMBER,
	APPROVEDBY_NAME VARCHAR2(255 char),
	APPROVEDBY_TYPE VARCHAR2(255 char),
	APPROVED_ON DATE,
	DECLINEDBY_ID NUMBER,
	DECLINEDBY_NAME VARCHAR2(255 char),
	DECLINEDBY_TYPE VARCHAR2(255 char),
	DECLINED_ON DATE,
	SCRAPPEDBY_ID NUMBER,
	SCRAPPEDBY_NAME VARCHAR2(255 char),
	SCRAPPEDBY_TYPE VARCHAR2(255 char),
	SCRAPPED_ON DATE
)

This might be an issue for this specific dataset. Maybe you can help us by providing some extra tips on how to add some more logging or debug the request to the Oracle database as this seems to be the issue?

Thanks a lot!

Compiling sqlines On Mac OS X

Hi,

Thanks for taking the time to develop this tool and making it open source. I really appreciate it.

I tried compile the sqlines binary on my Mac OS X machine and have a small issue that I hope you can help with.

My compiler is as follows:

$ g++ -v
Configured with: --prefix=/Library/Developer/CommandLineTools/usr --with-gxx-include-dir=/usr/include/c++/4.2.1
Apple LLVM version 8.0.0 (clang-800.0.42.1)
Target: x86_64-apple-darwin16.1.0
Thread model: posix
InstalledDir: /Library/Developer/CommandLineTools/usr/bin

If I run the build_all64.sh script all of the source files compile except for the ones that reference the #include <sys/io.h> header.

This header doesn't appear to be available on Mac OS X machines.

If I comment out the reference to this header, the files compile as expected, and the sqlines binary appears to work. I was able to use the binary to convert a SQL script from Oracle to MySQL syntax.

This is a long way of asking, am I potentially breaking anything by commenting out references to this header?

With many thanks for your time.

-Corey

Using -qf does not work if query is on same table

I am trying to migrate specific sets of data from Oracle to MySQL using sqlines -qf=queries

This is my queries file:

MY_DB.TABLE1, select * from MY_DB.TABLE1 where NAME like 'Jane%';
MY_DB.TABLE1, select * from MY_DB.TABLE1 where NAME like 'Mike%';

Only the first query is executed. The second query does not get executed, I'm assuming, because the table name is the same.

Is there a way to migrate specific sets of data from the same table in a list of multiple queries?

How can i compile Sqldata_w for Windows 64?

Hi,
I need to test this software without checkpoint at Sybase SQL Anywhere 16 with readonly user but the software do a "Checkpoint" at the first step.
Thank you.

informix to postgres: `WITH (NOT VARIANT)` unhandled

An Informix function like this:

CREATE FUNCTION crs_upperAnsi(avc VARCHAR(255) )
RETURNING VARCHAR(255) AS normalised
WITH (NOT VARIANT);
    -- Call function for macron and case insensitive functional indexes
    RETURN cf_ccl_upperAnsi(avc);
END FUNCTION;

Gets converted to this broken PostgreSQL code:

CREATE OR REPLACE FUNCTION crs_upperAnsi(avc VARCHAR(255) )
RETURNS VARCHAR(255) AS $$

BEGIN
WITH (NOT VARIANT);
    -- Call function for macron and case insensitive functional indexes
    RETURN cf_ccl_upperAnsi(avc);
END;
$$ LANGUAGE plpgsql;

See the WITH (NOT VARIANT) part, which makes PostgreSQL choke. Things work fine by removing that line.

POSTGRESQL does not allow keyword sp_addextendedproperty

Hello,

It seems that on this line:

sqlines/sqldata/sqldata.cpp

Line 497 in d132c7c

if(table_comments != NULL && _target_type != SQLDATA_MYSQL)

"everything not mysql" let create postgresql line:
EXECUTE sp_addextendedproperty 'Comment', ...

It fails when executed in postgreSQL databse.

informix to postgres: VARCHAR(100) function return removed

This informix function:

CREATE FUNCTION cf_cde_ParseRight( avc_ref VARCHAR(100) )
RETURNS VARCHAR(100) WITH (NOT VARIANT);
    
    DEFINE  li_pos      INTEGER;
    DEFINE  lvc_return  VARCHAR(100);
    
    LET lvc_return = UPPER(TRIM(avc_ref));
    IF NVL( lvc_return, '' ) != '' THEN
        LET li_pos = crs_InStr(1, lvc_return, ' & ');
        IF li_pos > 1 THEN
            LET lvc_return = SUBSTRING(lvc_return FROM li_pos + 3) ;
        END IF
    END IF;

    RETURN TRIM(lvc_return);
END FUNCTION;

Gets converted to this PostgreSQL function:

CREATE OR REPLACE FUNCTION cf_cde_ParseRight( avc_ref VARCHAR(100) )
RETURNS AS $$
 VARCHAR(100) WITH (NOT VARIANT);

    DECLARE  li_pos      INTEGER;
     lvc_return  VARCHAR(100);
BEGIN

    lvc_return := UPPER(TRIM(avc_ref));
    IF COALESCE( lvc_return, '' ) != '' THEN
        li_pos := crs_InStr(1, lvc_return, ' & ');
        IF li_pos > 1 THEN 
            lvc_return := SUBSTRING(lvc_return FROM li_pos + 3) ;
        END IF;
    END IF;

    RETURN TRIM(lvc_return);
END;
$$ LANGUAGE plpgsql;

See the missing return type in RETURNS AS $$

Teradata to PostgreSQL support

Can we migrate from Teradata to PostgreSQL using this tool?

informix to postgres: `create row type` is not converted

A working conversion would be -> create type NAME AS ( ... )

"mediumint(8) unsigned" mysql to oracle non translated

In Prestashop there are field declared "mediumint(8) unsigned" that are not translated to oracle

desc ps_specific_price;
+------------------------+-----------------------------+------+-----+---------+----------------+
| Field                  | Type                        | Null | Key | Default | Extra          |
+------------------------+-----------------------------+------+-----+---------+----------------+
| id_specific_price      | int(10) unsigned            | NO   | PRI | NULL    | auto_increment |
| id_specific_price_rule | int(11) unsigned            | NO   | MUL | NULL    |                |
| id_cart                | int(11) unsigned            | NO   | MUL | NULL    |                |
| id_product             | int(10) unsigned            | NO   | MUL | NULL    |                |
| id_shop                | int(11) unsigned            | NO   | MUL | 1       |                |
| id_shop_group          | int(11) unsigned            | NO   |     | NULL    |                |
| id_currency            | int(10) unsigned            | NO   |     | NULL    |                |
| id_country             | int(10) unsigned            | NO   |     | NULL    |                |
| id_group               | int(10) unsigned            | NO   |     | NULL    |                |
| id_customer            | int(10) unsigned            | NO   | MUL | NULL    |                |
| id_product_attribute   | int(10) unsigned            | NO   | MUL | NULL    |                |
| price                  | decimal(20,6)               | NO   |     | NULL    |                |
| from_quantity          | mediumint(8) unsigned       | NO   | MUL | NULL    |                |
| reduction              | decimal(20,6)               | NO   |     | NULL    |                |
| reduction_tax          | tinyint(1)                  | NO   |     | 1       |                |
| reduction_type         | enum('amount','percentage') | NO   |     | NULL    |                |
| from                   | datetime                    | NO   | MUL | NULL    |                |
| to                     | datetime                    | NO   | MUL | NULL    |                |
+------------------------+-----------------------------+------+-----+---------+----------------+

Building SQLines

../sqlparser/sqlparser.a doesn't exist for use by

sqlines/sqlines/build_all64.sh
and
sqlines/sqlines/build_all.sh

Add support for the VALUES() row constructor

I'm trying to convert this standard SQL syntax (as supported by e.g. PostgreSQL and SQL Server):

SELECT * FROM (VALUES(1)) t(a);
SELECT * FROM (VALUES(1, 2), (3, 4)) t(a, b);

to Oracle, for example:

SELECT 1 a FROM dual;
SELECT 1 a, 2 b FROM dual UNION ALL SELECT 3 a, 4 b FROM dual;

Unfortunately, the online version of the parser / translator (http://www.sqlines.com/online) doesn't do anything with my syntax.

-oracle_nls_lang CLI parameter not working

The -oracle_nls_lang CLI parameter does not seem to work, even though the code says it should:

sqlines/sqldata/sqlociapi.cpp

Line 89 in d132c7c

nls_lang = _parameters->Get("-oracle_nls_lang");

The intermediate data extracted from Oracle and stored in tab-delimited files shows that data is retrieved using the default Oracle client NLS_LANG encoding (system-dependent).

As an alternative, one may set the NLS_LANG environment variable manually before calling sqldata:

export NLS_LANG=FRENCH_FRANCE.WE8ISO8859P1

PS: that's irrelevant to the issue at hand, but since it is a common mistake, a reminder to potential future readers that NLS_LANG should match the target database encoding, NOT the source database encoding (see https://www.oracle.com/database/technologies/faq-nls-lang.html for more details), in order for Oracle database to properly convert data before passing it to the client (and thus ensure it is properly stored in the intermediate tab-delimited files, before insertion to the target database).

Error: SSL connection error

Informix to PG: fail to convert double-quote used for literal values

In informix you can use double-quotes to wrap literal values containing single quotes, but in PostgreSQL double-quotes are only for identifiers, never for literal. In PostgreSQL you can use dollar-quoting if you want to include single-ticks in a string, like:

SELECT $$I can use ' here$$

Oracle to MSSQL 2016: Default value for DATE column fails

I'm trying to migrate an entire Oracle database to SQL Server, and I'm running into several issues. I will log them separately here.

I'm trying to transfer an Oracle table which has a DATE type column with a DEFAULT value defined as to_date('01-01-1900', 'dd-mm-yyyy').

Minimal test case (Oracle):
create table TEST_DATE_DEFAULT (
Name DATE DEFAULT to_date('01-01-1900', 'dd-mm-yyyy')
);

While creating the table succeeds, you will get an error when it tries to add the default constraint: "Argument data type varchar is invalid for argument 3 of convert function."

The failing SQL that SQLines generated is:

ALTER TABLE [TEST_DATE_DEFAULT] ADD DEFAULT convert(DATETIME, '01-01-1900', 'dd-mm-yyyy')
FOR Name

This fails because the 3rd parameter of the CONVERT function should be an INT. See https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql .

Informix To SQL Server Data fails to transfer for lvarchar(8000) and bigger.

I am trying to transfer my database from Informix to SQL Server. However, any lvarchar column with a length of 8000 or greater does not transfer data. It creates the schema correctly converting the lvarchar -> varchar (max). However, the data does not transfer after the schema has been created. Any suggestions on how to fix this with config settings or is this a bug in the program.

Oracle to MSSQL 2016: failed to transfer dates before 01-01-1753

I'm trying to migrate an entire Oracle database to SQL Server, and I'm running into several issues. I will log them separately here.

I'm trying to transfer an Oracle table with a DATE type column, which contains several records with dates before 01-01-1753. Once SQLines encounters the first such record it fails this table with the error "[Microsoft][SQL Server Native Client 11.0]Datetime field overflow".

This happens because SQLines converted the Oracle "DATE" type column to MSSQL type "datetime", which does not support dates before January 1, 1753. [See https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql].

SQLines should instead, whenever possible, convert such columns to type "datetime2", which supports the full range of dates from 0001-01-01 through 9999-12-31. [See https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql]. This datatype is supported from MSSQL version 2008 and up.

long raw

sqldata support for SAP HANA?

The main sqldata page mentions support for SAP HANA, but I'm not finding any other mentions of HANA @ www.sqlines.com.

I've tried using -td=hana,/@:/<sid_or_tenant_db> but I'm presented with the error message:

Unknown database - Error: Target database type is unknown (Ok, 0ms)

Has anyone been able to get sqldata to work against a SAP HANA database?

Thanks,
markp

TO_CHAR conversion (Oracle -> SQL Server)

“Oracle” to “SQL Server” TO_CHAR is not being converted to a “CAST” or “CONVERT” as it should be. This is consistent online as well as in the Command line program.

Input given:

SELECT TO_CHAR(COALESCE(GPH2.SD_APP_DD,SYSDATE),'MM/DD/YYYY') reportdate
FROM DUAL GPH2

Output that was returned:
SELECT TO_CHAR(COALESCE(GPH2.SD_APP_DD,GETDATE()),'MM/DD/YYYY') reportdate

Version used to check locally: SQLines 3.1.113 - Windows 32-bit, 5.3 MB, July 02, 2018 (Most Recent Windows Release for SQL conversion only)

Unable to build

I am trying to build on Debian - here is a log of the errors generated:

$ uname -a
Linux computer 4.12.0-2-amd64 #1 SMP Debian 4.12.13-1 (2017-09-19) x86_64 GNU/Linux
$ g++ -v
Using built-in specs.
COLLECT_GCC=g++
COLLECT_LTO_WRAPPER=/usr/lib/gcc/x86_64-linux-gnu/7/lto-wrapper
OFFLOAD_TARGET_NAMES=nvptx-none
OFFLOAD_TARGET_DEFAULT=1
Target: x86_64-linux-gnu
Configured with: ../src/configure -v --with-pkgversion='Debian 7.2.0-7' --with-bugurl=file:///usr/share/doc/gcc-7/README.Bugs --enable-languages=c,ada,c++,go,brig,d,fortran,objc,obj-c++ --prefix=/usr --with-gcc-major-version-only --program-suffix=-7 --program-prefix=x86_64-linux-gnu- --enable-shared --enable-linker-build-id --libexecdir=/usr/lib --without-included-gettext --enable-threads=posix --libdir=/usr/lib --enable-nls --with-sysroot=/ --enable-clocale=gnu --enable-libstdcxx-debug --enable-libstdcxx-time=yes --with-default-libstdcxx-abi=new --enable-gnu-unique-object --disable-vtable-verify --enable-libmpx --enable-plugin --enable-default-pie --with-system-zlib --with-target-system-zlib --enable-objc-gc=auto --enable-multiarch --disable-werror --with-arch-32=i686 --with-abi=m64 --with-multilib-list=m32,m64,mx32 --enable-multilib --with-tune=generic --enable-offload-targets=nvptx-none --without-cuda-driver --enable-checking=release --build=x86_64-linux-gnu --host=x86_64-linux-gnu --target=x86_64-linux-gnu
Thread model: posix
gcc version 7.2.0 (Debian 7.2.0-7) 

$ cd /tmp/sqlines-master/sqlparser/
$ ./build_all64.sh 
datatypes.cpp: In member function ‘bool SqlParser::ParseTypedVariable(Token*, Token*)’:
datatypes.cpp:408:9: warning: this ‘else’ clause does not guard... [-Wmisleading-indentation]
         else
         ^~~~
datatypes.cpp:426:13: note: ...this statement, but the latter is misleadingly indented as if it were guarded by the ‘else’
             return true;
             ^~~~~~
datatypes.cpp: In member function ‘bool SqlParser::ParseDatetimeType(Token*)’:
datatypes.cpp:1646:12: warning: variable ‘first_month’ set but not used [-Wunused-but-set-variable]
     Token *first_month = NULL;
            ^~~~~~~~~~~
datatypes.cpp:1647:12: warning: variable ‘first_day’ set but not used [-Wunused-but-set-variable]
     Token *first_day = NULL;
            ^~~~~~~~~
datatypes.cpp:1648:12: warning: variable ‘first_hour’ set but not used [-Wunused-but-set-variable]
     Token *first_hour = NULL;
            ^~~~~~~~~~
datatypes.cpp:1649:12: warning: variable ‘first_minute’ set but not used [-Wunused-but-set-variable]
     Token *first_minute = NULL;
            ^~~~~~~~~~~~
datatypes.cpp:1650:12: warning: variable ‘first_second’ set but not used [-Wunused-but-set-variable]
     Token *first_second = NULL;
            ^~~~~~~~~~~~
datatypes.cpp:1651:12: warning: variable ‘first_fraction’ set but not used [-Wunused-but-set-variable]
     Token *first_fraction = NULL;
            ^~~~~~~~~~~~~~
datatypes.cpp:1656:12: warning: variable ‘second_year’ set but not used [-Wunused-but-set-variable]
     Token *second_year = NULL;
            ^~~~~~~~~~~
datatypes.cpp:1657:12: warning: variable ‘second_month’ set but not used [-Wunused-but-set-variable]
     Token *second_month = NULL;
            ^~~~~~~~~~~~
datatypes.cpp: In member function ‘bool SqlParser::ParseDecfloatType(Token*)’:
datatypes.cpp:2091:12: warning: variable ‘precision’ set but not used [-Wunused-but-set-variable]
     Token *precision = NULL;
            ^~~~~~~~~
datatypes.cpp: In member function ‘bool SqlParser::ParseIntervalType(Token*)’:
datatypes.cpp:2666:12: warning: variable ‘first_month’ set but not used [-Wunused-but-set-variable]
     Token *first_month = NULL;
            ^~~~~~~~~~~
datatypes.cpp:2667:12: warning: variable ‘first_day’ set but not used [-Wunused-but-set-variable]
     Token *first_day = NULL;
            ^~~~~~~~~
datatypes.cpp:2668:12: warning: variable ‘first_hour’ set but not used [-Wunused-but-set-variable]
     Token *first_hour = NULL;
            ^~~~~~~~~~
datatypes.cpp:2669:12: warning: variable ‘first_minute’ set but not used [-Wunused-but-set-variable]
     Token *first_minute = NULL;
            ^~~~~~~~~~~~
datatypes.cpp:2670:12: warning: variable ‘first_second’ set but not used [-Wunused-but-set-variable]
     Token *first_second = NULL;
            ^~~~~~~~~~~~
datatypes.cpp:2671:12: warning: variable ‘first_fraction’ set but not used [-Wunused-but-set-variable]
     Token *first_fraction = NULL;
            ^~~~~~~~~~~~~~
datatypes.cpp: In member function ‘bool SqlParser::ParseNtextType(Token*)’:
datatypes.cpp:4081:5: warning: this ‘else’ clause does not guard... [-Wmisleading-indentation]
     else
     ^~~~
datatypes.cpp:4089:9: note: ...this statement, but the latter is misleadingly indented as if it were guarded by the ‘else’
         if(Target(SQL_MARIADB, SQL_MYSQL))
         ^~
datatypes.cpp: In member function ‘bool SqlParser::ParseUnicodeType(Token*)’:
datatypes.cpp:5585:12: warning: variable ‘size’ set but not used [-Wunused-but-set-variable]
     Token *size = NULL;
            ^~~~
datatypes.cpp:5586:12: warning: variable ‘close’ set but not used [-Wunused-but-set-variable]
     Token *close = NULL;
            ^~~~~
datatypes.cpp: In member function ‘bool SqlParser::ParseVarchar2Type(Token*, int)’:
datatypes.cpp:6353:12: warning: variable ‘close’ set but not used [-Wunused-but-set-variable]
     Token *close = NULL;
            ^~~~~
db2.cpp: In member function ‘bool SqlParser::ParseDb2GeneratedClause(Token*, Token*, Token*, Token*, Token**, Token**, Token**, bool*)’:
db2.cpp:60:8: warning: variable ‘exists’ set but not used [-Wunused-but-set-variable]
   bool exists = false;
        ^~~~~~
functions.cpp: In member function ‘bool SqlParser::ParseFunction(Token*)’:
functions.cpp:664:5: warning: this ‘else’ clause does not guard... [-Wmisleading-indentation]
     else
     ^~~~
functions.cpp:674:2: note: ...this statement, but the latter is misleadingly indented as if it were guarded by the ‘else’
  if(exists)
  ^~
functions.cpp: In member function ‘bool SqlParser::ParseFunctionChr(Token*, Token*)’:
functions.cpp:2424:9: warning: variable ‘nchar’ set but not used [-Wunused-but-set-variable]
  Token *nchar = NULL;
         ^~~~~
functions.cpp: In member function ‘bool SqlParser::ParseFunctionConvert(Token*, Token*)’:
functions.cpp:2772:10: warning: variable ‘source_charset’ set but not used [-Wunused-but-set-variable]
   Token *source_charset = NULL;
          ^~~~~~~~~~~~~~
functions.cpp: In member function ‘bool SqlParser::ParseFunctionConvertVarchar(Token*, Token*, Token*)’:
functions.cpp:2974:9: warning: variable ‘close_bracket’ set but not used [-Wunused-but-set-variable]
  Token *close_bracket = NULL;
         ^~~~~~~~~~~~~
functions.cpp: In member function ‘bool SqlParser::ParseFunctionCsconvert(Token*, Token*)’:
functions.cpp:3194:9: warning: variable ‘source_charset’ set but not used [-Wunused-but-set-variable]
  Token *source_charset = NULL;
         ^~~~~~~~~~~~~~
functions.cpp: In member function ‘bool SqlParser::ParseFunctionLocate(Token*, Token*)’:
functions.cpp:7579:9: warning: variable ‘unit’ set but not used [-Wunused-but-set-variable]
  Token *unit = NULL;
         ^~~~
functions.cpp: In member function ‘bool SqlParser::ParseFunctionPatindex(Token*, Token*)’:
functions.cpp:9210:9: warning: variable ‘usng’ set but not used [-Wunused-but-set-variable]
  Token *usng = NULL;
         ^~~~
functions.cpp: In member function ‘bool SqlParser::ParseFunctionSubstring(Token*, Token*)’:
functions.cpp:10954:9: warning: variable ‘comma’ set but not used [-Wunused-but-set-variable]
  Token *comma = NULL;
         ^~~~~
functions.cpp: In member function ‘bool SqlParser::ParseFunctionVarcharFormatBit(Token*, Token*)’:
functions.cpp:13148:2: warning: this ‘if’ clause does not guard... [-Wmisleading-indentation]
  if(_target == SQL_ORACLE)
  ^~
functions.cpp:13152:3: note: ...this statement, but the latter is misleadingly indented as if it were guarded by the ‘if’
   Token::Remove(comma);
   ^~~~~
functions.cpp: In member function ‘bool SqlParser::ParseFunctionXmlforest(Token*, Token*)’:
functions.cpp:13578:9: warning: variable ‘alias’ set but not used [-Wunused-but-set-variable]
  Token *alias = NULL;
         ^~~~~
informix.cpp: In member function ‘void SqlParser::InformixConvertReturning(Token*, Token*)’:
informix.cpp:279:11: warning: variable ‘start’ set but not used [-Wunused-but-set-variable]
    Token *start = NULL;
           ^~~~~
informix.cpp: In member function ‘bool SqlParser::ParseInformixUpdateStatistics(Token*)’:
informix.cpp:554:9: warning: variable ‘high’ set but not used [-Wunused-but-set-variable]
  Token *high = NULL;
         ^~~~
language.cpp: In member function ‘bool SqlParser::ParseDefaultExpression(Token*, Token*, Token*, int)’:
language.cpp:1422:5: warning: this ‘if’ clause does not guard... [-Wmisleading-indentation]
     if(_target == SQL_HIVE)
     ^~
language.cpp:1425:2: note: ...this statement, but the latter is misleadingly indented as if it were guarded by the ‘if’
  return true;
  ^~~~~~
language.cpp: In member function ‘bool SqlParser::ParseKeyConstraint(Token*, Token*, Token*, ListW&, bool, ListWM*)’:
language.cpp:1607:7: warning: variable ‘unique_index’ set but not used [-Wunused-but-set-variable]
  bool unique_index = false;
       ^~~~~~~~~~~~
language.cpp: In member function ‘bool SqlParser::ParsePercentOperator(Token*)’:
language.cpp:3164:5: warning: this ‘else’ clause does not guard... [-Wmisleading-indentation]
     else
     ^~~~
language.cpp:3176:2: note: ...this statement, but the latter is misleadingly indented as if it were guarded by the ‘else’
  return true;
  ^~~~~~
mysql.cpp: In member function ‘bool SqlParser::ParseMySQLDelimiter(Token*)’:
mysql.cpp:51:5: warning: this ‘else’ clause does not guard... [-Wmisleading-indentation]
     else
     ^~~~
mysql.cpp:61:9: note: ...this statement, but the latter is misleadingly indented as if it were guarded by the ‘else’
         return true;
         ^~~~~~
mysql.cpp: In member function ‘bool SqlParser::ParseMyqlDefinerClause(Token*)’:
mysql.cpp:237:13: warning: this ‘else’ clause does not guard... [-Wmisleading-indentation]
             else
             ^~~~
mysql.cpp:266:17: note: ...this statement, but the latter is misleadingly indented as if it were guarded by the ‘else’
                 next = GetNextToken();
                 ^~~~
oracle.cpp: In member function ‘bool SqlParser::ParseOraclePartitions(Token*)’:
oracle.cpp:373:11: warning: variable ‘template_’ set but not used [-Wunused-but-set-variable]
    Token *template_ = NULL;
           ^~~~~~~~~
oracle.cpp: In member function ‘bool SqlParser::ParseOraclePartitionsBy(Token*)’:
oracle.cpp:426:9: warning: variable ‘list’ set but not used [-Wunused-but-set-variable]
  Token *list = NULL;
         ^~~~
oracle.cpp: In member function ‘bool SqlParser::ParseFunctionDbmsOutput(Token*, Token*)’:
oracle.cpp:618:5: warning: this ‘else’ clause does not guard... [-Wmisleading-indentation]
     else
     ^~~~
oracle.cpp:633:2: note: ...this statement, but the latter is misleadingly indented as if it were guarded by the ‘else’
  return true;
  ^~~~~~
oracle.cpp: In member function ‘bool SqlParser::ParseOracleCursorDeclaration(Token*, ListWM*)’:
oracle.cpp:941:5: warning: this ‘if’ clause does not guard... [-Wmisleading-indentation]
     if(cursors != NULL)
     ^~
oracle.cpp:945:2: note: ...this statement, but the latter is misleadingly indented as if it were guarded by the ‘if’
  if(_target == SQL_NETEZZA)
  ^~
oracle.cpp: In member function ‘bool SqlParser::ParseOracleRownumCondition(Token*, Token*, Token*, int*)’:
oracle.cpp:1024:27: error: ISO C++ forbids comparison between pointer and integer [-fpermissive]
  bool rownum2 = (first == false) ? second->Compare("rownum", L"rownum", 6) : false;
                           ^~~~~
postgresql.cpp: In member function ‘bool SqlParser::ParsePostgresBodyEnd()’:
postgresql.cpp:121:9: warning: variable ‘exists’ set but not used [-Wunused-but-set-variable]
    bool exists = false;
         ^~~~~~
select.cpp: In member function ‘bool SqlParser::ParseSelectFromClause(Token*, bool, Token**, Token**, int*, bool, ListW*)’:
select.cpp:886:37: error: ISO C++ forbids comparison between pointer and integer [-fpermissive]
   if(GetNextCharToken(',', L',') == false)
                                     ^~~~~
select.cpp: In member function ‘bool SqlParser::ParseValuesStatement(Token*, int*)’:
select.cpp:1481:17: warning: operation on ‘* result_sets’ may be undefined [-Wsequence-point]
    *result_sets = (*result_sets)++;
    ~~~~~~~~~~~~~^~~~~~~~~~~~~~~~~~
procedures.cpp: In member function ‘bool SqlParser::ParseSystemProcedure(Token*, Token*)’:
procedures.cpp:43:5: warning: this ‘else’ clause does not guard... [-Wmisleading-indentation]
     else
     ^~~~
procedures.cpp:53:2: note: ...this statement, but the latter is misleadingly indented as if it were guarded by the ‘else’
  return exists;
  ^~~~~~
storage.cpp: In member function ‘bool SqlParser::ParseTempTableOptions(Token*, Token**, Token**, bool*)’:
storage.cpp:53:11: warning: variable ‘preserve’ set but not used [-Wunused-but-set-variable]
    Token *preserve = NULL;
           ^~~~~~~~
sqlparser.cpp: In member function ‘void SqlParser::SetLang(const char*, bool)’:
sqlparser.cpp:68:5: warning: this ‘if’ clause does not guard... [-Wmisleading-indentation]
     if(value == NULL)
     ^~
sqlparser.cpp:71:2: note: ...this statement, but the latter is misleadingly indented as if it were guarded by the ‘if’
  short app = 0;
  ^~~~~
sqlparser.cpp:76:5: warning: this ‘if’ clause does not guard... [-Wmisleading-indentation]
     if(_stricmp(value, "cobol") == 0)
     ^~
sqlparser.cpp:79:2: note: ...this statement, but the latter is misleadingly indented as if it were guarded by the ‘if’
  if(source)
  ^~
statements.cpp: In member function ‘bool SqlParser::ParseStatement(Token*, int, int*)’:
statements.cpp:58:5: warning: this ‘else’ clause does not guard... [-Wmisleading-indentation]
     else
     ^~~~
statements.cpp:311:2: note: ...this statement, but the latter is misleadingly indented as if it were guarded by the ‘else’
  if(exists == false)
  ^~
statements.cpp: In member function ‘bool SqlParser::ParseCreateStatement(Token*, int*, bool*)’:
statements.cpp:341:9: warning: variable ‘external’ set but not used [-Wunused-but-set-variable]
  Token *external = NULL;
         ^~~~~~~~
statements.cpp: In member function ‘bool SqlParser::ParseCaseStatement(Token*, bool)’:
statements.cpp:1047:9: warning: variable ‘end_case’ set but not used [-Wunused-but-set-variable]
  Token *end_case = NULL;
         ^~~~~~~~
statements.cpp: In member function ‘bool SqlParser::ParseCreateTable(Token*, Token*)’:
statements.cpp:1445:5: warning: this ‘if’ clause does not guard... [-Wmisleading-indentation]
     if(semi == NULL && Target(SQL_ESGYNDB))
     ^~
statements.cpp:1449:2: note: ...this statement, but the latter is misleadingly indented as if it were guarded by the ‘if’
  Bookmark(BOOK_CT_END, table, last);
  ^~~~~~~~
statements.cpp: In member function ‘bool SqlParser::ParseCreateAuxiliary(Token*, Token*)’:
statements.cpp:1690:9: warning: variable ‘tablespace’ set but not used [-Wunused-but-set-variable]
  Token *tablespace = NULL;
         ^~~~~~~~~~
statements.cpp:1697:9: warning: variable ‘stores_table’ set but not used [-Wunused-but-set-variable]
  Token *stores_table = NULL;
         ^~~~~~~~~~~~
statements.cpp:1704:9: warning: variable ‘col’ set but not used [-Wunused-but-set-variable]
  Token *col = NULL;
         ^~~
statements.cpp: In member function ‘bool SqlParser::ParseCreateFunction(Token*, Token*, Token*, Token*)’:
statements.cpp:1847:10: warning: variable ‘name’ set but not used [-Wunused-but-set-variable]
   Token *name = NULL;
          ^~~~
statements.cpp: In member function ‘bool SqlParser::ParseCreateTrigger(Token*, Token*, Token*)’:
statements.cpp:2570:9: warning: variable ‘statement’ set but not used [-Wunused-but-set-variable]
  Token *statement = NULL;
         ^~~~~~~~~
statements.cpp: In member function ‘bool SqlParser::ParseExecuteStatement(Token*)’:
statements.cpp:3393:10: warning: variable ‘stmt_id’ set but not used [-Wunused-but-set-variable]
   Token *stmt_id = NULL;
          ^~~~~~~
statements.cpp: In member function ‘bool SqlParser::ParseCreateSequence(Token*, Token*)’:
statements.cpp:3897:8: warning: variable ‘exists’ set but not used [-Wunused-but-set-variable]
   bool exists = false;
        ^~~~~~
statements.cpp: In member function ‘bool SqlParser::ParseCreateStogroup(Token*, Token*)’:
statements.cpp:4050:7: warning: variable ‘exists’ set but not used [-Wunused-but-set-variable]
  bool exists = false;
       ^~~~~~
statements.cpp: In member function ‘bool SqlParser::ParseDeclareCursor(Token*, Token*, Token*)’:
statements.cpp:4348:12: warning: variable ‘cut_start’ set but not used [-Wunused-but-set-variable]
     Token *cut_start = for_;
            ^~~~~~~~~
statements.cpp: In member function ‘bool SqlParser::ParseForStatement(Token*, int)’:
statements.cpp:5263:5: warning: this ‘if’ clause does not guard... [-Wmisleading-indentation]
     if(open != NULL)
     ^~
statements.cpp:5267:2: note: ...this statement, but the latter is misleadingly indented as if it were guarded by the ‘if’
  Token *loop = GetNextWordToken("LOOP", L"LOOP", 4);
  ^~~~~
statements.cpp:5158:7: warning: variable ‘select_loop’ set but not used [-Wunused-but-set-variable]
  bool select_loop = false;
       ^~~~~~~~~~~
statements.cpp: In member function ‘bool SqlParser::ParseIfStatement(Token*, int)’:
statements.cpp:5849:22: error: ISO C++ forbids comparison between pointer and integer [-fpermissive]
     if(next_begin == false)
                      ^~~~~
statements.cpp: In member function ‘bool SqlParser::ParseUpdateStatement(Token*)’:
statements.cpp:8153:5: warning: this ‘else’ clause does not guard... [-Wmisleading-indentation]
     else
     ^~~~
statements.cpp:8166:2: note: ...this statement, but the latter is misleadingly indented as if it were guarded by the ‘else’
  while(true)
  ^~~~~
teradata.cpp: In member function ‘bool SqlParser::ParseTeradataPrimaryIndex(Token*, Token*, Token*, Token*)’:
teradata.cpp:201:5: warning: this ‘if’ clause does not guard... [-Wmisleading-indentation]
     if(_target == SQL_HIVE)
     ^~
teradata.cpp:204:2: note: ...this statement, but the latter is misleadingly indented as if it were guarded by the ‘if’
  return true;
  ^~~~~~
ar: oracle.o: No such file or directory

$ cd /tmp/sqlines-master/sqlines
$ ./build_all64.sh 
g++: error: ../sqlparser/sqlparser.a: No such file or directory

$ cd /tmp/sqlines-master/sqldata
$ ./build_all64.sh 
g++: error: ../sqlparser/sqlparser.a: No such file or directory

Am I doing something wrong?

Oracle to MSSQL: Failed to transfer foreign key relations

When transferring two tables MyTable1 and MyTable2, both within the same user/schema, from Oracle to MSSQL, SQLines (v3.1.337) generates a query similar to:

ALTER TABLE MySchema.[MyTable1] ADD CONSTRAINT [MyConstraint] FOREIGN KEY ([ID]) REFERENCES
MyTable2 ([ID])

This yield the error "Foreign key 'MyConstraint' references invalid table 'MyTable2'."

This fails because the table has been converted under the "MySchema" schema, and thus should be referred to as MySchema.MyTable2:

ALTER TABLE MySchema.[MyTable1] ADD CONSTRAINT [MyConstraint] FOREIGN KEY ([ID]) REFERENCES [MySchema].[MyTable2] ([ID])

Sybase ASE to Oracle: data corruption from Linux sqldata, but not Windows version

left hand side = SQLines Data 3.1.773 - Linux x86-64, 6.4 MB, February 12, 2018
right hand side = SQLines Data 3.1.771 - Windows 64-bit, 7.1 MB, January 04, 2018

IN Conversion (Oracle -> SQL Server)

For “Oracle” to “SQL Server” the “IN” is getting replaced with “dbo.IN” which isn’t going to work, trying to do many conversions and would like to use the command line tool instead of the online version.

Test version SQLines 3.1.113 - Windows 32-bit, 5.3 MB, July 02, 2018
(^^ is there a newer build for windows?)

Online converter works without issue without creating correct output.

Input

WITH George AS (SELECT B.*
FROM TABLE1 B
	INNER JOIN TABLE2 T2 ON
		1=1
		AND T2.AGENCY = B.AGENCY
		AND T2.REC_GROUP = B.REC_GROUP
		AND T2.REC_TYPE = B.REC_TYPE
		AND T2.REC_SUB_TYPE = B.REC_SUB_TYPE
		AND T2.REC_CATEGORY = B.REC_CATEGORY
		AND T2.REC_GROUP IN ('1','2','3','4')
		AND T2.REC_TYPE IN ('1','2','3','4')
		AND
		(
			(
				T2.REC_GROUP       ||
				'/'                  ||
					T2.REC_TYPE    ||
				'/'                  ||
					T2.REC_SUB_TYPE||
				'/'                  ||
					T2.REC_CATEGORY IN ( '1','2','3','4' )
			)
			OR
			(
				T2.REC_GROUP||
				'/'           ||
					T2.REC_TYPE = 'GEORGE'
			)
		)
WHERE
	1=1
	AND B.AGENCY = (SELECT AGENCY FROM PARAMS)
	AND UPPER(B.B1_ALT_ID) = (SELECT capid FROM PARAMS)
)
SELECT * FROM GEORGE

Output:

WITH George AS (SELECT B.*
FROM TABLE1 B
	INNER JOIN TABLE2 T2 ON
		1=1
		AND T2.AGENCY = B.AGENCY
		AND T2.REC_GROUP = B.REC_GROUP
		AND T2.REC_TYPE = B.REC_TYPE
		AND T2.REC_SUB_TYPE = B.REC_SUB_TYPE
		AND T2.REC_CATEGORY = B.REC_CATEGORY
		AND T2.REC_GROUP dbo.IN ('1','2','3','4')
		AND T2.REC_TYPE dbo.IN ('1','2','3','4')
		AND
		(
			(
				ISNULL(T2.REC_GROUP, '')       +
				'/'                  +
					ISNULL(T2.REC_TYPE, '')    +
				'/'                  +
					ISNULL(T2.REC_SUB_TYPE, '')+
				'/'                  +
					ISNULL(T2.REC_CATEGORY, '') dbo.IN ( '1','2','3','4' )
			)
			OR
			(
				ISNULL(T2.REC_GROUP, '')+
				'/'           +
					ISNULL(T2.REC_TYPE, '') = 'GEORGE'
			)
		)
WHERE
	1=1
	AND B.AGENCY = (SELECT AGENCY FROM PARAMS)
	AND UPPER(B.B1_ALT_ID) = (SELECT capid FROM PARAMS)
)
SELECT * FROM GEORGE

Oracle to MSSQL: SQLines Data unexpectedly transfers SEQUENCES, and fails on large values

While migrating Oracle to MSSQL I noticed that SQLines Data (v3.1.337) also transfers any SEQUENCES. While I suppose this may or may not be useful in any particular scenario, this came as a surprise to me, as the user interface only ever mentions it will transfer TABLES, and SEQUENCEs are clearly not tables. My table definitions don't depend on the existence of these sequences, so I don't really understand why they were transferred. At minimum the user interface should make clear(er) that it will transfer these object too.

On top of that, any sequences that have a current value (LAST_NUMBER in Oracle) larger than the max value of INT will fail to transfer because the SQL statements that create them do not specify a datatype, so they get created as INT.

Failing SQL was:
CREATE SEQUENCE [MyTable] START WITH 1000000000000000000000042112 INCREMENT BY 1;

How can I get the head files and libs in linux which deps by sql-server, sybase, db2, postgresql

I tried to find these deps it's hard to get these deps.

I had got mysql and oracle deps.

Building SQLParser

Wasn't able to build SQLParser due to "oracle.o" not being in the directory.

Build was tried on RaspberryPi running GCC with standard build not targeting x86 or x86_64.

31773 version Oracle - Error: Error loading Oracle oci.dll: The specified module could not be found.

I used the oracle client 12.2.0.1

Source database type is unknown

I get the following error trying to connect to a MSSQL:

$ ./sqldata -sd=sql,user/password@SERVERNAME\INSTANCE:1433,table -td=mariadb,root/password@localhost -t=table.*

SQLines Data 3.1.313 x86_64 Linux - Database Migration Tool.
Copyright (c) 2017 SQLines. All Rights Reserved.

Connecting to databases (2 ms)
  SQL Server - Error: Source database type is unknown (Ok, 0 ms)
  MySQL 10.1.26-MariaDB-1 Debian unstable x86_64 debian-linux-gnu system jemalloc YaSSL 2.4.2 (Ok, 2 ms)

Any ideas? I can connect to SERVERNAME\INSTANCE with other tools.