Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Bug: Sqldrv problems to insert when change DBPrefix #3881

Closed
savilaj opened this issue Nov 13, 2020 · 9 comments
Closed

Bug: Sqldrv problems to insert when change DBPrefix #3881

savilaj opened this issue Nov 13, 2020 · 9 comments
Labels
bug Verified issues on the current code behavior or pull requests that will fix them

Comments

@savilaj
Copy link

savilaj commented Nov 13, 2020

CRITICAL - 2020-11-13 01:16:51 --> [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'sap.sap.EndPoints'.

duplicates DBPrefix when DBPrefix value is other than dbo.
It also does not take the value of schema

In App\Config\Database.php
public $development = [
'DSN' => '',
'hostname' => 'xxxxxxxxxxxx',
'username' => 'xxxxxxxxxxxx',
'password' => 'xxxxxxxxx',
'database' => 'develop',
'DBDriver' => 'sqlsrv',
'DBPrefix' => 'sap.',
'pConnect' => false,
'DBDebug' => (ENVIRONMENT !== 'production'),
'charset' => 'utf8',
'DBCollat' => 'utf8_general_ci',
'swapPre' => '',
'encrypt' => false,
'compress' => false,
'strictOn' => false,
'failover' => []
];

@savilaj savilaj added the bug Verified issues on the current code behavior or pull requests that will fix them label Nov 13, 2020
@rattones
Copy link

a table in SQL SERVER can not have . (dot) character in the compound of the name like sap.

@savilaj
Copy link
Author

savilaj commented Nov 18, 2020

a table in SQL SERVER can not have . (dot) character in the compound of the name like sap.

... and not have (dot) in the table name:

class EndPointModel extends BaseModel {

  protected $table = 'EndPoints';
  protected $primaryKey = 'code';
  protected $allowedFields = [
      'code',
      'name',
      'baseURL',
      'method',
      'query',
      'verify',
      'debug',
      'http_errors',
      'timeout',
      'username',
      'password'
  ];
  protected $returnType = 'App\Entities\EndPoint';

@rattones
Copy link

'DBPrefix' => 'sap.',

that dot .. DBPrefix is used to ad a prefix to table name, in this case, sap. will generante sap.EndPoints as table name

@hdrodriguez
Copy link

hdrodriguez commented Nov 23, 2020

I currently have that same problem since the company where I work uses different schemes than dbo, which is why I have not been able to migrate from CI3, solution is 'prefix.' ?

@savilaj
Copy link
Author

savilaj commented Nov 23, 2020

I currently have that same problem since the company where I work uses different schemes than dbo, which is why I have not been able to migrate from CI3, solution is 'prefix.' ?

No, it is not the solution. When using a DBprefix other than dbo. a select, an update, works correctly, but when using an insert it fails.

@savilaj
Copy link
Author

savilaj commented Nov 23, 2020

The solution we have adopted to be able to migrate to CI4 has been to change the prefix to dbo. in all tables

@rattones
Copy link

cause this DBPrefix is not the dbo., it's a prefix to all name of tables, like:
DBPrefix = '' -> dbo.address
DBPrefix = 'usu_' -> dbo.usu_address

as you can see in the file system/Database/BaseConnection.php at line: 1347

	/**
	 * DB Prefix
	 *
	 * Prepends a database prefix if one exists in configuration
	 *
	 * @param string $table the table
	 *
	 * @return string
	 * @throws \CodeIgniter\Database\Exceptions\DatabaseException
	 */

@eSkiSo
Copy link
Contributor

eSkiSo commented Nov 24, 2020

This issue is related with the driver _insert funcion (on Builder.php):

protected function _insert(string $table, array $keys, array $unescapedKeys): string
	{
		$fullTableName = $this->getFullName($table);

		// insert statement
		$statement = 'INSERT INTO ' . $fullTableName . ' (' . implode(',', $keys) . ') VALUES (' . implode(', ', $unescapedKeys) . ')';

		return $this->keyPermission ? $this->addIdentity($fullTableName, $statement) : $statement;
	}

The issue is that only on _insert this happens, does not happen on _update or _delete
This getFullName puts the table name in the DATABASE.SCHEMA.TABLE format, but SCHEMA is hardcoded ( with dbo as far as i can see in the code Connection.php file)

So the quick fix woud be to replace $fullTableName with $table in the $statement inside _insert function.

@michalsn
Copy link
Member

michalsn commented Jan 3, 2021

As mentioned earlier - DBPrefix is a different setting than schema.

If you check the user guide, you will find a schema config variable that can be set.

Anyway, I feel like we need to use a full table name when it's possible to avoid confusion.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Verified issues on the current code behavior or pull requests that will fix them
Projects
None yet
Development

No branches or pull requests

5 participants