DataTables and Postgres

menashe

I just completed the arduous task of switching from MySQLQ to PostgresSQL.
I am getting the error "An error occurred while connecting to the database 'postgres'. The error reported by the server was: could not find driver".

What do I need to do?

From config.php:

$sql_details = array(
"type" => "Postgres", // Database type: "Mysql", "Postgres", "Sqlserver", "Sqlite" or "Oracle"
"user" => "postgres", // Database user name
"pass" => "", // Database password
"host" => "localhost", // Database host
"port" => "5432", // Database connection port (can be left empty for default)
"db" => "postgres", // Database name
"dsn" => "", // PHP DSN extra information. Set as charset=utf8mb4 if you are using MySQL
"pdoAttr" => array() // PHP PDO attributes array. See the PHP documentation for all options

kthorngren

I might be wrong since I don't use PHP but this thread shows how to determine if the driver is installed. If its not installed see if this SO thread helps to install it.

Kevin

menashe

Thank you. That was a problem, but I resolved.

I am reading my categories tables successfuly!

Editor::inst($db, 'categories')
    ->field(
        Field::inst('id')->set(false),
        Field::inst('category'),
        Field::inst('sub_category1')
                        ->setFormatter( Format::ifEmpty( null ) ),
        Field::inst('sub_category2')
                        ->getFormatter( Format::ifEmpty( null ) )
    )->where( function ($q) {
        $q->order('category asc');
        $q->order('sub_category1 asc');
        } )
    ->process($_POST)
    ->debug(true)
    ->json();

But the slightly more involved one is returning "error: "JSON encoding error: Type is not supported"

Editor::inst($db, 'items')
  ->field(
      Field::inst('items.id')->set(false),                   
      Field::inst('items.item')
          ->validator( Validate::notEmpty( ValidateOptions::inst()
              ->message( 'An Item name is required'))),
      Field::inst('items.notes'),
      Field::inst('items.category_id')
          ->options(Options::inst()
                  ->table('categories')
                  ->value('id')
                  ->label('category')
          )
          ->validator( Validate::dbValues(null, 'id', 'categories', $db))
          ->validator( Validate::notEmpty( ValidateOptions::inst()
              ->message( 'A Category is required'))),
      Field::inst('categories.category'),
      Field::inst('categories.sub_category1'),
      Field::inst('categories.sub_category2'),
    )
  ->leftJoin('categories', 'categories.id', '=', 'items.category_id')
  ->process($_POST)
  ->debug(true)
  ->json();

menashe

Solved!
Long story short--it looked rather difficult to migrate the blobs from MySQL to bytea in PostgreSQL, so I left them for later.
So the bytea fields were blnak rather than <null>
Fixed that!

allan

Nice one - thanks for the update!

Allan

NickEvans

If you need to connect to PostgreSQL database, you can use this tool - unidac.

allan

@NickEvans - spam, or does that actually relate to this thread other than that it mentions Postgres?

Allan