Syntax error 201 when importing to Informix

Ondřej Žižka 0
Viewed 39

I exported a database from Informix 11.50 and want to import it to Informix 14.10.

dbexport -ss -c -q iscala

The exported SQL script starts with the following:

{ DATABASE iscala  delimiter | }

EXECUTE PROCEDURE ifx_allow_newline ('t');

grant dba to "informix";
grant dba to "waspop";
grant connect to "jzl";


create distinct type 'informix'.vestnik as      decimal(4,0);

grant usage on type 'informix'.vestnik to 'public' as 'informix';

drop cast (decimal(4,0) as vestnik);
create explicit cast (decimal(4,0) as vestnik with 'informix'.date_vestnik);

CREATE FUNCTION "informix".date_vestnik(vestnik DATE) RETURNING vestnik WITH(NOT VARIANT);
RETURN ((CASE WHEN MONTH(vestnik)=12 AND DAY(vestnik)>12 THEN DAY(vestnik) ELSE MONTH(vestnik) END)
        *100+MOD(YEAR(vestnik),100))::NUMERIC(4,0)::vestnik;
END FUNCTION;


create explicit cast (vestnik as integer with 'informix'.vestnik_int);

CREATE FUNCTION "informix".vestnik_int(vestnik vestnik) RETURNING INT WITH(NOT VARIANT);
RETURN vestnik::NUMERIC(4,0)::INT;
END FUNCTION;


create explicit cast (integer as vestnik with 'informix'.int_vestnik);

CREATE FUNCTION "informix".int_vestnik(vestnik INT) RETURNING vestnik WITH(NOT VARIANT);
RETURN vestnik::NUMERIC(4,0)::vestnik;
END FUNCTION;

create explicit cast (vestnik as decimal(4,0) with 'informix'.vestnik_date);

CREATE FUNCTION "informix".vestnik_date(vestnik vestnik) RETURNING DATE WITH(NOT VARIANT);
DEFINE y, m, d INT;
LET y = MOD(vestnik::NUMERIC(4,0),100);
LET m = TRUNC(vestnik::NUMERIC(4,0)/100);
LET d = 1;
IF m<1 THEN RETURN NULL;
ELIF m>20 THEN RETURN NULL;
ELIF m>12 THEN LET m,d = 12,m;
END IF
IF y<0 THEN RETURN NULL;
ELIF y<50 THEN LET y = 2000+y;
ELSE LET y = 1900+y;
END IF
RETURN MDY(m,d,y);
END FUNCTION;


{ TABLE "informix".pbcattbl row size = 369 number of columns = 25 index size = 31 }
{ unload file name = pbcat00100.unl number of rows = 0 }
...
...

When I run the import with this SQL script I receive the following error

[informix@srvbib ~]$ dbimport -c -d datadbs -i /home/informix/ iscala
{ DATABASE iscala  delimiter | }

grant dba to "informix";
grant dba to "waspop";
grant connect to "jzl";






create distinct type 'informix'.vestnik as      decimal(4,0);

grant usage on type 'informix'.vestnik to 'public' as 'informix';



drop cast (decimal(4,0) as vestnik);
create explicit cast (decimal(4,0) as vestnik with 'informix'.date_vestnik);

CREATE FUNCTION "informix".date_vestnik(vestnik DATE) RETURNING vestnik WITH(NOT VARIANT);
RETURN ((CASE WHEN MONTH(vestnik)=12 AND DAY(vestnik)>12 THEN DAY(vestnik) ELSE MONTH(vestnik) END)
        *100+MOD(YEAR(vestnik),100))::NUMERIC(4,0)::vestnik;
END FUNCTION;


create explicit cast (vestnik as integer with 'informix'.vestnik_int);

CREATE FUNCTION "informix".vestnik_int(vestnik vestnik) RETURNING INT WITH(NOT VARIANT);
RETURN vestnik::NUMERIC(4,0)::INT;
END FUNCTION;


create explicit cast (integer as vestnik with 'informix'.int_vestnik);

CREATE FUNCTION "informix".int_vestnik(vestnik INT) RETURNING vestnik WITH(NOT VARIANT);
RETURN vestnik::NUMERIC(4,0)::vestnik;
END FUNCTION;


create explicit cast (vestnik as decimal(4,0) with 'informix'.vestnik_date);
*** prepare sqlobj
201 - A syntax error has occurred.

*** execute sqlobj
201 - A syntax error has occurred.

When I remove the procedure it works OK. If I try to create the function using the dbaccess, I can create it without issues.

Do you please know or could you please provide some advice or hint on how to solve this issue? Thank you.

export informix database-migration import database
1 Answers

Converting comments into an answer.

This looks like a bug. You will need to contact your Informix technical support channel. I've created bug CQ idsdb00111253. You can use that in conversations with your support team.

Analysis

The error message is not very helpful — the statement that is failing to be prepared is a DROP CAST statement that DB-Import creates after analyzing the CREATE CAST. The text of the generated statement is missing a close parenthesis because it isn't expecting DECIMAL(4, 0) — it thinks that the close parenthesis in that type name means it doesn't need to add one whereas, in fact, it does need to add one. Some lazy parsing is going to have to become less lazy.

There are two variants of the CREATE CAST statement according to the syntax diagrams:

CREATE [{ IMPLICIT | EXPLICIT }] CAST (<type1> AS <type2>)
CREATE [{ IMPLICIT | EXPLICIT }] CAST (<type1> AS <type2> WITH <function>)

The problem occurs because your <type2> is DECIMAL(4,0), and the code spots the ) parenthesis and assumes (mistakenly) that the ) is from the first variant rather than the second:

create explicit cast (vestnik as decimal(4,0) with 'informix'.vestnik_date);

Thank you for your investigation. Regarding your observation, I would expect that if I remove the drop case, the error will disappear, but the error is still there even if the drop cast has been removed. Could you please share with me how you debug this behaviour?

For reasons that are not completely clear to me, DB-Import takes the CREATE EXPLICIT CAST statement and manufactures a DROP CAST statement from it — but makes a mistake when doing so. At some point, I will check whether this is a new defect, but my gut feel is that it will be present in older versions too.

You could debug this by setting the SQLIDEBUG environment variable when you run DB-Import and then using sqliprint to show what messages are sent to and from the server. I can only find references to SQLIDEBUG in the context of JDBC, but it is more general than that.

export SQLIDEBUG=2:${TMPDIR:-/tmp}/sqdbg

When you connect to the server with this set, the client code will create a file such as /tmp/sqdbg_a3715. You can then run sqliprint /tmp/sqdbg_a3715.

Workarounds

As to workarounds, the simplest would be to remove the offending CREATE CAST statement(s) from the import file (iscala.exp/iscala.sql) and complete the import without it. Then use DB-Access to execute the omitted statements. It's undoubtedly a nuisance but should allow you to complete the import.

Jonathan Leffler0
Related