0

I am trying to save data to Heroku Postgres which is also using for heroku connect with Salesforce.

I have an error that says 

 Error:  [PDOException] SQLSTATE[42883]: Undefined function: 7 
 ERROR:  function   get_xmlbinary() does not exist
 LINE 1: SELECT (get_xmlbinary() = 'base64')

 HINT:   No function matches the given name and argument types. 
         You might need to add explicit type casts.

 QUERY:  SELECT (get_xmlbinary() = 'base64')
 CONTEXT: PL/pgSQL function hc_contact_status() line 3 at 
          IF Request URL: /signup

and it's stack trace is 

 #0 app/vendor/cakephp/cakephp/src/Database/Statement/StatementDecorator.php(169): PDOStatement->execute(NULL)
 #1 app/vendor/cakephp/cakephp/src/Database/Connection.php(274): Cake\Database\Statement\StatementDecorator->execute()
 #2 app/vendor/cakephp/cakephp/src/Database/Query.php(176): Cake\Database\Connection->run(Object(Cake\ORM\Query))
 #3 app/vendor/cakephp/cakephp/src/ORM/Table.php(1516): Cake\Database\Query->execute()
 #4 app/vendor/cakephp/cakephp/src/ORM/Table.php(1436): Cake\ORM\Table->_insert(Object(App\Model\Entity\Contact), Array)
 #5 app/vendor/cakephp/cakephp/src/ORM/Table.php(1367): Cake\ORM\Table->_processSave(Object(App\Model\Entity\Contact), Object(ArrayObject))
 #6 app/vendor/cakephp/cakephp/src/Database/Connection.php(561): Cake\ORM\Table->Cake\ORM\{closure}(Object(Cake\Database\Connection))
 #7 app/vendor/cakephp/cakephp/src/ORM/Table.php(1368): Cake\Database\Connection->transactional(Object(Closure))
 #8 app/src/Controller/ContactController.php(126): Cake\ORM\Table->save(Object(App\Model\Entity\Contact))
 #9 [internal function]: App\Controller\ContactController->signup('signup')
 #10 app/vendor/cakephp/cakephp/src/Controller/Controller.php(412): call_user_func_array(Array, Array)
 #11 /app/vendor/cakephp/cakephp/src/Routing/Dispatcher.php(114): Cake\Controller\Controller->invokeAction()
 #12 app/vendor/cakephp/cakephp/src/Routing/Dispatcher.php(87): Cake\Routing\Dispatcher->_invoke(Object(App\Controller\ContactController))
 #13 app/webroot/index.php(37): Cake\Routing\Dispatcher->dispatch(Object(Cake\Network\Request), Object(Cake\Network\Response))
 #14 {main}

Database block in my app.php is

'Datasources' => [
    'default' => [
        'className'  => 'Cake\Database\Connection',
        'driver'     => 'Cake\Database\Driver\Postgres',
        'persistent' => false,
        'host'       => '[ host name for the heroku postgre ]',
        'port'       => '5432',
        'username'   => '[ user name for the heroku postgre ]',
        'password'   => '[ password for the heroku postgre ]',
        'database'   => '[ database for the heroku postgre ]',
        'encoding'   => 'utf8',
        'timezone'   => 'UTC',
        'schema'     => 'salesforce',             
        'cacheMetadata' => false,
        'log'        => false,
        'quoteIdentifiers' => false,
    ],

In this setting, I can retrieve data from Heroku connect but when I try to write something in DB, it will return the error I showed above.

I found an Q and A made for Lavera user and I think I have the same situation. https://discussion.heroku.com/t/heroku-connect-with-laravel/1335

I think Schema => 'salseforce' need to be 'public' but I don't know how to change the setting in CakePHP environment.

I have also run 

# set search_path=salesforce, public;

If anybody knows a solution for the CakePHP, Please let me know. I appreciate your help

Update:

I got respond from Heroku support and they gave me a good idea to solve this question.

Following are heroku's respond and tip

Why this happened ? 

    this error is usually a result of an application incorrectly setting
 search_path and ignoring the public schema. Running set search_path   
 salesforce,public in psql only sets it for the current session.

How to possibly resolve this question ? 

   Datasources:default:schema => 'public' rather than salesforce and then
use fully qualified table names elsewhere in your application.

or

    you could set schema => 'salesforce,public'

Note that he wasn't professional for CakePHP environment so He doesn't sure if second method works or not.

but I think I can do the first method.

If you have more idea of it or something to share on this topic please comment on below

Ryutaro Matsuda
  • 61
  • 3
  • 7

1 Answers1

0

[Additional Update & Solution]

I just tried what the heroku support said. I edited a constructor in a Model like following (in my case, I did contact model )

public function initialize(array $config)
{
    parent::initialize($config);
    // added "salesforce" for following table name 
    $this->table('salesforce.account');
    $this->displayField('name');
    $this->primaryKey('id');

}

and In the app.php file 

'schema'     => 'public',

Schema points to public.

This worked!

Ryutaro Matsuda
  • 61
  • 3
  • 7