loading a database with images

Gary_Dale · October 10, 2016, 6:56pm

I have a database with a large enough number of records that I don't want to do this manually. I would like to have query do it instead. Everything I've found through searches seems to want to do it manually by creating a form.

I have a small number of images (5), one of which will be added to the database based on the content of another field.

What I was thinking was something along the lines of (extra fields omitted)

SELECT "place"
   CASE WHEN "place" = 0 THEN 'image0.png'
          WHEN "place" = 1 THEN 'image1.png'
          WHEN "place" = 2 THEN 'image2.png'
          WHEN "place" = 3 THEN 'image3.png'
          WHEN "place" >= 4 THEN 'image4.png'
   END "ribbon"
FROM "Results"

However I have two issues with that. The first is that the "Ribbon" field probably won't contain the image from the current directory but will instead just contain the text. The other is that Base complains about a SQL syntax error when I try to save it.

Can anyone help me move forward on this?

Gary_Dale · October 10, 2016, 7:11pm

OK, missing comma after the first field explains the syntax error. But as expected, I'm getting the file name rather than the actual file in the query result.

RobertG · October 10, 2016, 7:38pm

Hi Gary,

You have forgotten: ,
SELECT "place", ...

With ' ' will be input text. It isn't an image, which could be shown.
Save the images by a form in a separate table "images".
You will get the image you want, if you start a subselect for it.

SELECT "place",
CASE WHEN "place" = 0 THEN (SELECT "image" FROM "image" WHERE "ID" = 0)
WHEN "place" = 1 THEN (SELECT "image" FROM "image" WHERE "ID" = 1)
WHEN "place" = 2 THEN (SELECT "image" FROM "image" WHERE "ID" = 2)
WHEN "place" = 3 THEN (SELECT "image" FROM "image" WHERE "ID" = 3)
WHEN "place" >= 4 THEN (SELECT "image" FROM "image" WHERE "ID" = 4)
END AS "ribbon"
FROM "Results"

Regards

Robert

RobertG · October 10, 2016, 7:38pm

Hi Gary,

SELECT "place"

You have forgotten: ,
SELECT "place", ...

CASE WHEN "place" = 0 THEN 'image0.png' WHEN "place" = 1 THEN
'image1.png' WHEN "place" = 2 THEN 'image2.png' WHEN "place" = 3
THEN 'image3.png' WHEN "place" >= 4 THEN 'image4.png' END "ribbon"
FROM "Results"

With ' ' will be input text. It isn't an image, which could be shown.
Save the images by a form in a separate table "images".
You will get the image you want, if you start a subselect for it.

SELECT "place",
CASE WHEN "place" = 0 THEN (SELECT "image" FROM "image" WHERE "ID" = 0)
WHEN "place" = 1 THEN (SELECT "image" FROM "image" WHERE "ID" = 1)
WHEN "place" = 2 THEN (SELECT "image" FROM "image" WHERE "ID" = 2)
WHEN "place" = 3 THEN (SELECT "image" FROM "image" WHERE "ID" = 3)
WHEN "place" >= 4 THEN (SELECT "image" FROM "image" WHERE "ID" = 4)
END AS "ribbon"
FROM "Results"

Regards

Robert

Gary_Dale · October 10, 2016, 9:05pm

Having an issue with my form. I was able to dump the ID data into it from a spreadsheet (paste into the table) but the table seems to be read only. When I right-click on the image control, nothing happens. I also have an extra field which provides a text translation of what the image says and I can't modify it either.

I was following a video by TheFrugalComputerGuy.com -

LibreOffice Base (15) Embedding a Picture and things went pretty much as per his video in the very short bit about actually adding the control, but it doesn't let me edit.

There's probably something about Forms that I'm missing....

Gary_Dale · October 10, 2016, 9:29pm

Actually, it looks like any table I add is read only... The original table in the database allows me to add and change records, but new tables that I add don't.

Gary_Dale · October 10, 2016, 9:44pm

Go it. The old primary key problem. Base insists that it create one and call it ID instead of using another name.

RobertG · October 11, 2016, 6:05am

Hi Gary,

Go it. The old primary key problem. Base insists that it create one
and call it ID instead of using another name.

How do you create tables? Every name for a field with primary-key is
allowed. Primary-key has only to be unique.

Your query (by adding the comma) could also work, if the images are in
the same path as the *.odb-file.

In the form you have to connect the imagecontrol to the field, which
shows the text. The imagecontrol will show the iage insted of the path.

Regards

Robert

RobertG · October 11, 2016, 6:05am

Hi Gary,

How do you create tables? Every name for a field with primary-key is
allowed. Primary-key has only to be unique.

Your query (by adding the comma) could also work, if the images are in
the same path as the *.odb-file.

In the form you have to connect the imagecontrol to the field, which
shows the text. The imagecontrol will show the iage insted of the path.

Regards

Robert

Gary_Dale · October 11, 2016, 2:00pm

Yes, found the way to make something a Primary Key.

I got the code to work but am trying to optimize it (for human readability more than speed). I wanted something like this:

SELECT "event_name", "first_name", "middle_initial", "last_name", "sex", "age", "email", "place",
  CASE
    WHEN "age" BETWEEN 0 AND 18 THEN 'Youth'
    WHEN "age" BETWEEN 19 AND 59 THEN 'Adult'
    ELSE 'Senior'
  END "Group",
  CASE
    WHEN "place" <= 3 THEN ( SELECT "finish", "ribbon" FROM "Ribbons" WHERE "ID" = "place" )
    WHEN "place" >= 4 THEN ( SELECT "finish", "ribbon" FROM "Ribbons" WHERE "ID" = "4" )
  END "Finish", "Ribbon"
FROM "Results"

which Base accepts as syntactically valid but generates a "Column not found: 4 in statement [...] when I run the query. Looking at the code in Design View, it has the Ribbon field as a Field instead of as an Alias with the code generating the field.

Is it possible to specify two fields in one CASE statement or do I have to duplicate the code for each field?

RobertG · October 11, 2016, 2:29pm

Hi Gary,

SELECT "event_name", "first_name", "middle_initial", "last_name",
"sex", "age", "email", "place", CASE WHEN "age" BETWEEN 0 AND 18
THEN 'Youth' WHEN "age" BETWEEN 19 AND 59 THEN 'Adult' ELSE
'Senior' END "Group", CASE WHEN "place" <= 3 THEN ( SELECT
"finish", "ribbon" FROM "Ribbons" WHERE "ID" = "place" ) WHEN
"place" >= 4 THEN ( SELECT "finish", "ribbon" FROM "Ribbons" WHERE
"ID" = "4" ) END "Finish", "Ribbon" FROM "Results"

1. A subselect must have only one row with one field.
2. If you want to connect a subselsect with the outer select, you have
to write a correlative select.

SELECT "event_name", "first_name", "middle_initial", "last_name",
"sex", "age", "email", "place",
CASE
    WHEN "age" BETWEEN 0 AND 18 THEN 'Youth'
    WHEN "age" BETWEEN 19 AND 59 THEN 'Adult'
    ELSE 'Senior'
END "Group",
CASE
   WHEN "place" <= 3 THEN ( SELECT "finish" FROM "Ribbons"
      WHERE "ID" = "a"."place" )
   WHEN "place" >= 4 THEN ( SELECT "finish" FROM "Ribbons"
   WHERE "ID" = "4" )
END "Finish",
CASE
   WHEN "place" <= 3 THEN ( SELECT "ribbon" FROM "Ribbons"
      WHERE "ID" = "a"."place" )
   WHEN "place" >= 4 THEN ( SELECT "ribbon" FROM "Ribbons"
      WHERE "ID" = "4" )
END "Ribbon"
FROM "Results" AS "a"

With "a"."place" the subselscts reads the value of "place" from the
current row, which is read out from "Results".

Regards

Robert

RobertG · October 11, 2016, 2:34pm

Hi Gary,

1. A subselect should only have one value (one row, one field).
2. If you will connect to the current row of the outer select, the
table of the outer select must have an alias.

SELECT "event_name", "first_name", "middle_initial", "last_name",
"sex", "age", "email", "place",
  CASE
    WHEN "age" BETWEEN 0 AND 18 THEN 'Youth'
    WHEN "age" BETWEEN 19 AND 59 THEN 'Adult'
    ELSE 'Senior'
  END "Group",
  CASE
    WHEN "place" <= 3 THEN ( SELECT "finish" FROM "Ribbons"
WHERE "ID" = "a"."place" )
    WHEN "place" >= 4 THEN ( SELECT "finish" FROM "Ribbons"
WHERE "ID" = "4" )
  END "Finish",
  CASE
    WHEN "place" <= 3 THEN ( SELECT "ribbon" FROM "Ribbons"
WHERE "ID" = "a"."place" )
    WHEN "place" >= 4 THEN ( SELECT "ribbon" FROM "Ribbons"
WHERE "ID" = "4" )
  END "Ribbon"
FROM "Results" AS "a"

... would work.

Regards

Robert

Gary_Dale · October 11, 2016, 3:13pm

Sounds good in theory but I get the same error. I've also tried it with just sub-selecting for just one field (remove the last CASE statement) and again get the same error.

RobertG · October 11, 2016, 5:38pm

Hi Gary,

would be good if you will send me the database with private mail - so
I could have a look, which error appears.

Regards

Robert