This one has stumped me.  

My DBISAM > EDB migration process generates a script including a list
of INSERT statements like:

 EXECUTE IMMEDIATE 'INSERT INTO "Divers" VALUES (32, ''Fulcher'',
......

These work fine .. until I get a name like O'Hora, or O'Neil, etc,

The statement:
 EXECUTE IMMEDIATE 'INSERT INTO "Divers" VALUES (34, ''O'Hora'', ...

throws an error as expected .. but I have yet to stumble upon the
magic number of <single quote>s to get this working.
I also tried 'slashing' and concatenations .. but no joy so far.

So can a single quote be escaped in this case .. or must I use some
other character or even just ignore them?
I don't suppose there is a way to specify different script delimiters
per script?

--

Malcolm,

<< My DBISAM > EDB migration process generates a script including a list of
INSERT statements like: >>

Just have the migration script be generated so that it wraps any CHAR,
VARCHAR, or CLOB columns with the QUOTEDSTR() function.  Don't look for it
in the docs, however, because it is supposed to be saved for 2.03.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> Malcolm,
>
> << My DBISAM > EDB migration process generates a script including a
> list of INSERT statements like: >>
>
> Just have the migration script be generated so that it wraps any
> CHAR, VARCHAR, or CLOB columns with the QUOTEDSTR() function.
> Don't look for it in the docs, however, because it is supposed to
> be saved for 2.03.

Yes, I did know about and have used QUOTEDST() but how does ..

Doh! .. do you mean?

 EXECUTE IMMEDIATE 'INSERT INTO "Divers" VALUES (34, ' +
QuotedStr('O''Hora') + ', ....

Seems to work provided I use exactly those quotes!

Ta!
--

Malcolm wrote:

>
>   EXECUTE IMMEDIATE 'INSERT INTO "Divers" VALUES (34, ' +
> QuotedStr('O''Hora') + ', ....
>
> Seems to work provided I use exactly those quotes!
>

Um, no it does not .. or if it did ... not any more.  Grrr.
Help!

--

Malcolm

> >   EXECUTE IMMEDIATE 'INSERT INTO "Divers" VALUES (34, ' +
> > QuotedStr('O''Hora') + ', ....
> >
> > Seems to work provided I use exactly those quotes!
> >
>
> Um, no it does not .. or if it did ... not any more.  Grrr.
> Help!

A simple case like the following runs and inserts O'Hara into the target
column.

execute immediate 'insert into test1 (col2)
values('+quotedstr('O''Hara')+')';

When you say it doesn't work do you mean you get an error?

Could you post the complete statement which is not working.

John

John Hay wrote:

> Malcolm
>
> > >   EXECUTE IMMEDIATE 'INSERT INTO "Divers" VALUES (34, ' +
> > > QuotedStr('O''Hora') + ', ....
> > >
> > > Seems to work provided I use exactly those quotes!
> > >
> >
> > Um, no it does not .. or if it did ... not any more.  Grrr.
> > Help!
>
> A simple case like the following runs and inserts O'Hara into the
> target column.
>
> execute immediate 'insert into test1 (col2)
> values('+quotedstr('O''Hara')+')';
>
> When you say it doesn't work do you mean you get an error?
>
> Could you post the complete statement which is not working.
>
> John

Hi John

I can get it to prepare OK in the manager (UNICODE in case it matters)
But when run it reports: "The execution of the script did not
complete and was stopped at 0 seconds".

Here is a script to build the actual table as well as the INSERT
statement that fails.  You will note that the AsciiName is using #96
instead of #39 .. just to avoid any confusion while debugging. (I
hope I don't get into trouble for posting it here instead of the
binaries).

SCRIPT
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE "Divers"
                     (
                     "DRef"  INTEGER,
                     "Name"  VARCHAR(30) COLLATE "UNI_CI",
                     "FirstName"  VARCHAR(30) COLLATE "UNI_CI",
                     "Representing"  VARCHAR(30) COLLATE "UNI",
                     "Sex"  VARCHAR(1) COLLATE "UNI",
                     "Born"  INTEGER,
                     "CoachName"  VARCHAR(30) COLLATE "UNI",
                     "TeamCode"  VARCHAR(4) COLLATE "UNI",
                     "Registration"  VARCHAR(20) COLLATE "UNI",
                     "AsciiName"  VARCHAR(30) COLLATE "UNI_CI",
                     "AsciiFirst"  VARCHAR(30) COLLATE "UNI_CI"
                     )
                     VERSION 1
                     UNENCRYPTED
                     INDEX PAGE SIZE 8192
                     BLOB BLOCK SIZE 1024
                     PUBLISH BLOCK SIZE 1024
                     PUBLISH COMPRESSION 0
                     MAX ROW BUFFER SIZE 32768
                     MAX INDEX BUFFER SIZE 65536
                     MAX BLOB BUFFER SIZE 32768
                     MAX PUBLISH BUFFER SIZE 32768';
  EXECUTE IMMEDIATE 'CREATE INDEX "SURNAME" ON "Divers"
                     ("Name" COLLATE "UNI_CI", "FirstName" COLLATE
"UNI_CI")';
  EXECUTE IMMEDIATE 'CREATE INDEX "FIRSTNAME" ON "Divers"
                     ("FirstName" COLLATE "UNI_CI", "Name" COLLATE
"UNI_CI")';
  EXECUTE IMMEDIATE 'ALTER TABLE "Divers"
                     ADD CONSTRAINT "PrimaryKey" PRIMARY KEY
("DRef")';

  EXECUTE IMMEDIATE 'INSERT INTO "Divers" VALUES (34, ' +
quotedstr('O''Hora') + ', ''Olivia'', ''Dublin DC'', ''F'', 1995,
''Ricardo Gutierrez'', ''DUB'', '''', ''O`Hora'', ''Olivia'')';

END

Malcolm,

>    EXECUTE IMMEDIATE 'INSERT INTO "Divers" VALUES (34, ' +
> quotedstr('O''Hora') + ', ''Olivia'', ''Dublin DC'', ''F'', 1995,
> ''Ricardo Gutierrez'', ''DUB'', '''', ''O`Hora'', ''Olivia'')';      <----------------

You used O'Hora twice, once with quotedstr, once without. Maybe that's
that problem.

Uli

Uli Becker wrote:

> Malcolm,
>
> >   EXECUTE IMMEDIATE 'INSERT INTO "Divers" VALUES (34, ' +
> > quotedstr('O''Hora') + ', ''Olivia'', ''Dublin DC'', ''F'', 1995,
> > ''Ricardo Gutierrez'', ''DUB'', '''', ''O`Hora'', ''Olivia'')';
> > <----------------
>
> You used O'Hora twice, once with quotedstr, once without. Maybe
> that's that problem.
>
> Uli

No, Uli, I do need the name twice.  In the second one I have used `
(#96) instead of ' (#39) until I discover the correct syntax.  If I
use #96 in both columns and forget the quotedstr it works fine.  The
problem is how to escape the ' (#39).  I just can't get quotedstr to
work, or find out how many #39 are needed.

I should be able to manually add the required number of #39 .. but
then the script either fails to 'prepare' or it fails to match the
data to the columns - or so it seems to me.

Anyway got to go out now - back in 7 or 8 hours.

--

Malcolm

I tried your script on Unicode and it  worked fine.  I have 2.02 Build 14
installed.

I extracted the line which did the insert, changed the 34 to 35, ran that
and it also worked fine.

Have you tried creating a test database from scratch and running the script
as you sent it to me?


John

Malcolm,

> No, Uli, I do need the name twice.  In the second one I have used `
> (#96) instead of ' (#39) until I discover the correct syntax.  If I
> use #96 in both columns and forget the quotedstr it works fine.  The

Sorry, I didn't read your code carefully enough.

Uli