1. Ben Antwi
  2. Valentina Reports ADK
  3. Dienstag, Oktober 18 2022, 04:31 PM
  4.  Abonnieren via E-Mail
How to display an image from database with filename example 12345.png stored as text in sqlite3 database and the actual image is stored in the working directory named "img/" as filepath. I need assistance. Thank you in advance.
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
That's how I would do that:
1. Add the following expression to the query (image is a field with filename):
CONCAT( $P(pImgFolder), image ) as full_path
2. Add parameter pImgFolder and set the default location, e.g.:
C:\\Users\\USERNAME\\Project\\img\\
3. Add Picture control to the report and set its source as "URL from field" and select "full_path"

Later, in your Python code, you construct an absolute path to your "img" directory and set it as a value for pImgFolder parameter.
Anhänge
Kommentar
There are no comments made yet.
Ben Antwi Akzeptierte Antwort
@Sergey Pashkov I'm kind of confused with your reply.

1. I'm working with only just one table which contains all I need from the database namely firstname, surname and the image as shown below in the query.
SELECT firstname,surname,image FROM tempOfficial.

2. So adding this line CONCAT( $P(pImgFolder), image ) as full_path is what is getting me confused.

3. With the Picture control to the report the "URL from field" shows image from options instead of "full_path"

Could you please assist me since this is my first time trying to implement this in python. Thanks in advance.
Anhänge
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
Table contains only the file name, so you have to construct full absolute path in the source query as a separate field.
Then the “full_path” field appears in the list of the Picture control.
Kommentar
There are no comments made yet.
Ben Antwi Akzeptierte Antwort
Meaning the source query should be like below?

SELECT firstname,surname, CONCAT($P(pImgFolder), image) AS full_path FROM temp_table
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
Yes, exactly,

Actually, you can start with a constant
SELECT firstname,surname, CONCAT('C:\\path\\img\\', image) AS full_path FROM temp_table

and then replace it with a parameter.
Kommentar
There are no comments made yet.
Ben Antwi Akzeptierte Antwort
I run the query below in Valentina studio and still image is not showing
SELECT firstname,surname, GROUP_CONCAT('C:\\Users\RAZOR64\\Desktop\\PROJECT\\img\\', '4545Ken Wood') AS full_path FROM temp_table
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
Not clear.
Did you change the source query of the report? No extension in the filename?
Kommentar
There are no comments made yet.
Ben Antwi Akzeptierte Antwort
Yes I did and I have updated the source query as below but still the image is not displaying on report preview. I have attached an image of the report preview.

SELECT firstname,surname,email,phone,address,dob,birth_place, GROUP_CONCAT('C:\\Users\RAZOR64\\Desktop\\PROJECT\\img\\', '4545Ken Wood.png') AS full_path FROM temp_table
Anhänge
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
Let's test just loading a picture.
Please change the picture source to "URL (local or remote)" and select this image - will it load it?
Kommentar
There are no comments made yet.
Ben Antwi Akzeptierte Antwort
Please when I change the picture source to "URL (local or remote)" and select an image it displays the selected image when I preview. Below is the image of output.
Anhänge
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
But there is GROUP_CONCAT. Or it is not a query actually used?
Kommentar
There are no comments made yet.
Ben Antwi Akzeptierte Antwort
The GROUP_CONCAT does not display the image from the image file location. But I'm using an actual query.
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
So CONCAT should be used here. What is the point of GROUP_CONCAT without grouping?
Kommentar
There are no comments made yet.
Ben Antwi Akzeptierte Antwort
Please I tried using CONCAT but it returned an error saying Sqlite db "EmpDatabase.db": Error: "no such function: CONCAT" , that's why i used GROUP_CONCAT
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
Ok, SQLite equivalent is:
'C:\\Users\RAZOR64\\Desktop\\PROJECT\\img\\' || '4545Ken Wood.png' AS full_path
Kommentar
There are no comments made yet.
Ben Antwi Akzeptierte Antwort
@Sergey Pashkov 'C:\\Users\RAZOR64\\Desktop\\PROJECT\\img\\' || '4545Ken Wood.png' AS full_path is now working in the Valentina Studio. Please how to I call it in the python script?
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
Now you can create a parameter pImgFolder (I described it at first), set it by default to 'C:\\Users\RAZOR64\\Desktop\\PROJECT\\img\\'.

In python script, you get the full absolute path to the img folder and pass it to report, e.g.:
rpt.setParameterValue( 'pImgFolder', path )
Kommentar
There are no comments made yet.
Ben Antwi Akzeptierte Antwort
The code below works fine by generating a pdf report without the image. Please where exactly should I put this example? rpt.setParameterValue( 'pImgFolder', 'C:\\Users\RAZOR64\\Desktop\\PROJECT\\img\\' ) as you stated above?


id = '4545'

# Open local project
project = valentina.project.connect('file://C:/Users/RAZOR64/Desktop/PROJECT/tempofficial.vsp')

# Make report instance
report = project.report(name='tempofficial_summary', dsn='sqlite://C:/Users/RAZOR64/Desktop/PROJECT/EmployeeDatabase.db',query='SELECT id,firstname,surname,birth_place,dob FROM temporary_staff WHERE id={}'.format(id ))

# Print result as PDF
report.printToDisk('C://Users/RAZOR64/Desktop/tempReport1234.pdf')

# Cleanup
report.close()
project.close()
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
Parameters can be set between generating and printing.

But in your code snippet, you're redefining the source query, so the parameter is not there anymore:
query='SELECT id,firstname,surname,birth_place,dob FROM temporary_staff WHERE id={}'.format(id ))

This query should return the same set of fields as the source query you create in the Valentina Studio.

So if you go this way, it will look something like that:
query="SELECT id,firstname,surname,birth_place,dob, '{}' || image as full_path FROM temporary_staff WHERE id={}".format('C:/Users/RAZOR64/Desktop/PROJECT/img/',id )

Actually, you can define parameters right here, including an ID
query='SELECT id,firstname,surname,birth_place,dob, $P(pImgPath) || image as full_path FROM temporary_staff WHERE id=$P(pID)'

and set them both afterwards:
report.setParameterValue( 'pImgFolder', 'C:/Users/RAZOR64/Desktop/PROJECT/img/' )
report.setParameterValue( 'pID', id )

So the overall generation and printing:

# Make report instance
report = project.report(name='tempofficial_summary', dsn='sqlite://C:/Users/RAZOR64/Desktop/PROJECT/EmployeeDatabase.db',query='SELECT id,firstname,surname,birth_place,dob, $P(pImgPath) || image as full_path FROM temporary_staff WHERE id=$P(pID)')

report.setParameterValue( 'pImgFolder', 'C:/Users/RAZOR64/Desktop/PROJECT/img/' )
report.setParameterValue( 'pID', id )

# Print result as PDF
report.printToDisk('C:/Users/RAZOR64/Desktop/tempReport1234.pdf')
Kommentar
There are no comments made yet.
Ben Antwi Akzeptierte Antwort
Meaning there are two ways above to achieve this right?
And if that be the case, I have run both and it generates the report without errors but the image is still not displaying.
Could it be that I have not done something right in Valentina studio?
Kommentar
There are no comments made yet.
  • Seite :
  • 1
  • 2
  • 3


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories