-
Jordi
Newbie
-
Posts: 1
Threads: 1
Joined: Jun 2023
Reputation:
0
¿How to connect to sql db using codeigniter?
I'm trying to connect to sql db using codeigniter (sqlsrv driver). But i can't..
I connected with php script, but with codeigniter i can't.
Php version: 8.2.4, sqlsrv version: php_pdo_sqlsrv_82_ts_x64, php_sqlsrv_82_ts_x64.
The error message in log:
[Microsoft][Administrador de controladores ODBC] Error de SQLSetConnectAttr del controlador.
Code: $db['default'] = array(
'dsn' => '',
'hostname' => 'localhost\SQLEXPRESS',
'username' => 'sa',
'password' => '1234,
'database' => 'dbtest',
'dbdriver' => 'sqlsrv',
'dbprefix' => '',
'pconnect' => FALSE,
'db_debug' => FALSE,
'cache_on' => FALSE,
'cachedir' => '',
'char_set' => 'utf8',
'dbcollat' => 'utf8_general_ci',
'swap_pre' => '',
'autoinit' => TRUE,
'encrypt' => FALSE,
'compress' => FALSE,
'stricton' => FALSE,
'failover' => array(),
'save_queries' => TRUE
);
Thanks!
-
jcarvalho
Newbie
-
Posts: 7
Threads: 4
Joined: Apr 2023
Reputation:
0
(06-01-2023, 12:55 AM)Jordi Wrote: ¿How to connect to sql db using codeigniter?
I'm trying to connect to sql db using codeigniter (sqlsrv driver). But i can't..
I connected with php script, but with codeigniter i can't.
Php version: 8.2.4, sqlsrv version: php_pdo_sqlsrv_82_ts_x64, php_sqlsrv_82_ts_x64.
The error message in log:
[Microsoft][Administrador de controladores ODBC] Error de SQLSetConnectAttr del controlador.
Code: $db['default'] = array(
'dsn' => '',
'hostname' => 'localhost\SQLEXPRESS',
'username' => 'sa',
'password' => '1234,
'database' => 'dbtest',
'dbdriver' => 'sqlsrv',
'dbprefix' => '',
'pconnect' => FALSE,
'db_debug' => FALSE,
'cache_on' => FALSE,
'cachedir' => '',
'char_set' => 'utf8',
'dbcollat' => 'utf8_general_ci',
'swap_pre' => '',
'autoinit' => TRUE,
'encrypt' => FALSE,
'compress' => FALSE,
'stricton' => FALSE,
'failover' => array(),
'save_queries' => TRUE
);
I use the freeTDS in Linux
I made a library
PHP Code: <?php
namespace App\Libraries;
use Config\Ncweb;
class Mssql
{
/* default values */
private $connection = null;
private $server = "";
private $database = "";
private $user = "";
private $password = "";
private $port = "";
private $hasMiniMe;
private $miniMeUrl;
private $hasDocker;
private $nc_settings;
const ICONV_TRANSLIT = "TRANSLIT";
const ICONV_IGNORE = "IGNORE";
const WITHOUT_ICONV = "";
public function __construct()
{
$this->nc_settings = new Ncweb();
$this->server = $this->nc_settings->sqlServer;
$this->database = $this->nc_settings->mssqlDatabase;
$this->user = $this->nc_settings->mssqlUser;
$this->password = $this->nc_settings->mssqlPassword;
$this->port = $this->nc_settings->mssqlPort;
$this->hasMiniMe = $this->nc_settings->hasMiniMe;
$this->miniMeUrl = $this->nc_settings->miniMeUrl;
$this->hasDocker = $this->nc_settings->hasDocker;
}
public function __destruct()
{
unset ($this->connection);
}
public function mssql_connect_pdo()
{
try
{
$options = array(
\PDO::ATTR_PERSISTENT => true,
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
\PDO::ATTR_EMULATE_PREPARES => true,
);
//removed because without the port the dynamic ports work (,$this->port)
if( DIRECTORY_SEPARATOR == '/')
{
$this->connection = new PDO("odbc:DRIVER=FreeTDS;SERVER=$this->server,$this->port;DATABASE=$this->database", $this->user, $this->password, $options);
}
else
{
$this->connection = new \PDO("odbc:DRIVER={SQL Server Native Client 11.0};SERVER=$this->server,$this->port;DATABASE=$this->database", $this->user, $this->password, $options);
}
}
catch( \PDOException $ex )
{
die( $ex->getMessage() );
}
}
public function mssql_select_pdo( $query, $encode=null )
{
try
{
$sql = mb_convert_encoding($query, 'ISO-8859-1', 'UTF-8');
$res = array();
if( $this->hasMiniMe == false )
{
$this->mssql_connect_pdo();
$statement = $this->connection->prepare( $sql );
$statement->execute();
$res = $statement->fetchAll();
$this->utf8_encode_deep($res);
log_message('info', 'status: OK ' .' query:' . $query );
}
else
{
try
{
log_message('info', 'minime:1; status:OK ' . ' query:' . $sql);
$res = $this->miniMe_Caller($sql, 1)['result']; //versoes recentes minime
//$res = $this->miniMe_Caller($sql, 1); //versoes antigas minime
}
catch (Exception $ex)
{
log_message('error', 'minime:0; status:' . $ex->getMessage() . ' query:' . $sql);
return false;
}
}
return $res;
}
catch (\PDOException $ex )
{
log_message('error', 'status:'. $ex->getMessage() . ' query:' . $query );
$res['status'] = 'nok';
$res['msg'] = $ex->getMessage();
$res['result'] = array();
return $res;
}
}
public function mssql_execute_pdo( $query )
{
if( $this->hasMiniMe == false )
{
try
{
log_message('info', 'status:OK ' . ' query:' . $query );
$this->mssql_connect_pdo();
//$this->connection->beginTransaction();
$res['rows'] = $this->connection->exec($query);
$res['result'] = true;
$res['status'] = 'ok';
return $res;//true;
}
catch ( \PDOException $ex )
{
//$this->connection->rollBack();
log_message('error', 'status:'. $ex->getMessage() . ' query:' . $query );
$res['status'] = 'nok';
$res['msg'] = $ex->getMessage();
$res['result'] = false;//array();
return $res;//false;
}
}
else
{
try
{
log_message('info', 'minime:1; status:OK ' . ' query:' . $query);
$res = $this->miniMe_Caller($query, 0); //versoes recentes minime
//$res = $this->miniMe_Caller($sql, 1); //versoes antigas minime
if( $res['status'] == 'ok' )
{
$saida = array(
'status' => 'ok',
'msg' => 'operacao efetuada com sucesso',
'result' => true,
);
}
else
{
$saida = array(
'status' => 'nok',
'msg' => 'operacao falhou',
'result' => false,
);
}
return $saida;
}
catch (Exception $ex)
{
log_message('error', 'minime:0; status:' . $ex->getMessage() . ' query:' . $query);
return false;
}
}
}
private function miniMe_Caller($query, $isSelect = 0)
{
//removed for this example
}
private function utf8_decode_deep( &$input )
{
if( is_string( $input ) )
{
if( mb_detect_encoding( $input, 'UTF-8, ISO-8859-1' ) === 'UTF-8' )
{
$input = \UConverter::transcode( $input, 'ISO-8859-1' , 'UTF-8' );
}
}
else if( is_array( $input ) )
{
foreach ( $input as &$value )
{
$this->utf8_decode_deep( $value );
}
unset( $value );
}
else if( is_object( $input ) )
{
$vars = array_keys( get_object_vars( $input ) );
foreach ( $vars as $var )
{
$this->utf8_decode_deep( $input->$var );
}
}
}
private function utf8_encode_deep( &$input )
{
if( is_string( $input ) )
{
if( mb_detect_encoding( $input, 'UTF-8', true ) === false )
{
$input = \UConverter::transcode( $input, 'UTF-8','ISO-8859-1' );
}
}
else if ( is_array( $input ) )
{
foreach ( $input as &$value )
{
$this->utf8_encode_deep( $value );
}
unset( $value );
}
else if ( is_object( $input ) )
{
$vars = array_keys( get_object_vars( $input ) );
foreach ( $vars as $var )
{
$this->utf8_encode_deep( $input->$var );
}
}
}
}
|
CodeIgniter not connecting to SQL Server using sqlsrv driver with PHP 8.2.4
