'Missing right quote' eDatabaseError message
Marcos Rogério Cardoso
Oracle table.
I'll try to explain it in details:
I have a table in Oracle 8i composed of only three columns:
COL1 - number(10), COL2 - varchar2(3600), COL3 - long
In my app, I have to read a text file and insert the data in the table
described above.
The insert command (through a TQuery.ExecSQL) is inside a try...except
and at the except clause (where I capture a eDatabaseError expection) I
save the error message in a log file. After this app is run I take a
look at the log file and I find out that from the +180000 lines
included, about 200 lines couldn't be inserted because of the 'Missing
right quote' exception (I save in the log the INSERT command that should
be executed from the TQuery var and I confirm that all the queries have
all the necessary quotes, none is missing), and only one couldn't be
inserted because of the 'String literal is too long' Oracle error, but
it's not the case.
My question is: is there any limitation about the size of the
TQuery.SQL.Text property? There are some with the size of 500, 600, 700
bytes, but this size is normal AFAIK and some lines which are bigger
than these were inserted without any problem.
Maybe is there any character which can't be inserted? The single quote
(') I changed for the acute (´) to avoid some problems, and the (:) I
doubled so Delphi won't take it as a parameter.
My setup is: Delphi IDE 5, Oracle 8i and Win98.
If some information is missing, please inform me.
TIA,
Marcos Rogério Cardoso
John Pierce
expects a terminating quote. You can double the quote -- '' ( I mean
single quote single quote, not double quote) or use a different
substitute, or fool around with your langdrivers so that the acute
works.
John Pierce
Marcos Rogério Cardoso
database only one has the acute. The rest looks perfectly normal.
I'm still looking for an answer,
Marcos Rogério Cardoso
John Pierce
I assume the "missing right quote" error is from the BDE not Oracle?
Is RequestLive true? Are you using Sql Passthrough mode? What does
SQLMonitor show is being sent to Oracle when one of these errors occurs?
What happens if you try to run just one of the rejected ones? Is it
absolutely necessary that you do this from within Delphi? This is the
sort of thing that SQL*Loader handles very well.
John Pierce
Marcos Rogério Cardoso
Let's try to answer it:
The 'Missing right quote' message is being returned from the BDE.
The RequestLive property is False (default) because I don't set it when I
create the TQuery var.
The SQL Passthrough mode at the BDE is set to 'SHARED AUTOCOMMIT'.
The BDE version is 5.00 (you didn't ask but I guess this information might
be necessary).
I don't know SQL Monitor.
If I keep on trying running only one of those rejected INSERT statements
through Delphi it keeps returning the same message, but if I try to run it
through SQL*Plus it truncates the statement because it's too long so it
doesn't work there too.
Unfortunately I couldn't find a way to make it work through SQL*Loader,
because the first column of the table is a sequencer, the second column is
extracted from the 26th position to the first ocurrence of the '#' character
in the line of the text file read (it has a variable length), and the third
column is from the first occurrence of the '#' character to the end of the
line (variable length here too).
If you have any more questions, keep asking,
Marcos Rogério Cardoso
John Pierce
Marcos Rogério Cardoso wrote:
> Phew! Lotsa questions, huh? ;^)
> Let's try to answer it:
> The 'Missing right quote' message is being returned from the BDE.
> The RequestLive property is False (default) because I don't set it when I
> create the TQuery var.
We want to prevent the BDE from massaging the Sql statements, so set RequestLive
:= True, which will pass the SQL directly to Oracle. At that point, it will
either be accepted, or we'll get an Oracle error message, which should be more
informative.
>
> The SQL Passthrough mode at the BDE is set to 'SHARED AUTOCOMMIT'.
> The BDE version is 5.00 (you didn't ask but I guess this information might
> be necessary).
> I don't know SQL Monitor.
Sql Monitor comes with the Enterprise version. How are you connecting to
Oracle? If it's via ODBC, then we may hit a brick wall on this one.
>
> If I keep on trying running only one of those rejected INSERT statements
> through Delphi it keeps returning the same message, but if I try to run it
> through SQL*Plus it truncates the statement because it's too long so it
> doesn't work there too.
Try in SQL worksheet. There's no length limit there.
>
> Unfortunately I couldn't find a way to make it work through SQL*Loader,
> because the first column of the table is a sequencer, the second column is
> extracted from the 26th position to the first ocurrence of the '#' character
> in the line of the text file read (it has a variable length), and the third
> column is from the first occurrence of the '#' character to the end of the
> line (variable length here too).
I couldn't follow that, but I'll take your word for it. You may end up having
to massage the file bit so that you can use Sql*LOader. (See remark on ODBC
above).
>
>
> If you have any more questions, keep asking,
> Marcos Rogério Cardoso
That's about it. See what happens.
John Pierce
Robert Cerny
If the statement is too long and gets truncated, it's corrupted and indeed
missing right quote.
insert into table1 (Field1,Field2)
values ('Something','something veeeeery long, let''s say 100k text');
truncated version will look like:
insert into table1 (Field1,Field2)
values ('Something','something veeeeery long, let''s
See the missing right quote?
Another problem could be an embedded quote char:
insert into table1 (Field1,Field2)
values ('Something','something not so long, let's say 1k text');
Quoted text is 'something not so long, let'.
--
----------------------
Regards
Robert Cerny
Remove both qwe when replying
email: robert.q...@neosys.xrs.qwe.si
No questions via email, unless explicitly invited.
Marcos Rogério Cardoso wrote in message <3975A963...@furb.rct-sc.br>...
>Phew! Lotsa questions, huh? ;^)
>Let's try to answer it:
>The 'Missing right quote' message is being returned from the BDE.
>The RequestLive property is False (default) because I don't set it when I
>create the TQuery var.
>The SQL Passthrough mode at the BDE is set to 'SHARED AUTOCOMMIT'.
>The BDE version is 5.00 (you didn't ask but I guess this information might
>be necessary).
>I don't know SQL Monitor.
>If I keep on trying running only one of those rejected INSERT statements
>through Delphi it keeps returning the same message, but if I try to run it
>through SQL*Plus it truncates the statement because it's too long so it
>doesn't work there too.
>Unfortunately I couldn't find a way to make it work through SQL*Loader,
>because the first column of the table is a sequencer, the second column is
>extracted from the 26th position to the first ocurrence of the '#'
character
>in the line of the text file read (it has a variable length), and the third
>column is from the first occurrence of the '#' character to the end of the
>line (variable length here too).
>
>
Marcos Rogério Cardoso
inserted only the one with the string literal too long couldn't be inserted of
course, but that's another issue that I have to handle myself.
I used the SQL Monitor to watch and looked that everything was doing fine... I used
the SQL Monitor before already, sorry for not having noticed what you were talking
about...
I used the SQL worksheet too and it worked fine there
Thank you very much for your help, John, it really helped me a lot,
Marcos Rogério Cardoso
Al Willis
It looks like you have already solved your problem, but here is another
solution. Try setting the ParamCheck property on the TQuery to False. I
had the same problem with MS SQL 7. I picked this up from a post back in
October 1999. It worked for me.
Al
"Marcos Rogério Cardoso" <mcar...@furb.rct-sc.br> wrote in message
news:3975F8C3...@furb.rct-sc.br...