Welcome Guest, Not a member yet? Register   Sign In
[SOLVED] Schema in SQLSRV not working
#1

(This post was last modified: 02-07-2021, 02:57 PM by Hrodriguez18.)

I have the following configuration using SQLSRV in database

PHP Code:
'DBPrefix' => '',
'schema' => 'adm',
'pConnect' => false

I necessarily have to use adm schema instead of dbo, however I see that the selection statement is not taking the schema that I am assigning to it in the configuration file.

PHP Code:
class UserController extends BaseController
{
    public function 
index()
    {
        
$userModel = new UserModel();
        
$entity $userModel->find(2);
        
dd($userModel->db->getLastQuery());

    }


result :




PHP Code:
SELECT *
FROM "users"
WHERE "users"."id" 


PHP Code:
SELECT *
FROM "users"
WHERE "users"."id" = :users.id


   

   

PHP Code:
class UserModel extends Model
{
    protected 
$DBGroup              'default';
    protected 
$table                'users';
    protected 
$primaryKey           'id';
    protected 
$useAutoIncrement     true;
    protected 
$insertID             0




On the other hand, if I rename the table to "adm.user" the selection is done correctly, but the insert or update statement then if I use the getFullName it returns DB.schema.tableName => "DB". "Schema" . "schema.tablename".







   
Hernan Rodríguez
[email protected]
Reply
#2

(02-02-2021, 11:55 PM)Hrodriguez18 Wrote: I have the following configuration using SQLSRV in database

PHP Code:
'DBPrefix' => '',
'schema' => 'adm',
'pConnect' => false

I necessarily have to use adm schema instead of dbo, however I see that the selection statement is not taking the schema that I am assigning to it in the configuration file.

PHP Code:
class UserController extends BaseController
{
 public function 
index()
 {
 
$userModel = new UserModel();
 
$entity $userModel->find(2);
 
dd($userModel->db->getLastQuery());

 }


result :




PHP Code:
SELECT *
FROM "users"
WHERE "users"."id" 


PHP Code:
SELECT *
FROM "users"
WHERE "users"."id" = :users.id






PHP Code:
class UserModel extends Model
{
 protected 
$DBGroup              'default';
 protected 
$table                'users';
 protected 
$primaryKey           'id';
 protected 
$useAutoIncrement     true;
 protected 
$insertID             0




On the other hand, if I rename the table to "adm.user" the selection is done correctly, but the insert or update statement then if I use the getFullName it returns DB.schema.tableName => "DB". "Schema" . "schema.tablename".
Yes, same problem here after we changed our scheme from dbo to a different name. We prefer to use tables and schemas such as "schema.table" because sometimes we need to combine a table with other schemas in a SQL statement. We can also connect tables with other databases if necessary such as "db1.schema.table" without having to make another database connection.
Reply
#3

(This post was last modified: 02-06-2021, 07:42 PM by Hrodriguez18.)

#########################################################################################################



I have found a solution for now, it works for me in the same way I will try to make a pull request to improve this situation.



system/Database/SQLSRV/Builder.php:197  


PHP Code:
private function getFullName(string $table): string
    
{
    
    $table_exploded explode('.'$table);
    
    if(count($table_exploded) === 2)
        {
            $this->db->schema str_replace('"'''$table_exploded[0]);
            $tableName $table_exploded[1];
        }else
        {
            $tableName $table;
        }

        if (
$this->db->escapeChar === '"')
        {
            return 
'"' $this->db->getDatabase() . '"."' $this->db->schema '"."' str_replace('"'''$tableName) . '"';
        }
        return 
'[' $this->db->getDatabase() . '].[' $this->db->schema '].[' str_replace('"'''$tableName) . ']';
    } 


I hope it works for you.
Hernan Rodríguez
[email protected]
Reply
#4

(02-06-2021, 07:32 PM)Hrodriguez18 Wrote: #########################################################################################################



I have found a solution for now, it works for me in the same way I will try to make a pull request to improve this situation.



system/Database/SQLSRV/Builder.php:197  


PHP Code:
private function getFullName(string $table): string
 
{
 
    $table_exploded explode('.'$table);
 
    if(count($table_exploded) === 2)
        {
            $this->db->schema str_replace('"'''$table_exploded[0]);
            $tableName $table_exploded[1];
        }else
        {
            $tableName $table;
        }

 if (
$this->db->escapeChar === '"')
 {
 return 
'"' $this->db->getDatabase() . '"."' $this->db->schema '"."' str_replace('"'''$tableName) . '"';
 }
 return 
'[' $this->db->getDatabase() . '].[' $this->db->schema '].[' str_replace('"'''$tableName) . ']';
 } 


I hope it works for you.

Yes pretty much same with me, except I'm breaking it to 3 segments. My Changes are;

system/Database/SQLSRV/Builder.php
PHP Code:
private function getFullName(string $table): string
{
 list(
$dbase$schema$table) = array_pad(explode('.'$table), -3null);

 $dbase = (is_null($dbase)) ? $this->db->getDatabase() : str_replace('"'''$dbase);

 $schema = (is_null($schema)) ? $this->db->schema str_replace('"'''$schema);

 $table =  str_replace('"'''$table);

 if (
$this->db->escapeChar === '"')
 {
  return '"' $dbase '"."' $schema '"."' $table '"';
 }

 return 
'[' $dbase '].[' $schema '].[' $table ']';
 


system/Database/SQLSRV/Connection.php
PHP Code:
protected function _listColumns(string $table ''): string
{
 list(
$dbase$schema$table) = array_pad(explode('.'$table), -3null);

 
$dbase = ($dbase) ?: $this->database;

 
$schema = ($schema) ?: $this->schema;

 return 
'SELECT [COLUMN_NAME] '
  ' FROM [INFORMATION_SCHEMA].[COLUMNS]'
  ' WHERE  [TABLE_NAME] = ' $this->escape($this->DBPrefix $table)
  ' AND [TABLE_SCHEMA] = ' $this->escape($schema);



and

PHP Code:
public function _fieldData(string $table): array
{
 list(
$dbase$schema$table) = array_pad(explode('.'$table), -3null);

 
$dbase = ($dbase) ?: $this->database;

 
$schema = ($schema) ?: $this->schema;

 
$sql 'SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, COLUMN_DEFAULT
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME= ' 
$this->escape(($table)) . ' AND [TABLE_SCHEMA] = ' $this->escape($schema)
  ;

 ...
 ... 

Change this file also if you are using rules. I'm not sure whether we can extend it.
system/Validation/Rules.php

PHP Code:
public function is_not_unique(string $str nullstring $field, array $data): bool
{
 
// Grab any data for exclusion of a single row.
 
list($field$whereField$whereValue) = array_pad(explode(','$field), 3null);

 
$fieldName explode('.'$field);

 
$fieldCount count($fieldName);

 if (
$fieldCount 3)
 {
  // Break the table and field apart
  sscanf($field'%[^.].%[^.].%[^.].%[^.]'$dbase$schema$table$field);

  $table $dbase '.' $schema '.' $table;
 }
 elseif (
$fieldCount 2)
 {
  // Break the table and field apart
  sscanf($field'%[^.].%[^.].%[^.]'$schema$table$field);

  $table $schema '.' $table;
 }
 else
 {
  // Break the table and field apart
  scanf($field'%[^.].%[^.]'$table$field);
 }

  ...
  ... 

and

PHP Code:
public function is_unique(string $str nullstring $field, array $data): bool
{
  // Grab any data for exclusion of a single row.
  list($field$ignoreField$ignoreValue) = array_pad(explode(','$field), 3null);

  $fieldName explode('.'$field);

  $fieldCount count($fieldName);

 if (
$fieldCount 3)
 {
  // Break the table and field apart
  sscanf($field'%[^.].%[^.].%[^.].%[^.]'$dbase$schema$table$field);

  $table $dbase '.' $schema '.' $table;
 }
 elseif (
$fieldCount 2)
 {
  // Break the table and field apart
  sscanf($field'%[^.].%[^.].%[^.]'$schema$table$field);

  $table $schema '.' $table;
 }
 else
 {
  // Break the table and field apart
  scanf($field'%[^.].%[^.]'$table$field);
 }

  ...
  ... 
Reply
#5

(This post was last modified: 02-07-2021, 03:01 PM by Hrodriguez18.)

(02-07-2021, 12:45 AM)nfaiz Wrote:
(02-06-2021, 07:32 PM)Hrodriguez18 Wrote: ################################################ ################################################ #####



He encontrado una solución por ahora, me funciona de la misma manera que intentaré hacer una solicitud de extracción para mejorar esta situación.



system / Database / SQLSRV / Builder.php: 197   Espero que funcione para usted.


PHP Code:
private function getFullName(string $table): string
 
{
 
    $table_exploded explode('.'$table);
 
    if(count($table_exploded) === 2)
        {
            $this->db->schema str_replace('"'''$table_exploded[0]);
            $tableName $table_exploded[1];
        }else
        {
            $tableName $table;
        }

 if (
$this->db->escapeChar === '"')
 {
 return 
'"' $this->db->getDatabase() . '"."' $this->db->schema '"."' str_replace('"'''$tableName) . '"';
 }
 return 
'[' $this->db->getDatabase() . '].[' $this->db->schema '].[' str_replace('"'''$tableName) . ']';
 } 

Sí, más o menos lo mismo conmigo, excepto que lo estoy dividiendo en 3 segmentos. Mis cambios son;

system / Database / SQLSRV / Builder.php system / Database / SQLSRV / Connection.php y cambie este archivo también si está usando reglas. No estoy seguro de que podamos ampliarlo. system / Validation / Rules.php y
PHP Code:
private function getFullName(string $table): string
{
 list(
$dbase$schema$table) = array_pad(explode('.'$table), -3null);

 $dbase = (is_null($dbase)) ? $this->db->getDatabase() : str_replace('"'''$dbase);

 $schema = (is_null($schema)) ? $this->db->schema str_replace('"'''$schema);

 $table =  str_replace('"'''$table);

 if (
$this->db->escapeChar === '"')
 {
  return '"' $dbase '"."' $schema '"."' $table '"';
 }

 return 
'[' $dbase '].[' $schema '].[' $table ']';
 



PHP Code:
protected function _listColumns(string $table ''): string
{
 list(
$dbase$schema$table) = array_pad(explode('.'$table), -3null);

 
$dbase = ($dbase) ?: $this->database;

 
$schema = ($schema) ?: $this->schema;

 return 
'SELECT [COLUMN_NAME] '
  ' FROM [INFORMATION_SCHEMA].[COLUMNS]'
  ' WHERE  [TABLE_NAME] = ' $this->escape($this->DBPrefix $table)
  ' AND [TABLE_SCHEMA] = ' $this->escape($schema);





PHP Code:
public function _fieldData(string $table): array
{
 list(
$dbase$schema$table) = array_pad(explode('.'$table), -3null);

 
$dbase = ($dbase) ?: $this->database;

 
$schema = ($schema) ?: $this->schema;

 
$sql 'SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, COLUMN_DEFAULT
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME= ' 
$this->escape(($table)) . ' AND [TABLE_SCHEMA] = ' $this->escape($schema)
  ;

 ...
 ... 




PHP Code:
public function is_not_unique(string $str nullstring $field, array $data): bool
{
 
// Grab any data for exclusion of a single row.
 
list($field$whereField$whereValue) = array_pad(explode(','$field), 3null);

 
$fieldName explode('.'$field);

 
$fieldCount count($fieldName);

 if (
$fieldCount 3)
 {
  // Break the table and field apart
  sscanf($field'%[^.].%[^.].%[^.].%[^.]'$dbase$schema$table$field);

  $table $dbase '.' $schema '.' $table;
 }
 elseif (
$fieldCount 2)
 {
  // Break the table and field apart
  sscanf($field'%[^.].%[^.].%[^.]'$schema$table$field);

  $table $schema '.' $table;
 }
 else
 {
  // Break the table and field apart
  scanf($field'%[^.].%[^.]'$table$field);
 }

  ...
  ... 



PHP Code:
public function is_unique(string $str nullstring $field, array $data): bool
{
  // Grab any data for exclusion of a single row.
  list($field$ignoreField$ignoreValue) = array_pad(explode(','$field), 3null);

  $fieldName explode('.'$field);

  $fieldCount count($fieldName);

 if (
$fieldCount 3)
 {
  // Break the table and field apart
  sscanf($field'%[^.].%[^.].%[^.].%[^.]'$dbase$schema$table$field);

  $table $dbase '.' $schema '.' $table;
 }
 elseif (
$fieldCount 2)
 {
  // Break the table and field apart
  sscanf($field'%[^.].%[^.].%[^.]'$schema$table$field);

  $table $schema '.' $table;
 }
 else
 {
  // Break the table and field apart
  scanf($field'%[^.].%[^.]'$table$field);
 }

  ...
  ... 


make a pull request for these changes
Hernan Rodríguez
[email protected]
Reply




Theme © iAndrew 2016 - Forum software by © MyBB