Fetch data from sqlite database

Sarangi Tech Solution

May 03, 2017 00:51

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 .


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;
$failcount=$row[c];
   }

while($row = $result45->fetchArray(SQLITE3_ASSOC)  ){
    echo "MOST COUNT failures :" . $row . "  and count =" . $row[c] . "  ";
$failemail=$row;
$failcount=$row[c];
   }
       }
      
  
  }
  $db->close();

is there any change in this code ? how can i remove the errors? Best Regards, Sarangi Tech Solutions

Comments

12 comments

cPanelMichael

May 03, 2017 15:07
Hello, Here's a quote from the where represents the time in a Unix timestamp. The system retains a maximum of three broken databases so that server owners can attempt to fix the corrupted databases.
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.
0
Sarangi Tech Solution

May 04, 2017 07:53
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
0
cPanelMichael

May 04, 2017 16:02
may i know where is the location of this specific DB with cPanel & WHM 64.0

Hello, Yes, it's located at: /var/cpanel/eximstats_db.sqlite3 Thank you.
0
Sarangi Tech Solution

May 08, 2017 12:32
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 ?
0
cPanelMichael

May 08, 2017 17:06
do this code need any modification ?

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.
0
Sarangi Tech Solution

May 15, 2017 11:37
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
0
cPanelMichael

May 29, 2017 16:54
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.
0
Konservin

June 15, 2017 04:47
Try this solution, worked for me: stackoverflow.com/questions/44499844/cant-access-eximstats-sqlite3-db-after-whm64-upgrade/44517849#44517849
0
Sarangi Tech Solution

June 21, 2017 12:35
Try this solution, worked for me: stackoverflow.com/questions/44499844/cant-access-eximstats-sqlite3-db-after-whm64-upgrade/44517849#44517849

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
0
Sarangi Tech Solution

June 21, 2017 12:40
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.

we appreciate your assistance . and also we would like to notice you with our reply with Konservin......
0
Konservin

June 26, 2017 05:44
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

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.
0
Konservin

June 30, 2017 13:59
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.

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.
0

Please sign in to leave a comment.

Comments

Didn't find what you were looking for?