Welcome Guest, Not a member yet? Register   Sign In
[SOLVED] How to change db connection in CI
#1

(This post was last modified: 01-30-2018, 03:10 AM by plonknimbuzz.)

i want to dump my old db to my new db design which have different stucture.
i dump all my table and save query using get_compiled_insert() to file.

the problem is my old db is sql server and my new db is mysql

example
$this->db->set(['id'=>1, 'name'=>'john'])->get_compiled_insert('table');

result : INSERT INTO "table" ("id", "name") VALUES ('1', 'john')


this is valid sql server query but not in mysql, because the right query in mysql is: INSERT INTO `table` (`id`, `name`) VALUES ('1', 'john')

conclusion:
script: $this->db->set(['id'=>1, 'name'=>'john'])->get_compiled_insert('table');
with sql server connection: INSERT INTO "table" ("id", "name") VALUES ('1', 'john')
with mysql connection: INSERT INTO `table` (`id`, `name`) VALUES ('1', 'john')

what i want is dump sql server data to build mysql query. 
i really can solve this with my own code (build query manually), but i want to do the right way in CI.

How to do this in the right way(best practice)?
maybe i can dump the data using sql server connection and build compiled query using mysql connection.
thanks
Reply
#2

SOLVED: https://www.codeigniter.com/userguide3/d...cting.html

thanks
Reply
#3

(01-30-2018, 02:26 AM)plonknimbuzz Wrote: this is valid sql server query but not in mysql, because the right query in mysql is: INSERT INTO `table` (`id`, `name`) VALUES ('1', 'john')

That's not entirely true.

Double quotes are the correct identifier escape character per the SQL standards and MySQL does support that via the ANSI_QUOTES SQL mode.
Reply
#4

(This post was last modified: 01-31-2018, 12:01 AM by plonknimbuzz.)

(01-30-2018, 06:14 AM)Narf Wrote:
(01-30-2018, 02:26 AM)plonknimbuzz Wrote: this is valid sql server query but not in mysql, because the right query in mysql is: INSERT INTO `table` (`id`, `name`) VALUES ('1', 'john')

That's not entirely true.

Double quotes are the correct identifier escape character per the SQL standards and MySQL does support that via the ANSI_QUOTES SQL mode.

Did you mean this?

[Image: hJ5T100.png]

thanks narf, this is new knowledge for me.
Reply
#5

Possibly, the mode name is ANSI_QUOTES, but phpMyAdmin may be shortening it ... idk.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB