Error using query builder

Daniel Amamoo-Otchere commented on Mar 3, 2018

Hello I am trying out the querybuilder but having challenges with the code

__ $this->src('pacedsn')->query(DB::table('v_applicant')->toPostgreSQL() )__

Error

Fatal error: Uncaught Exception: Query Error >> [ERROR: syntax error at or near "`" LINE 1: SELECT * FROM `v_applicant` ^] >> SELECT * FROM v_applicant in C:\xampp\htdocs\koolreport\koolreport\datasources\PdoDataSource.php:137 Stack trace: #0 C:\xampp\htdocs\koolreport\koolreport\KoolReport.php(235): koolreport\datasources\PdoDataSource->start() #1 C:\xampp\htdocs\nakdef.paceware.reports\index.php(5): koolreport\KoolReport->run() #2 {main} thrown in C:\xampp\htdocs\koolreport\koolreport\datasources\PdoDataSource.php on line 137

Please advice!

KoolReport commented on Mar 5, 2018

We will investigate the issue. For now, could you please help us to open the koolreport/packages/querybuilder/PostgreSQL.php and replace with following content:

<?php

namespace koolreport\querybuilder;

class PostgreSQL extends SQL
{
    protected $indentifierCover=array("","");
}

... then try again if it works.

Daniel Amamoo-Otchere commented on Mar 5, 2018

Hello, I get the following error

Fatal error: Uncaught Exception: Query Error >> [ERROR: syntax error at or near "utf8" LINE 1: set names utf8 ^] >> SELECT * FROM v_applicant in C:\xampp\htdocs\koolreport\koolreport\datasources\PdoDataSource.php:137 Stack trace: #0 C:\xampp\htdocs\koolreport\koolreport\KoolReport.php(235): koolreport\datasources\PdoDataSource->start() #1 C:\xampp\htdocs\XXX.reports\index.php(5): koolreport\KoolReport->run() #2 {main} thrown in C:\xampp\htdocs\koolreport\koolreport\datasources\PdoDataSource.php on line 137

KoolReport commented on Mar 6, 2018

Hi,

We have checked and found the issue, please help me to modify:

Go to line 45 of koolreport\datasources\PdoDataSource.php, you will find this line:

$this->connection->exec("set names $charset");

change it to:

$this->connection->exec("set names '$charset'");

Sorry for this incidence and thank you very much for letting us know this issue.

The next version of KoolReport will have this fix.

Again thank you, Daniel!

Daniel Amamoo-Otchere commented on Mar 6, 2018

Many thanks for the quick feedback. 1, Can you advice if the view is still relevant in the case of using the query builder?. 2. Can you apply the footer with the query builder?

KoolReport commented on Mar 6, 2018

Do you mean the “CREATE VIEW” statement or sth else. QueryBuilder can get data from created view in database.

KoolReport commented on Mar 7, 2018

I read again your questions. Do you mean you want to know whether QueryBuilder has any effect on the report view. The answer is no. By default, we use normal SQL statement to query data. QueryBuilder just help to generate that SQL. The output of Querybuilder is SQL Query. So basically it does not change any report view.

Daniel Amamoo-Otchere commented on Mar 7, 2018

Hello, No I mean the php file with with. The koolreport implementation uses 3 files, the Index, setup, and the view. Do you need to use the xxx.view.php file in this case.

I also wanted to know how do you implement a grand total for an aggregate field in this case.

KoolReport commented on Mar 7, 2018

All are the same, you still need 3 files. The QueryBuilder only replace your original SQL.

About the grand total or each field. Do you mean how to make footer in the Table to show with sum or other aggregated methods?

Daniel Amamoo-Otchere commented on Mar 7, 2018

Hello, We identified something else. When the Querybuilder executes it shows ok but it has as a header $this->dataStore('XXXX') showing, How can that be replaced with a proper description.

KoolReport commented on Mar 7, 2018

You are running in debug() mode, it will just show all the available dataStore() It seems to me that you have not had the view. Please add {reportName}.view.php file.

Daniel Amamoo-Otchere commented on Mar 7, 2018

Hello, In regards to your question below, Yes that is precisely what was required. To create a footer with aggregates. About the grand total or each field. Do you mean how to make footer in the Table to show with sum or other aggregated methods?

KoolReport's Forum

Error using query builder #224

Build Your Excellent Data Report