Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Fetch data from sqlite database

Discussion in 'Database Discussions' started by Sarangi Tech Solution, May 3, 2017.

Tags:
  1. Sarangi Tech Solution

    Joined:
    May 2, 2017
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    india
    cPanel Access Level:
    Reseller Owner
    hi,
    Our eximstats_db has been converted to sqlite database after our WHM upgrade, and new version is 64.0.19. We are using eximstas_db for cron job purpose in our server and we are new with SQLite . we have tried many more times to access this database using php codes . But no result is found . While run the code a new db creating in the same folder it self . How can i solve this problem ? . And attaching the code that used .

    Code:
    class MyDB extends SQLite3
    {
    function __construct()
    {
    $this->open('/var/cpanel/eximstats_db.sqlite3');
    }
    }
    $db = new MyDB();
    if(!$db){
    echo $db->lastErrorMsg();
    } else {
    echo "Opened database successfully\n";
    
    
    
    
    date_default_timezone_set('Asia/Kuwait');
     $time= date("Y-m-d H:i:s");
           $date = new DateTime(date("Y-m-d H:i:s"));
            $date->modify('-600 seconds');
          $intervel=$date->format('Y-m-d H:i:s');
          $query1 = '(SELECT  mailtime FROM  failures WHERE ip!="127.0.0.1" AND mailtime BETWEEN "$intervel" AND "$time" ) UNION ALL (SELECT  mailtime FROM  defers WHERE ip!="127.0.0.1" AND mailtime BETWEEN "$intervel" AND "$time")';
      
        
          $result = $db->query($query1);
        
           $count=count($result);
    
     echo $count;
    
         if($count>20){
          
             $qry="(SELECT email, COUNT( * ) c FROM failures WHERE ip!='127.0.0.1' AND mailtime BETWEEN  '$intervel' AND '$time' GROUP BY email ORDER BY c DESC LIMIT 1)";
             $qry2="(SELECT email, COUNT( * ) c FROM defers WHERE ip!='127.0.0.1' AND mailtime BETWEEN  '$intervel' AND '$time' GROUP BY email ORDER BY c DESC LIMIT 1)";
      
        $result21 = $db->query($qry);
      $result45 = $db->query($qry2);
          
    
    echo $intervel." to" .$time." count is= ".$count.'   ';
    
    
    while($row = $result21->fetchArray(SQLITE3_ASSOC) ){
        echo "MOST COUNT failures :" . $row . "  and count =" . $row[c] . "  ";
    $failemail=$row[email];
    $failcount=$row[c];
       }
    
    while($row = $result45->fetchArray(SQLITE3_ASSOC)  ){
        echo "MOST COUNT failures :" . $row[email] . "  and count =" . $row[c] . "  ";
    $failemail=$row[email];
    $failcount=$row[c];
       }
           }
          
      
      }
      $db->close();
    
    
    is there any change in this code ? how can i remove the errors?


    Best Regards,


    Sarangi Tech Solutions
     
    #1 Sarangi Tech Solution, May 3, 2017
    Last edited by a moderator: May 3, 2017
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    38,658
    Likes Received:
    1,427
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

    Here's a quote from the 64 Release Notes regarding eximstats using SQLite:

    You can find documentation on SQLite syntax for your custom application at:

    Query Language Understood by SQLite

    Note that if you need assistance with custom SQLite coding, you may find more user-feedback on a website such as StackOverflow.

    Thank you.
     
    eva2000 likes this.
  3. Sarangi Tech Solution

    Joined:
    May 2, 2017
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    india
    cPanel Access Level:
    Reseller Owner
    how can i get the path of SQlite3 database after WHM Upgrade for using with pdos

    may i know where is the location of this specific DB with cPanel & WHM 64.0
     
    #3 Sarangi Tech Solution, May 4, 2017
    Last edited by a moderator: May 4, 2017
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    38,658
    Likes Received:
    1,427
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

    Yes, it's located at:

    /var/cpanel/eximstats_db.sqlite3

    Thank you.
     
    eva2000 likes this.
  5. Sarangi Tech Solution

    Joined:
    May 2, 2017
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    india
    cPanel Access Level:
    Reseller Owner
    function __construct()
    {
    $this->open('/var/cpanel/eximstats_db.sqlite3');
    }
    }
    $db = new MyDB();
    if(!$db){
    echo $db->lastErrorMsg();
    } else {
    echo "Opened database successfully\n";

    do this code need any modification ?
     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    38,658
    Likes Received:
    1,427
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    That looks to be correct, however you will find more feedback on custom coding examples on websites such as StackOverflow, or through the assistance of a qualified system administrator. Assistance with custom development isn't something we can provide on the cPanel Forums.

    Thank you.
     
  7. Sarangi Tech Solution

    Joined:
    May 2, 2017
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    india
    cPanel Access Level:
    Reseller Owner
    thanks Mr.cPanelMichael i have followed on stack overflow for a solution. they suggested to use PDOs .... but While using PDO Still it shows error even if PDO Driver is installed
     
  8. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    38,658
    Likes Received:
    1,427
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

    Feel free to open a support ticket using the link in my signature if you'd like us to take a closer look at the affected system. You can post the ticket number here so we can update this thread with the outcome.

    Thank you.
     
  9. Konservin

    Konservin Registered

    Joined:
    Jun 14, 2017
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Tallinn, Estonia
    cPanel Access Level:
    Website Owner
    Try this solution, worked for me:
    stackoverflow.com/questions/44499844/cant-access-eximstats-sqlite3-db-after-whm64-upgrade/44517849#44517849
     
    #9 Konservin, Jun 14, 2017
    Last edited by a moderator: Jun 15, 2017
  10. Sarangi Tech Solution

    Joined:
    May 2, 2017
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    india
    cPanel Access Level:
    Reseller Owner
    thanks Konservin i have seen thet post and tried with the content . but still also raising the exception '
    Unable to open database: unable to open database file
    ' even if the root user have read write permission
     
  11. Sarangi Tech Solution

    Joined:
    May 2, 2017
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    india
    cPanel Access Level:
    Reseller Owner
    we appreciate your assistance . and also we would like to notice you with our reply with Konservin......
     
  12. Konservin

    Konservin Registered

    Joined:
    Jun 14, 2017
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Tallinn, Estonia
    cPanel Access Level:
    Website Owner
    I think that's exactly the problem: root permissions are irrelevant because you're connecting not as root. Try granting rw to everybody and see if that works.
     
  13. Konservin

    Konservin Registered

    Joined:
    Jun 14, 2017
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Tallinn, Estonia
    cPanel Access Level:
    Website Owner
    Answer at stackoverflow.com/questions/44499844/cant-access-eximstats-sqlite3-db-after-whm64-upgrade/44517849#44517849 is now updated to offer another approach in case you cannot modify privileges for these files.

    Briefly: copy the files (with a cron job) to somewhere you can modify the privileges, and use these files instead.
     
Loading...

Share This Page