ORA-00933 SQL command not specifically terminated. Zeos or SQLdb + Oracle

[babycode]

Good afternoon sirs,

I have a Data Module with ZConnection and a ZQuery with SQL:

Code: Pascal  [Select][+][-]

1. select * from COMPANY.clients;

When I activate the query at design or run time I receive the error: "ORA-00933 SQL command not specifically terminated."

I ran the query creating the objects at run time and the problem does not occur:

Code: Pascal  [Select][+][-]

1. procedure TfrmPrincipal.Button1Click(Sender: TObject);
2. var
3.   Connection: TZConnection;
4.   Query: TZQuery;
5. begin
6.   Connection := TZConnection.Create(nil);
7.   try
8.     Connection.Protocol := 'oracle';
9.     Connection.HostName := 'LOCALHOST';
10.     Connection.Database := 'DATABASE_COMPANY_A';
11.     Connection.User := 'USER';
12.     Connection.Password := 'masterpass';
13.     Connection.LibraryLocation:='oci.dll';
14.     Connection.Connected := True;
15.  
16.     Query := TZQuery.Create(nil);
17.     try
18.       Query.Connection := Connection;
19.       Query.SQL.Text := 'SELECT * FROM COMPANY.clients WHERE CODCLI = 123456';
20.       Query.Open;
21.  
22.       // Processar os dados
23.       while not Query.EOF do
24.       begin
25.         ShowMessage(Query.FieldByName('NAME').AsString);
26.         Query.Next;
27.       end;
28.     finally
29.       Query.Free;
30.     end;
31.  
32.   finally
33.     Connection.Free;
34.   end;
35. end;  

The problem occurs with both Zeos and SQLdb

[dseligo]

Try without semicolon at the end. In your 2nd example (which works) you don't have semicolon.

[babycode]

Hello, thanks for responding. I tried without the semicolon.

[dseligo]

And what if you (in runtime) try:

Code: Pascal  [Select][+][-]

1. Query.SQL.Text := 'SELECT * FROM COMPANY.clients WHERE CODCLI = 123456';
2. Query.Open;

Also, try to make one component in runtime, and other one at design time (i.e. create TZConnection at runtime, and put your TZQuery in Datamodule).

Then at runtime (without creating TZQuery):

Code: Pascal  [Select][+][-]

1.       Query.Connection := Connection;
2.       Query.SQL.Text := 'SELECT * FROM COMPANY.clients WHERE CODCLI = 123456';
3.       Query.Open;

If it works when you all create at runtime, I bet the error is somewhere in your code or component setup.

Did you set Connected of TZConnection to false before running application?

[Zvoni]

Errr...... isn't the delimiter to separate schema/namespace from table a colon in Oracle?

[babycode]

It worked without semicolons. I entered the schema before the table name.

[jcmontherock]

In mySQL you should write:

Code: Pascal  [Select][+][-]

1. Query.SQL.Text := 'SELECT * FROM COMPANY.clients WHERE CODCLI = 123456;';

';' is the default script delimiter.

[Zvoni]

In mySQL you should write:

Code: Pascal  [Select][+][-]

1. Query.SQL.Text := 'SELECT * FROM COMPANY.clients WHERE CODCLI = 123456;';

';' is the default script delimiter.

What's got MySQL to do with the Problem?

There is a saying in Germany: If you can read, you have an advantage.....

[jcmontherock]

SQL is a normalized language... and Mysql is by Oracle edited !

[Zvoni]

SQL is a normalized language...

Yes... for the core part of SQL. The moment you go into the extended part, that statement becomes useless

and Mysql is by Oracle edited !

Try to execute the following in MySQL

Code: SQL  [Select][+][-]

1. SELECT * FROM SomeTable FETCH FIRST 10 ROWS ONLY
2.  

That SQL will execute in Oracle but not in MySQL

Just because Oracle bought MySQL, doesn't mean a Solution for MySQL will work in Oracle and vice versa

[jcmontherock]

Yes, but you can try it...