[Jesse Clark]

Hi,

I have been experiencing strange behavior when running SQL scripts through the DBExplorer. I have a script that contains all my table creates for my schema and I have been running it in MyEclipse DBExplorer by selecting the “Connected to…” option for my database from the dropdown and then clicking the run icon.

Everything works great except for when I have a SQL standard one line comment “– comment” on a line before a _certain_ create table command.

Script excerpt:

<snip>

drop table license cascade;

<snip>

-- TODO create fks for license 
create table license ( 
   license_id BIGSERIAL,
   some_id_1 bigint not null,
   <snip>
   constraint license_pk primary key (license_id) 
); 

alter table license 
   add constraint license_fk1 foreign key (
      some_id_1)
    references seal (
      some_id_1);
      
<snip>

Now when I run this script the create table and the alter table commands are not getting executed and on subsequent runs the drop table will pop up an alert with a table doesn’t exist error. If I remove the comment the two commands execute fine, if I change the comment to a C style block comment they execute correctly.

If I leave the comment but remove the alter table command, not only does the create table for license not execute but the three create table commands following it get skipped, but the forth and subsequent create table commands after the create license get executed. I’ve even tried changing the text of the comment to just ‘–‘ and it still causes commands to get skipped.

I have tried running the script through the psql command line and get no errors with the SQL standard style comment in the script.

For the time being we will be using C style block comments which don’t seem to cause the error but I thought I would let you guys know. My setup info is in my sig.

Thanks,
-j

[Riyad Kalla]

Jesse, thank you for the info I’m going to send to our DB Explorer dev and see what he has to say.

[Jesse Clark]

I just realized I forgot to attach my sig with my setup info in it.

[support-jeff]

One of the problems here is that the tool, as designed was not really meant to execute full SQL scripts. It just so happens that most of the time, it seems to work ok. However, a known issue is line-termination. The SQL editor as it was originally written only recognizes semi-colon line-termination. So in your script:

— TODO create fks for license
create table license (

in reality gets treated as:

— TODO create fks for license create table license (

which ofcourse is not a create table call, it’s just a comment! There is an issue already for tracking this. Not sure if/when it will be addressed. BTW, the C-style comments work just fine because, ofcourse, they can be inlined in SQL.

[Author]

Posts