Base/SQL help

Jeff_Sadowski · January 2, 2012, 10:06pm

I can't figure out the best place to get help with SQL to use to get
the information I want.

Situation:

I have a script gathering information about printers daily.

It puts the information directly to the database.

The fields in the database are date,ip,serial,counter
date is a timestamp
ip and serial are character arrays
and counter is a big int

I would like to get the last 2 entries in sql but I can't figure out
how to do it in 1 sql statement.

I created a script that gets the distinct ip's using one sql statement
and sorts them so that all offices entries are together and can be
separated by office.
then runs a sql statement for each ip to get the last two entries.
final output looks like so

<office 1 name>
IP, Serial, Couter Diff
<ip>,<serial>,<counter1-counter2> between <date1> and <date2>
...
<office 1 name> Subtotal <office 1 subtotal>
<office 2 name>
IP, Serial, Couter Diff
<ip>,<serial>,<counter1-counter2> between <date1> and <date2>
...
<office 2 name> Subtotal <office 2 subtotal>
...

I'd like to be able to do this in a report in LibreOffice under Base
but can't figure out how to use base to do what my script does.

I never had anything like this situation in any of my classes on
databases so I am lost on how to begin.
All I remember reports on where things that could be gotten with a
single sql statement.

planas · January 3, 2012, 2:39am

What is the database backend? You can use Base as a front end to many database engines.

Mark_Stanton · January 3, 2012, 8:58am

The SQL is something like

    SELECT TOP 2 * FROM <yourtable>
    ORDER BY counter, DESC
    WHERE ip = <selected ip>

depending, as Jay says, on your SQL engine.

Mark Stanton
One small step for mankind...

Villeroy · January 3, 2012, 10:10am

Any resource on plain SQL can give you the solution. Base can not help you
by any means to compose any non-trivial query. Once you have a working query
against your database you build your report based on that query.
Most likely the query will include the GROUP BY statement and 2 instances of
your table to get the difference value.

Tom_Davies · January 3, 2012, 1:02pm

Hi
There is some documentation on this page
http://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Base_Guide
I am not sure if it gets into Sql statements or even as far as Queries yet but it might be worth having a quick glance.
Regards from
Tom

Jeff_Sadowski · January 3, 2012, 3:58pm

Any resource on plain SQL can give you the solution. Base can not help you
by any means to compose any non-trivial query. Once you have a working query
against your database you build your report based on that query.
Most likely the query will include the GROUP BY statement and 2 instances of
your table to get the difference value.

Thank you I was hoping there might be a way in base to help me out
with my query.

since I only take one record per day I can get a good proximity of my
report data with something like so

SELECT `date`, `modifiedprintusage`.* FROM `modifiedprintusage` WHERE
`date` > NOW( ) - 48 * 3600 ORDER BY `ip` ASC

maybe group by where ip like 192.168.[this octet].[not this octet]
Maybe sql just can't do it.

Issues I am having that are BASE related the above SQL works ok in
BASE with 2 exceptions in design view.
1.) it complains about the (-48*3600) I have to remove this and re add
it in sql view.
2.) it puts an `ip`.`modifiedprintusage` after `modifiedprintusage`.*
and will not let me hide the order by field; again I have to edit it
in sql view to remove it.

BASE also has an issue displaying all the fields even though I gave it
* it skips the first field which in this case is date. In mysql the
above sql show the date twice.

also it always puts the table name back in on all the fields this is
ok put ugly looking.

I'm using the odbc connector for mysql.
I found it impossible working with tables that don't have primary keys
in base. You can't create an entry form that works. I found it
impossible though base to add the primary key to an existing table.
Since I only have one entry a day for my printers I used date and
serial together as a primary key I wonder if that has anything to do
with it not printing the date if I only use * in my select statement.
But like I said in mysql itself it shows it with just *.

Mysql is guilty of the other flaws like time not being in seconds.
I have to try and find what it is using I get no records with the
above sql I needed to multiply the subtracted by 60 or so to get
results but I'll figure that out latter.

Villeroy · January 3, 2012, 4:37pm

All the wizards and graphical tools are more or less broken. What the query
designer can do properly can be done by SQL learners after the very first
lessons had been learned. Anything slightly advanced has to be done in plain
SQL.

MySQL has a datediff function:
http://www.w3schools.com/sql/func_datediff_mysql.asp

In Base syntax (parsed mode, should also work in graphical view):
SELECT "date" AS "Date","ip"||' - '||"serial" AS "Printer", SUM("counter")AS
"Sum"
FROM "modifiedprintusage"
WHERE DATEDIFF('dd', CURRENT_DATE, "date")<3
GROUP BY "date","ip","serial"

In MySQL syntax (direct SQL mode)
In Base syntax:
SELECT `date` AS `Date`,`ip`||' - '||`serial` AS `Printer`, SUM(`counter`)AS
`Sum`
FROM `modifiedprintusage`
WHERE DATEDIFF( CURDATE(), `date`)<3
GROUP BY `date`,`ip`,`serial`

It is supposed to return this 3-column table:
Date | Printer | Sum
2012-01-02 | 192.168.023 - Epson Blurb | 232
2012-01-02 | 192.168.024 - HP Spitfire | 1023
...

Tom_Davies · January 3, 2012, 5:13pm

Hi
MariaDb is a drop-in replacement for MySql and is developing much faster. It might not help with the current problems but it might be worth thinking about moving to it sometimes fairly soon.
http://mariadb.org/
http://en.wikipedia.org/wiki/MariaDB
Presumably it just reads the same data so it shouldn't involve any actual changes and could run alongside MySql so that you could test it without committing to it. I haven't tried it but hopefully they make it easy to migrate!

Anyway, all that is a side-issue.
Regards from
Tom

Villeroy · January 3, 2012, 5:37pm

Tom wrote

Hi
MariaDb is a drop-in replacement for MySql and is developing much faster.

Could some list admin ban this spam bot, please?

drew · January 3, 2012, 6:15pm

@Tom - though I'm not ready to agree to something so strong as a ban, I
can't in good conscious pass up agreeing with Andreas in spirit.

Andreas, and some others, gave some very good feedback to the original
question, building exactly the type of knowledge base that can be a big
help to others, not just the requester in this specific case.

The addition of the last post from you does more then just add clutter
to the list. This type of tangential reference to another product not
involved quite literally screws up search results for lots of folks
later down the road - with specificity, you have added just a slew of
posts that do nothing but tag the string MariaDB to email threads. Which
in the end will impact those searching for answers dealing with
legitimate questions about LibreOffice AND MariaDB.

In other words, I know you want to contribute, but in a real and
concrete sense the best way to contribute is to stay on topic.

Sincerely,

Drew Jensen

elderdanlewis · January 3, 2012, 11:56pm

This is all confusing to me. How many tables are in this database
and what are they? What are the fields and to what table to they belong?
What are the field types of each one of the fields? What do you mean by
ip and serial are character arrays? Character arrays are not one of the
field types nor field properties that I know of. An example of character
arrays would be nice too.
     You use the terms office 1 name, office 2 name, office 1 subtotal,
and office 2 subtotal. What are the relationships of these terms to the
fields in the database?
     What I'm trying to do is to create your database to see what could
be done. But I can't do that with what you have written: I need more
specific information.
     You speak of a script. Could you share that with us also?

--Dan

Tom_Davies · January 4, 2012, 10:11am

Hi
If the database is small enough to upload then uploading it to Nabble might
be the easiest. Just click the link to Nabble. Perhaps a copy of the
database with no data in it might help Dan?

Note to the two odd posts prior to Dan's: if people express dissatisfaction
with a 3rd party product it's not unreasonable for us to give them
alternatives as part of the answer. In this case there is an alternative
that is close enough to what Jeff is using and hopefully that would allow
him to migrate with a minimum of disruption. Not everyone is completely
happy with Oracle products and the alternatives might not be widely known.
I don't see the point of not mentioning it when it might well be helpful to
the original poster, Jeff.

Regards from
Tom

iplaw67 · January 4, 2012, 12:22pm

Hi Jeff,

I'm using the odbc connector for mysql.

This has always had its own particular problems, dependent on the
version you are using.

I found it impossible working with tables that don't have primary keys
in base. You can't create an entry form that works.

Yes, you need to have a primary key on your table, and preferably,
include it in your queries, otherwise the results are read only.

I found it

impossible though base to add the primary key to an existing table.

Yes, you would need to do this via the command line interface or another
mysql interfacing tool, although I suspect that this might be a
limitation of the ODBC driver (I haven't checked to compare with JDBC or
the native mysql connector extension).

Since I only have one entry a day for my printers I used date and
serial together as a primary key I wonder if that has anything to do
with it not printing the date if I only use * in my select statement.
But like I said in mysql itself it shows it with just *.

Multiple-field keys as the primary key for a table have always been
slightly problematic with OOo/LO, again dependent on your connector
driver type and version, and even the version of OOo/LO.

Mysql is guilty of the other flaws like time not being in seconds.
I have to try and find what it is using I get no records with the
above sql I needed to multiply the subtracted by 60 or so to get
results but I'll figure that out latter.

You may find that you have to use CAST in your SQL statements to get the
results that you want. I find mysql's handling of dates and times
strange indeed, and it has been my undoing on many an occasion.

Alex

Jeff_Sadowski · January 4, 2012, 5:39pm

This is all confusing to me. How many tables are in this database
and what are they? What are the fields and to what table to they belong?
What are the field types of each one of the fields? What do you mean by
ip and serial are character arrays? Character arrays are not one of the
field types nor field properties that I know of. An example of character
arrays would be nice too.
You use the terms office 1 name, office 2 name, office 1 subtotal,
and office 2 subtotal. What are the relationships of these terms to the
fields in the database?
What I'm trying to do is to create your database to see what could
be done. But I can't do that with what you have written: I need more
specific information.
You speak of a script. Could you share that with us also?

--Dan

You are 100% correct here is a better example of data and what I'd
like as the output form with the given example.

CREATE TABLE `storename` (
`store` int(11) NOT NULL,
`storename` varchar(20) DEFAULT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `storename` VALUES (1,"store 1");
INSERT INTO `storename` VALUES (2,"store 2");

CREATE TABLE `printusage` (
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
  `serial` varchar(20) NOT NULL,
  `ip` varchar(15) DEFAULT NULL,
  `counter` bigint(20) DEFAULT NULL,
  `webcounter` bigint(20) DEFAULT NULL,
  `totalprint` bigint(20) DEFAULT NULL,
  `bwprint` bigint(20) DEFAULT NULL,
  `copies` bigint(20) DEFAULT NULL,
  `bwcopies` bigint(20) DEFAULT NULL,
  `faxes` bigint(20) DEFAULT NULL,
  `store` int(11) DEFAULT NULL,
  PRIMARY KEY (`date`,`serial`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `printusage` VALUES ('2011-12-30
00:00:00','1','192.168.1.50',100,100,100,100,0,0,0,1);
INSERT INTO `printusage` VALUES ('2011-12-30
00:00:00','2','192.168.1.51',100,100,100,100,0,0,0,1);
INSERT INTO `printusage` VALUES ('2011-12-30
00:00:00','3','192.168.1.52',100,100,100,100,0,0,0,1);
INSERT INTO `printusage` VALUES ('2011-12-30
00:00:00','4','192.168.2.50',100,100,100,100,0,0,0,2);
INSERT INTO `printusage` VALUES ('2011-12-30
00:00:00','5','192.168.2.51',100,100,100,100,0,0,0,2);
INSERT INTO `printusage` VALUES ('2011-12-31
00:00:00','1','192.168.1.50',105,105,105,105,0,0,0,1);
INSERT INTO `printusage` VALUES ('2011-12-31
00:00:00','2','192.168.1.51',105,105,105,105,0,0,0,1);
INSERT INTO `printusage` VALUES ('2011-12-31
00:00:00','3','192.168.1.52',105,105,105,105,0,0,0,1);
INSERT INTO `printusage` VALUES ('2011-12-31
00:00:00','4','192.168.2.50',105,105,105,105,0,0,0,2);
INSERT INTO `printusage` VALUES ('2011-12-31
00:00:00','5','192.168.2.51',105,105,105,105,0,0,0,2);

example of desired output report.

store 1
IP, Serial, Counter Difference from 2011-12-30 00:00:00 to 2011-12-31 00:00:00
192.168.1.50, 1, 5
192.168.1.51, 2, 5
192.168.1.52, 3, 5
store 1 subtotal 15
store 2
IP, Serial, Counter Difference from 2011-12-30 00:00:00 to 2011-12-31 00:00:00
192.168.2.50, 4, 5
192.168.2.51, 4, 5
store 2 subtotal 10
total 25

elderdanlewis · January 4, 2012, 8:08pm

This looks to be quite feasible. Reports can be created two ways
in Base: using the Report Wizard, and using Design View (Create Report
using Design View). The latter uses what was originally the Sun Report
Builder.
Here is a couple of links that might be of use when creating
reports in design view:
http://www.google.com/search?client=ubuntu&channel=fs&q=sunreportbuilderguidebook.pdf&ie=utf-8&oe=utf-8
is a link to a PDF describing how to use the report builder.
http://user.services.openoffice.org/en/forum/viewtopic.php?f=47&t=39095
is a link to describe how to create user defined functions. (You may not
need this for you problem, but it may come in handy later.
LibreOffice Base uses HSQLDB. Presently, Base 3.4.4 uses version
1.8. Version 2.2.6 is available as a separate program. The following
link will begin the download of the HSQLDB Guide for version 1.8.
http://hsqldb.org/doc/guide/guide.pdf

--Dan

Jeff_Sadowski · January 5, 2012, 6:26pm

All the wizards and graphical tools are more or less broken. What the query
designer can do properly can be done by SQL learners after the very first
lessons had been learned. Anything slightly advanced has to be done in plain
SQL.

MySQL has a datediff function:
http://www.w3schools.com/sql/func_datediff_mysql.asp

In Base syntax (parsed mode, should also work in graphical view):
SELECT "date" AS "Date","ip"||' - '||"serial" AS "Printer", SUM("counter")AS
"Sum"
FROM "modifiedprintusage"
WHERE DATEDIFF('dd', CURRENT_DATE, "date")<3
GROUP BY "date","ip","serial"

In MySQL syntax (direct SQL mode)
In Base syntax:
SELECT `date` AS `Date`,`ip`||' - '||`serial` AS `Printer`, SUM(`counter`)AS
`Sum`
FROM `modifiedprintusage`
WHERE DATEDIFF( CURDATE(), `date`)<3
GROUP BY `date`,`ip`,`serial`

It is supposed to return this 3-column table:
Date | Printer | Sum
2012-01-02 | 192.168.023 - Epson Blurb | 232
2012-01-02 | 192.168.024 - HP Spitfire | 1023
...

--

Thank you for mentioning datediff
"SELECT * FROM `modifiedprintusage` where
DATEDIFF(CURRENT_DATE,`date`)<3 ORDER BY `ip` DESC;"
works great from the commandline mysql but libreoffice is rejecting it.

iplaw67 · January 5, 2012, 7:00pm

Hi Jeff,

Thank you for mentioning datediff
"SELECT * FROM `modifiedprintusage` where
DATEDIFF(CURRENT_DATE,`date`)<3 ORDER BY `ip` DESC;"
works great from the commandline mysql but libreoffice is rejecting it.

It should work with the SQL parser turned off, but this means that you
can't use the graphical query designer, you can only work in SQL mode.

Alex

Jeff_Sadowski · January 5, 2012, 8:08pm

Hi Jeff,

Thank you for mentioning datediff
"SELECT * FROM `modifiedprintusage` where
DATEDIFF(CURRENT_DATE,`date`)<3 ORDER BY `ip` DESC;"
works great from the commandline mysql but libreoffice is rejecting it.

It should work with the SQL parser turned off, but this means that you
can't use the graphical query designer, you can only work in SQL mode.

How do you turn it off?
I don't see the option.

Jeff_Sadowski · January 5, 2012, 8:23pm

Hi Jeff,

Thank you for mentioning datediff
"SELECT * FROM `modifiedprintusage` where
DATEDIFF(CURRENT_DATE,`date`)<3 ORDER BY `ip` DESC;"
works great from the commandline mysql but libreoffice is rejecting it.

It should work with the SQL parser turned off, but this means that you
can't use the graphical query designer, you can only work in SQL mode.

How do you turn it off?
I don't see the option.

Found it
Edit->"Run SQL command directly"
did the trick.
It would be nice if the design view worked a bit better but I can live
without it.