1. john smith
  2. Valentina Studio
  3. Friday, September 27 2024, 11:11 AM
  4.  Subscribe via email
OK, i am making some progress - created a report that pulls from a mysql db and will print from the studio pro application.
i am a little stuck now, adding that to a method in xojo - getting an error or 2

---'SpecRx is the name of the StudioPro project
---'Report1 is the report i have created inside SpecRx
---'pxdemographics is the db in mysql that contains the table(s) i am querying
---'pid is the column name i am using and s is a variable holding and account number ...ie.. WHERE pid=s

"from method in xojo IDE"

var s as string
s=acctnum.text
dim theReport as VReport

theReport = SpecRx.MakeNewReport( "report1", "pxdemographics" )
theReport.SetParameterValue( "pid",s)

-----------
error like this: Expected class Vreport, but got string
-----------

Thanks for taking a look
John
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
The SQL Editor cannot execute queries with report parameters at the moment, but we are considering adding this feature.

For now, it can be tested in the query window.
Attachments (2)
Comment
There are no comments made yet.
  1. one week ago
  2. Valentina Studio
  3. # 1
john smith Accepted Answer
Here is a copy of the actual query i am using in StudioPro to test a query, I am getting an error-

select SpectacleRx.pid,SpectacleRx.RxDate,SpectacleRx.RxType,SpectacleRx.exid,SpectacleRx.PxName,SpectacleRx.RxOD,SpectacleRx.AddOD,SpectacleRx.RxOS,SpectacleRx.AddOS,SpectacleRx.ExpDate from SpectacleRx where SpectacleRx.SpecID IN ($P!(SpecID));

13:54:49 Kernel error: Error( 1064 ) 42000: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '!(SpecID))' at line 1"
Comment
There are no comments made yet.
  1. one week ago
  2. Valentina Studio
  3. # 2
Sergey Pashkov Accepted Answer
In this case, you can mark the placeholder for the IDs as follows:
SELECT * FROM RxList WHERE RxID IN ( $P!(pIDs) ).

In the code, you'll construct a string of IDs separated by commas and pass it as the pIDs parameter.
The executed query will then look like:
SELECT * FROM RxList WHERE RxID IN ( 1, 2, 3 ).
Comment
There are no comments made yet.
  1. one week ago
  2. Valentina Studio
  3. # 3
john smith Accepted Answer
Actually, I did get the built in query to work over the weekend, thanks!

But, haha, next question.

Is there a syntax for a WHERE clause that can adjust to a different number of parameters on the fly?

…. Example

I’d like to be able to print 1 or 2 or 3 separate prescriptions for a patient on the same report.
Within the user application, the user (me) would checkbox which prescriptions to print from a checkbox… the RxID from the database would identify each Rx to print. So let’s say I would return a list of RxID numbers (42,45,…)
I would not know ahead how many I’d need to print. (Til I check them off)

SELECT * from RxList where RxID =? <<——- a list of RxID’s.

Thanx!
Comment
There are no comments made yet.
  1. one week ago
  2. Valentina Studio
  3. # 4
Sergey Pashkov Accepted Answer
Could you please show the query and parameter definition in the Valentina Studio?

Simple query works in my test: using a MySQL data source, an empty query in the code, and a parameter for the WHERE clause.
Comment
There are no comments made yet.
  1. one week ago
  2. Valentina Studio
  3. # 5
Sergey Pashkov Accepted Answer
We'll recheck it. It should work without specifying a query.

The PreviewPage method returns a Picture object, which can be drawn on a canvas.

Typically, I test reports by printing them to PDF files, for example:
report.PrintToDisk(folderItem, EVReportPrintType.kToPDF, 1)

There is an example demonstrating the usage of report methods (using an SQLite data source): Examples/VReport/GeneralOnSQLite/GeneralOnSQLite.xojo_binary_project.
Comment
There are no comments made yet.
  1. one week ago
  2. Valentina Studio
  3. # 6
john smith Accepted Answer
Thank you again! For some reason, I had the impression that the ADK would pull the query from the valentina project.
The report printed blank until I copied the query from the StudioPro App and pasted it into my xojo print method.
IS there a way to Not have to put the query into the Xojo method?

Also, while I am asking, what is the syntax to see an onscreen Preview of the Report rather than actually print it every time I want
to test a change?
theReport = project.MakeNewReport("report1", dsdb,sql )
theReport.SetParameterValue( "pid",s)
theReport.PreviewPage.() '<<------------syntax here? for windows
'instead of 'theReport.PrintToLocalPrinter()
Comment
There are no comments made yet.
  1. one week ago
  2. Valentina Studio
  3. # 7
Sergey Pashkov Accepted Answer
So SpecRx is a full path to the project, right?

We need to create VProject object and open it:


Dim fi As FolderItem = GetFolderItem( SpecRx )
Dim project As VProject = new VProject( fi )
project.Open()
Comment
There are no comments made yet.
  1. one week ago
  2. Valentina Studio
  3. # 8
john smith Accepted Answer
Ok, got the data string, thank you!
I am getting another error or 2...?
suggestions?

thanks, John
Attachments (1)
Comment
There are no comments made yet.
  1. one week ago
  2. Valentina Studio
  3. # 9
Sergey Pashkov Accepted Answer
Hello John,

MakeNewReport requires a data source string, not just a name.

This string can be obtained in Valentina Studio via a context menu action "Show Datasource String".


dim ds_str as string = "mysql://host = 'localhost' port = '3306' dbname = 'sakila' user = 'root' password = 'pw' timeout = '200' ssl = 'false'"

mReport = mProject.MakeNewReport( reportName, ds_str )
Attachments (2)
Comment
There are no comments made yet.
  • Page :
  • 1


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