0

I'm trying to reverse engineer a DB in MySql with a sql file generated from an OpenOffice database. while trying to import and run the sql file, I get the following error:

    ERROR: Line 1: syntax error, unexpected IDENT_QUOTED, expecting EVENT_SYM or FUNCTION_SYM. Statement skipped.

I tried many a things including removing double quotes and adding delimiter to the statements, yet the issue persists. For your reference, please find the excerpts from the .sql file.

CREATE SCHEMA PUBLIC AUTHORIZATION DBA;

CREATE CACHED TABLE Users(UserID NUMERIC(15) NOT NULL PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
MobileNumber VARCHAR(12) NOT NULL,
EmailAddress VARCHAR(50) NOT NULL,
CompanyName VARCHAR(50),
City VARCHAR(50),
StateOrProvince VARCHAR(50),
CountryOrRegion VARCHAR(50),
PostalCode INTEGER,
PassPhraseID NUMERIC(10) NOT NULL,
Gender CHAR(1) NOT NULL,
Age NUMERIC(3) NOT NULL,
MaritalStatus CHAR(10),
Height NUMERIC(4,1),
Weight NUMERIC(3),
Complexion INTEGER,
PreferredMessageMode VARCHAR(10) NOT NULL,
isPaid BOOLEAN NOT NULL,
FBID VARCHAR(50),
BBM VARCHAR(8),
PreferredVerticals VARCHAR_IGNORECASE(100),
EmergencyNos CHAR(60) NOT NULL,
DOB DATE,
PhotoID INTEGER,
CategoryID NUMERIC(2) NOT NULL,
isActive BOOLEAN NOT NULL,
ActivationDate TIMESTAMP(0));

CREATE CACHED TABLE UserLocation(
UserID NUMERIC(15) NOT NULL PRIMARY KEY,
Location NUMERIC(22) NOT NULL,
Timestamp TIMESTAMP(0) NOT NULL,
CONSTRAINT SYS_FK_101 FOREIGN KEY(UserID) REFERENCES Users(UserID));

I have no clue about this. Please help, Thanks in Advance

2 Answers 2

1

There are a few things wrong with the DDL Open Office has generated for you:

  • MySQL doesn't have a VARCHAR_IGNORECASE datatype
  • The CREATE CACHED TABLE statement isn't valid
  • TIMESTAMP(0) should be TIMESTAMP

The following should work:

CREATE TABLE Users(
  UserID NUMERIC(15) NOT NULL PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  MobileNumber VARCHAR(12) NOT NULL,
  EmailAddress VARCHAR(50) NOT NULL,
  CompanyName VARCHAR(50),
  City VARCHAR(50),
  StateOrProvince VARCHAR(50),
  CountryOrRegion VARCHAR(50),
  PostalCode INTEGER,
  PassPhraseID NUMERIC(10) NOT NULL,
  Gender CHAR(1) NOT NULL,
  Age NUMERIC(3) NOT NULL,
  MaritalStatus CHAR(10),
  Height NUMERIC(4,1),
  Weight NUMERIC(3),
  Complexion INTEGER,
  PreferredMessageMode VARCHAR(10) NOT NULL,
  isPaid BOOLEAN NOT NULL,
  FBID VARCHAR(50),
  BBM VARCHAR(8),
  PreferredVerticals VARCHAR(100),
  EmergencyNos CHAR(60) NOT NULL,
  DOB DATE,
  PhotoID INTEGER,
  CategoryID NUMERIC(2) NOT NULL,
  isActive BOOLEAN NOT NULL,
  ActivationDate TIMESTAMP
);

CREATE TABLE UserLocation(
  UserID NUMERIC(15) NOT NULL PRIMARY KEY,
  Location NUMERIC(22) NOT NULL,
  Timestamp TIMESTAMP NOT NULL
);

ALTER TABLE UserLocation 
ADD CONSTRAINT FK_UserLocation 
FOREIGN KEY (UserID) REFERENCES Users(UserID);
1

I managed to solve this problem by changing the setup connection from MySQL Workbench > Advance >SQL_MODE = TRADITIONAL enter image description here

After setting the parameter, then, you can reverse engineer and complete all the subsequent steps using the step-by-step wizard from the reverse engineer menu for your desire database.
Hope this solution solve your problem too.

1
  • Thank you! Worked perfectly! Jul 29, 2023 at 16:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.