http://www.perlmonks.org?node_id=961935


in reply to Dancer + SQL::Translator

Thanks for the reminder about SQL::Translator. I added a call to "dance;" at the end to run it from the command line directly, changed the DSN etc to point at our Oracle db and I changed those "MySQL"s to "Oracle". Unfortunately, although the SQL is generated the png generation failed for me immediately with xxx:

ERROR (line 9): Invalid statement: Was expecting remark, or run, or pr
+ompt, or create, or table comment, or comment on table, or comment on
+ column, or alter, or drop

[download]

and leaving MySQL in with:

ERROR (line 789): Invalid constraint: Was expecting primary key def, o
+r unique key def, or foreign key def

[download]

The generated SQL in both cases looks ok as far as I can tell but the line number reported seems to bear no relationship to the SQL at that line number. :-(

Obviously the same happens with sqlt-graph.

BTW, I thought GraphViz was deprecated in favour of Graphviz2.

Replies are listed 'Best First'.
Re^2: Dancer + SQL::Translator
by jeffa (Bishop) on Mar 27, 2012 at 15:31 UTC

    I saw that error creep up many times when i was trying to put this together. Looks like more will be necessary than simply replacing the name of the database vendor/driver. Take a look at the value for $sql and see what part(s) are invalid for Oracle. As for GraphViz2, normally i would jump up to upgrade but in this case i just need something quick and dirty to help me visualize my problem that needs to be solved. Thanks for your time. :)

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]
[select]

      It is the double bracketed values in the timestamp definition.

      CREATE TABLE "ACCOUNT" (
  "ACCOUNT_ID" number(38) NOT NULL,
  "DATE_TIME_UTC" timestamp(6)(11),
  "CURRENCY_ID" number(38) NOT NULL,
  "NAME" varchar2(128) NOT NULL,
  "BALANCE" number(14) NOT NULL,
  "ACCOUNT_TYPE_ID" number(38) NOT NULL,
  "CREATED_BY_USER" varchar2(128),
  "CREATED_DATE_TIME_UTC" timestamp(6)(11),
  "MODIFIED_BY_USER" varchar2(128),
  "MODIFIED_DATE_TIME_UTC" timestamp(6)(11)
);

      [download]

      When the table was originally created those columns were only timestamps with no precision.

[reply]
[d/l]

        I spoke to one of the maintainers of SQL::Translator and after a suggestion from him I arrived at the following which avoids the problem above:

        my $translator = SQL::Translator->new(
    from        => 'DBI',
    to          => 'GraphViz',
    parser_args => {
        dsn         => "dbi:Oracle:xxx",
        db_user     => 'xxx',
        db_password => 'xxx',
        producer_args => {
            output_type   => 'png',
            out_file => 'out.png',
            natural_join  => 1,
            width => 30.0,
            height => 30.0,
            #fontsize => 10
        },
    },
   );
my $sql = $translator->translate( data => '');

        [download]

        unfortunately our schema is quite large and the above produces a png which although looks like it might be correct is unreadable - everything is too small. The png output is limited in size apparently. I tried generating a postscript file as well but I don't seem to be able to produce one of those - nothing is output. Maybe I don't have whatever GraphViz needs to generate postscript.

[reply]
[d/l]

In Section Cool Uses for Perl