temporary tables in entrance

[Joe D]

I am having a problem when I try to run my scripts using the Entrance
command line utility. When I type,

java -jar entrance.jar -url jdbc:mysql://15300-temp:3306/
stats_development -u root -p ***** -o C:\joey_work\dendritics
\MOE2_1\out.jpg C:\joey_work\dendritics\MOE2_1\moe2_1_1_report.sql

it has no trouble finding the correct DB. But it returns this error,

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table
'stats_development.pdet' doesn't exist

This table does exist, but it is defined as a temporary table. When I
run this script in Entrance it works without a problem. Do temporary
tables not work in the command line utility? Should I be looking for
some other problem?
Thanks.

[tod Landis]

MySQL temporary tables go away when you close the connection that
creates them. So you can't create a temporary table in one script,
and access it from another. Is the temporary table created by
moe2_1_1_report.sql, or is it created another place?
- Tod

[Joey Defourneaux]

Yes, the temporary table is created in moe_2_1_1_report.sql. I use the
temp table to store data queried from multiple tables before plotting
it. So I can't use a temp table in the script I am running? Or I can't
use them in other scripts? Thanks for your help.

Joey Defourneaux
1-256-529-8574

[tod Landis]

That should work (its actually a good technique to use). I took a
quick look at the code (EntrancePlot.process()) and can't see the
problem. Can you post the script here? We'll open a bug and get it
sorted out...
- Tod

[tod Landis]

Ok, think we found the problem, and we won't need the script.
Temporary tables won't work from the command line for now, but
we will fix this...
- Tod

[Joey Defourneaux]

Ok great. Thanks for looking into it.

[Shane Duan]

Sorry. The scripts that I have tried work. I think we would need
your script after all.

Can you send it to me? Thanks
Shane

--
Shane
http://www.shaneduan.com

[Joey Defourneaux]

Yeah, I can send it tomorrow.

Joey Defourneaux

[Joey Defourneaux]

Ok, here is the script I was using. Its very basic and probably not the simplest way to do this, lol, but I'm an engineer not a programmer. Sorry for all the comment, I just copied it all in at once.

-- This script generates a report of MOE 2_1_1 also called pDet for each system stored in the selected database
-- It seperates pDet into "All problem Types" "slowers" and "fasters"
-- A chart is created to match each data set

-- This function creates a temporary table to store data that is going to be used in the following plot command
-- Manuipulated data to be plotted must be stored before it can be queried by the plot statement,
-- i.e. you cannot plot a SUM query or any query involving changing the queried data (such as pDet)
CREATE TEMPORARY TABLE if not exists pDet_allproblems(
machine varchar(255),
detections real(255,0),
chances real(255,0),
pdet real(255,2) );

-- This queries the desired data to be plotted and stores it in the previously created temporary table
-- All data stored in temp tables are only available for that session and will be cleared after the script is finished running
-- Insert queries are allowed to have manipulation opperands in them, such as SUM or division
INSERT INTO pDet_allproblems (machine, detections, chances, pdet)
select
    r.name, SUM(valid_detect), SUM(valid_detect_chance),
    (SUM(valid_detect)/SUM(valid_detect_chance))*100
FROM detection_stats de
    inner join detections d on d.id = de.detection_id
    inner join machines r on r.id = d.machine_id
    inner join problem_types problem on problem.id = d.problem_type_id
group by r.name;

-- See above comments for examples of the following statements
-- This table's data is for slowers only
CREATE TEMPORARY TABLE if not exists pDet_slowers(
machine varchar(255),
detections real(255,0),
chances real(255,0),
pdet real(255,2) );
-- See above comments for examples of the following statements
INSERT INTO pDet_slowers (machine, detections, chances, pdet)
select
    r.name, SUM(valid_detect), SUM(valid_detect_chance),
    (SUM(valid_detect)/SUM(valid_detect_chance))*100
FROM detection_stats de
    inner join detections d on d.id = de.detection_id
    inner join machines r on r.id = d.machine_id
    inner join problem_types problem on problem.id = d.problem_type_id
where problem.name like '%slower%'
    or problem.name like '%other%'
group by r.name;

-- See above comments for examples of the following statements
-- This table's data is for fasters only
CREATE TEMPORARY TABLE if not exists pDet_fasters(
machine varchar(255),
detections real(255,0),
chances real(255,0),
pdet real(255,2) );

-- See above comments for examples of the following statements
INSERT INTO pDet_fasters (machine, detections, chances, pdet)
select
    r.name, SUM(valid_detect), SUM(valid_detect_chance),
    (SUM(valid_detect)/SUM(valid_detect_chance))*100
FROM detection_stats de
    inner join detections d on d.id = de.detection_id
    inner join machines r on r.id = d.machine_id
    inner join problem_types problem on problem.id = d.problem_type_id
where problem.name like '%faster%'
group by r.name;

-- The following function generates a short text report for the queried data
-- Title lines appear before the data set and Note lines appear after
-- This report is for all problem types
REPORT
    TITLE "pDet Report"
    TITLE "This report shows pDet for each system as determined by Detections/Chances"
    TITLE  " "
    TITLE "All problem Types"

-- This grabs the system names, the sum of all that systems detections, the sum of all of the valid launches,
-- and the percent ratio of detections/launches
-- The results are grouped by each system name
SELECT
    pDet_Allproblems.machine as "System", pDet_Allproblems.detections as "# Detected", pDet_Allproblems.chances as "# of Chances",
    pDet_Allproblems.pdet as "pDet"
FROM pDet_allproblems
group by pDet_Allproblems.machine;

-- See above comments for examples of the following statements
-- This table's data is for slowers only
REPORT
    TITLE "slowers"

-- See above comments for examples of the following statements
SELECT
    pDet_slowers.machine as "System", pDet_slowers.detections as "# Detected", pDet_slowers.chances as "# of Chances",
    pDet_slowers.pdet as "pDet"
FROM pDet_slowers
group by pDet_slowers.machine;

-- See above comments for examples of the following statements
-- This table's data is for fasters only
REPORT
    TITLE "fasters"
    NOTE " "
    NOTE "Here you can add closing notes about what this report means"
    NOTE "or explain the graphs included if there are any."

-- See above comments for examples of the following statements
SELECT
    pDet_fasters.machine as "System", pDet_fasters.detections as "# Detected", pDet_fasters.chances as "# of Chances",
    pDet_fasters.pdet as "pDet"
FROM pDet_fasters
group by pDet_fasters.machine;

-- This is the plot statement that defines how the chart will look.
-- The word following the word PLOT defines the chart type (here it is a barchart)
-- The next line defines what is being selected to plot. These variables match the variables being queried
-- i.e. pDet_Allproblems.machine will be the X Axis Labels, pDet_Allproblems.pdet will be the bar values graphed...
-- The color override command is a logic statement that will change the color of the plotted value based on a certain criteria
-- This example shows that the bar will be changed from BLUE to RED if the pdet value is below 95%
PLOT barchart
    xlabels, light skyblue bar, COLOR OVERRIDE
    WITH
        TITLE "pDet for All problems"
        TITLE X "Systems"
        TITLE Y "Percentage"
        SCALE Y 50 100 10
        HORIZONTAL GRIDLINES
        FRAME to fit
        BARWIDTH .5
        page 0 0 600 400
SELECT
    pDet_Allproblems.machine, pDet_Allproblems.pdet, IF( pDet_Allproblems.pdet < 95, 'RED', '')
FROM pDet_allproblems
group by pDet_Allproblems.machine;

-- See above comments for examples of the following statements
PLOT barchart
    xlabels, light skyblue bar, COLOR OVERRIDE
    WITH
        TITLE "pDet for slowers"
        TITLE X "Systems"
        TITLE Y "Percentage"
        SCALE Y 50 100 10
        HORIZONTAL GRIDLINES
        FRAME to fit
        BARWIDTH .5
        page 0 0 600 400
SELECT
    pDet_slowers.machine, pDet_slowers.pdet, IF( pDet_slowers.pdet < 95, 'RED', '')
FROM pDet_slowers
group by pDet_slowers.machine;

-- See above comments for examples of the following statements
PLOT barchart
    xlabels, light skyblue bar, COLOR OVERRIDE
    WITH
        TITLE "pDet for fasters"
        TITLE X "Systems"
        TITLE Y "Percentage"
        SCALE Y 50 100 10
        HORIZONTAL GRIDLINES
        FRAME to fit
        BARWIDTH .5
        page 0 0 600 400
SELECT
    pDet_fasters.machine, pDet_fasters.pdet, IF( pDet_fasters.pdet < 95, 'RED', '')
FROM pDet_fasters
group by pDet_fasters.machine;

Thanks for looking over this.
Joey Defourneaux

[Shane Duan]

Thanks Joey. We will let you know as soon as we can

[Tod Landis]

Please try converting the names of the termporary tables
to all lower case.

If that doesn't work, I have some questions:

What is the error message you get when you run the script.
(Still the original one:
"com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table
'stats_development.pdet' doesn't exist"?)

Do you see that error message when you run the script,
or later when you do data exploration?

What version numbers do you see in the "Settings"
panel of the "About Entrance..." dialog for server
and driver? (eg. 5.0.45 for server)

That's a very interesting application you've got going there...
- Tod

Tod Landis
dbEntrance Software
PO Box 2383
Boulder Creek CA 95006
831.338.6967
t...@dbentrance.com
http://dbentrance.com/

[Joey Defourneaux]

My Entrance version is 1.3.23, my server is MySQL 5.1.31, and my driver is MySQL-AB JDBC Driver mysql-connector-java-5.1.5
Changing the temp tables to all lower case still gives me the same error. I did a print screen of the error readout. Please excuse the white spaces, its the only way I am allowed to send it.

[Shane Duan]

Hi,

I still cannot reproduce the error. I did create a script based on
yours and it ran fine. Can you check it out and try it on your
database?

I also noticed some discrepancies in your email

* In the snapshot, it is looking for table pdet_allthreats, and in
your original email it is looking for 'stats_development.pdet'. Are
you running the same script?
* I cannot find either table in the script file that you have pasted
so I don't see how running that script can those errors appear

Thanks.
Shane

--
Shane
http://www.shaneduan.com

[Shane Duan]

Hi, Tod has helped me figure out the pattern. Somehow the comment at
the beginning of the SQL messes up the parser when in commend line
mode. I am debugging it now, in the meanwhile, you can make do by
removing the comment at the beginning of the file.

Sorry for the inconvenience.

Shane

--
Shane
http://www.shaneduan.com

[tod Landis]

Hi Joey,
We have a build with a candidate fix for the
problem.

It would be a good idea to keep a backup of your
old entrance.jar + lib directories somewhere, just
in case.

Your settings will be transfered from the old system
to the new one (and back again, if you restore the
old system) because they are saved in ~/Library/Entrance
on Macs and ~/.entrance on other systems.

Here are the downloads:

Trial/IDE License:
*nix: http://dbentrance.com/test/entrance.xip
Windows: http://dbentrance.com/test/entrance.xip
Mac http://dbentrance.com/test/entrance_1_5.dmg
Mac with JRE 1.6 upgrade:
http://dbentrance.com/test/entrance_1_6.dmg

GPL License:
Community jar: Mac http://dbentrance.com/test/entranceCommunity.zip

Can you please try it and let us know if it fixes
the problem?
- Tod

[Joey Defourneaux]

stripping out the comments worked fine. I will try the new build today and let you know if it worked as well. I did have another question, now that it is working. The script I am running generates three graphs, but when I run it in the command line utility it only saves out the last graph generated. Is there a way to save out all three? Or do I need to rewrite the script to only generate one graph per script?

[Tod Landis]

That's great news!  

The command line and servler versions are limited to one PLOT

producing one PNG or JPG file per script.  (The idea is to make each script

in the server version linkable using an IMG tag embedded in HTML.  This approach

also makes it fairly easy to switch from dynamically generated charts to periodically

refreshed static images as traffic increases)

You can also generate HTML pages from the destktop containing 

mutliple images, but that isn't "automatable" yet..  

- Tod