KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

How to find out which error was returned by the database #2533

Open Johan Maris opened this topic on on Jan 11 - 6 comments

Johan Maris commented on Jan 11

Hi, When something is wrong with a given query and the database returns an error, the framework just crashes saying "Error ; Call to a member function execute() on bool ....." like in het example in the screenshot. I just wonder if there is a way to find out which error was exactly returned by the database. When a query fails, the database normally returns an error message that tells exactly where in the query it went wrong, and it would really help a lot in debugging if this error could be displayed (eventually in a log )

KoolReport commented on Jan 11

May I see your code inside setup() function (including the sql)

Johan Maris commented on Jan 12

Hi, here is the code. I however was hoping that you could give me some ideas on how I could find out myself what's wrong with a query when the prepareAndBind fails to create the $stmt object. Is there maybe something like a validation function that I could call which can indicate where there is an error in the query ?

[](
public function setup()

{
    // get input parameters
    $NbrDays = request()->get('NbrDays');
    $Agentid = request()->get('AgentId');
   
    // build detailled datatable
    $this->src('uhh')->query (
            'SELECT 	count(gtp_trip_id) as NbrTrips,
                            agent_identifier,
                            customer_code,
                            corporation_id,
                            date(created_at)as CDate,
                            source_system 
            FROM '.config('koolreport.uhhdata.database').'.trips 
            where 	date(created_at)> (date(now())-('.intval($NbrDays).'+1))
                            and agm_status = "done"
                            and agent_identifier like "'.$Agentid.'"
            group by agent_identifier,customer_code,corporation_id,gtp_trip_id,date(created_at),agm_status,source_system;'
            )
    ->pipeTree(
            function($node)  // build detailled listing
            {
            $node->pipe($this->dataStore('Details') );   
            },
                    
            function($node)  // build trip count list per day
            {
            $node->pipe(new OnlyColumn(array("agent_identifier","NbrTrips","CDate","source_system")))
            ->pipe(new Group(array(
                        "by"=>array("agent_identifier","CDate","source_system"),
                        "sum"=>array("NbrTrips")
                        )))
            ->pipe($this->dataStore('TripsPerDay'));
            },
           
            );
                       
})
KoolReport commented on Jan 12

In your SQL, you use double quote for string which should be single quote.

select * from mytable where name="Johan"

should be changed to

select * from mytable where name='Johan'
Johan Maris commented on Jan 12

Hi, the mysql database I use supports both ' and " as string delimiter. I just found the error myself tough, there was nothing wrong with the query syntax, but there was a typo in the table name. So the issue itself is fixed, but my original question was not to fix the query, but I wanted to know if there is a way in koolreport to catch the error response that is returned by the database, because if I would have that option I would have seen a long time ago that the table didn't exist...

Sebastian Morales commented on Jan 13

Hi Johan, it's our fault to not catch the error message when preparing a statement from a sql query. If you want the exact sql error message pls open the file koolreport/core/src/datasources/MySQLDataSource.php and replace the following line:

        $stmt = $this->connection->prepare($query);

with these ones:

        mysqli_report(MYSQLI_REPORT_ALL);
        $stmt = $this->connection->prepare($query);
        if($stmt === false)
          die ("Mysql Error: " . $this->connection->error);
Johan Maris commented on Jan 13

That's exactly what I need, thanks a lot !

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
help needed
solved

Laravel