利用Notepad++
編碼 -> 字元集 -> 西歐語系 -> Windows – 1252 -> 貼上DB複製的big5文字 -> 編碼 -> 編譯成UTF-8
利用Notepad++
編碼 -> 字元集 -> 西歐語系 -> Windows – 1252 -> 貼上DB複製的big5文字 -> 編碼 -> 編譯成UTF-8
Codeigniter won’t prepend “N” to strings and that’s critical for SQL server to retrieve string values. The solution is to revise DB driver of CI:
File: sys\database\DB_Driver.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
function escape($str) { if (is_string($str)) { //Revise this part if($this->dbdriver == 'sqlsrv'){ $str = "N'".$this->escape_str($str)."'"; }else{ $str = "'".$this->escape_str($str)."'"; } } elseif (is_bool($str)) { $str = ($str === FALSE) ? 0 : 1; } elseif (is_null($str)) { $str = 'NULL'; } return $str; } |
Don’t use “IN” anymore!
Solution: EXISTS
1 2 3 |
SELECT * FROM current_table AS a INNER JOIN new_table AS b ON instr(b.item_key,regexp(a.item_key,"[0-9]+"))>0; |
Reference:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">(</span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">on</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> source server</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">...)</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">BACKUP</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">DATABASE</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> Northwind </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">TO</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">DISK</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 0, 0);">'c:\Northwind.bak'</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">(</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">target server</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">...)</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">RESTORE</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> FILELISTONLY </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">FROM</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">DISK</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 0, 0);">'c:\Northwind.bak'</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">(</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">look at the device names</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">...</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">and</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> determine </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">where</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> you want the mdf </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">and</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> ldf files </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">to</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> go </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">on</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> this target server</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">)</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">RESTORE</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">DATABASE</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> TestDB </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">FROM</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">DISK</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 0, 0);">'c:\Northwind.bak'</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">WITH</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> MOVE </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 0, 0);">'Northwind'</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">TO</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 0, 0);">'c:\test\testdb.mdf'</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> MOVE </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 0, 0);">'Northwind_log'</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">TO</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 0, 0);">'c:\test\testdb.ldf'</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> GO</span> |
Here is my environment: Windows 7 + IIS 7 + SQL Server 2008 R2, PHP 5.3
Believing in latest version from MS is my fault.
Reference:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
function _make_unicode($value){ if(is_string($value) && $value!="NULL") return "N".$value; else return $value; } function _insert($table, $keys, $values) { foreach($values as $index => $value){ $values[$index] = $this->_make_unicode($value); } return "INSERT INTO ".$this->_escape_table($table)." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")"; } function _update($table, $values, $where) { foreach($values as $key => $val) { $valstr[] = $key." = ".$this->_make_unicode($val); } return "UPDATE ".$this->_escape_table($table)." SET ".implode(', ', $valstr)." WHERE ".implode(" ", $where); } |
1 2 3 4 5 6 7 8 9 10 |
$serverName = "[server name]"; //serverName\instanceName $connectionInfo = array( "Database"=>"[DB name]", "UID"=>"[account]", "PWD"=>"[password]"); $conn = sqlsrv_connect( $serverName, $connectionInfo); if( $conn ) { echo "Connection established.<br />"; }else{ echo "Connection could not be established.<br />"; die( print_r( sqlsrv_errors(), true)); } |
MS SQL:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
USE [lab] GO /****** Object: Table [dbo].[Employee] Script Date: 12/17/2013 23:15:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Employee]( [id] [int] IDENTITY(1,1) NOT NULL, [department_id] [int] NULL, [first_name] [nvarchar](50) NULL, [last_name] [nvarchar](50) NULL, [email] [nvarchar](50) NULL, [ext] [nvarchar](50) NULL, [hire_date] [timestamp] NULL, [leave_date] [datetime] NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO |
My SQL:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE Employee ( id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, departmentId TINYINT UNSIGNED NOT NULL COMMENT "CONSTRAINT FOREIGN KEY (departmentId) REFERENCES Department(id)", firstName VARCHAR(20) NOT NULL, lastName VARCHAR(40) NOT NULL, email VARCHAR(60) NOT NULL, ext SMALLINT UNSIGNED NULL, hireDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, leaveDate DATETIME NULL, INDEX name (lastName, firstName), INDEX (departmentId) ) |