How to use database prefixes and Drupal 9 insert() method

I am developing a custom module for a Drupal 9 website. I am trying to perform an INSERT in a remote PostgreSQL database. So …

The database has multiple schemas: public, contents, office, …
So in sites/default/settings.php I have this:

$databases['remotedb']['default'] = array (
  'database' => 'mydatabase',
  'username' => 'user',
  'password' => 'pass123',
  'prefix' => '',
  'host' => '123.456.78.901',
  'port' => '5432',
  'namespace' => 'DrupalCoreDatabaseDriverpgsql',
  'driver' => 'pgsql',
);
$databases['remotedb']['office'] = array (
  'database' => 'mydatabase',
  'username' => 'user',
  'password' => 'pass123',
  'prefix' => 'office.',
  'host' => '123.456.78.901',
  'port' => '5432',
  'namespace' => 'DrupalCoreDatabaseDriverpgsql',
  'driver' => 'pgsql',
);

I have performed successful SELECT queries, using [‘remotedb’][‘default’], like this:

$db = DrupalCoreDatabaseDatabase::getConnection('default', 'remotedb');
$q = $db->query("SELECT price FROM contents.categories WHERE id=:categoryId", [':categoryId' => $categoryId]);
$res = $q->fetchCol();

Now I’m trying to perform an INSERT, using insert() method, as advised in Drupal Docs. At first I didn’t have [‘remotedb’][‘office’] defined, so I had errors such as "table does not exist".
So after researching I thought I should use prefix and that’s how [‘remotedb’][‘office’] ended up in settings.php.
My INSERT code is now like this:

$db = DrupalCoreDatabaseDatabase::getConnection('office', 'remotedb');
$q = $db->insert('credits')->fields(['amount' => $amount,'type' => $type,]);
$res = $q->execute();

But I get this error on $res = $q->execute(); line:

DrupalCoreDatabaseDatabaseExceptionWrapper: SQLSTATE[42602]: Invalid name: 7 ERROR: 
invalid name syntax LINE 6: AND pg_attribute.attrelid = '."office"."credits"'::re... ^: 
SELECT pg_attribute.attname AS column_name, format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS data_type, 
pg_get_expr(pg_attrdef.adbin, pg_attribute.attrelid) AS column_default 
FROM pg_attribute LEFT JOIN pg_attrdef ON pg_attrdef.adrelid = pg_attribute.attrelid AND pg_attrdef.adnum = pg_attribute.attnum 
WHERE pg_attribute.attnum > 0 AND NOT pg_attribute.attisdropped AND pg_attribute.attrelid = :key::regclass 
AND (format_type(pg_attribute.atttypid, pg_attribute.atttypmod) = 'bytea' OR
pg_get_expr(pg_attrdef.adbin, pg_attribute.attrelid) LIKE 'nextval%'); 
Array ( [:key] => ."office"."credits" ) 
in DrupalmyofficeServiceCreditFunctions->addCredit() (line 91 of modules/custom/myoffice/src/Service/CreditFunctions.php).

It drives me crazy. I tried multiple things and nothing help. I eventually end up w/ same error.
The only way I can perform this INSERT is by using query(), and not insert() method.

I guess my questions would be:

is it possible to perform the INSERT by using the DatabaseAPI insert() method for a prefixed table (in a non-public schema one)? what am I doing wrong? what am I missing?
how should the prefix be used in insert() method?

Source: Symfony Questions

Was this helpful?

0 / 0

#drupal #drupal-9 #php #postgresql #symfony