Tag Archive for 'databases'

Convert MS SQL Database to CSV or MySQL


Strict Standards: call_user_func_array() expects parameter 1 to be a valid callback, non-static method CodeColorerLoader::CallBeforeHighlightCodeBlock() should not be called statically in /home/iloveitaly/mabblog.com/blog/wp-includes/plugin.php on line 170

Strict Standards: Non-static method CodeColorer::GetInstance() should not be called statically in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer.php on line 218

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer-core.php on line 53

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer-core.php on line 54

Strict Standards: call_user_func_array() expects parameter 1 to be a valid callback, non-static method CodeColorerLoader::CallAfterHighlightCodeBlock() should not be called statically in /home/iloveitaly/mabblog.com/blog/wp-includes/plugin.php on line 170

Strict Standards: Non-static method CodeColorer::GetInstance() should not be called statically in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer.php on line 226

This is more of a thorny issue that you would at first expect – Microsoft does not provide an easy way out of their database if you are on a different platform.

The open source (or paid!) tools available for interacting with Microsoft SQL databases on OS X are very limited. There are not any native GUIs available (to my knowledge), I’ve found that getting TDS up and running is a pain, and it seems impossible to import a .bak or ms sql insert dump. Additionally, I’ve had weird encoding + CSV formatting issues when given a CSV exported from a dump given to me.

I’ve discovered a workaround to the issue that has worked reliable for very large data sets (30,000+ rows). Oracle’s SQL Developer application can connect to a ms sql database. Once you have a connection established you can export to a bunch of different formats. However, I’ve had encoding issues when exporting as a CSV or other plain text format. However, exporting the ms sql from Oracle SQL developer as a XLS is very reliable (text with quotes, commas, and other characters are perfectly preserved).

Google has released a great tool for handling large CSVs. The great thing about Google Refine is that it can import data from a variety of formats – one being XLS. If you import the XLS file into Google Refine and then export as an CSV you’ll have a data format which works great with libraries such as Ruby’s CSV class or CSV import functionality on software such as Sequel Pro. A multi-step process, but it has worked reliably.

If you are looking to convert a mdb (a Microsoft Access database) to SQL there is a great tool for converting a mdb to sqlite which you can then easily export a MySQL compatible SQL dump or a plain CSV file either using a tool like Base or the command line.

PHP, MS SQL, and Linux


Strict Standards: call_user_func_array() expects parameter 1 to be a valid callback, non-static method CodeColorerLoader::CallBeforeHighlightCodeBlock() should not be called statically in /home/iloveitaly/mabblog.com/blog/wp-includes/plugin.php on line 170

Strict Standards: Non-static method CodeColorer::GetInstance() should not be called statically in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer.php on line 218

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer-core.php on line 53

Deprecated: Non-static method CodeColorerOptions::ParseOptions() should not be called statically, assuming $this from incompatible context in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer-core.php on line 84

Deprecated: Non-static method CodeColorerOptions::PopulateDefaultValues() should not be called statically, assuming $this from incompatible context in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer-options.php on line 70

Deprecated: Non-static method CodeColorerOptions::FilterLanguage() should not be called statically, assuming $this from incompatible context in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer-options.php on line 106

Deprecated: Non-static method CodeColorerOptions::GetLanguageMappings() should not be called statically, assuming $this from incompatible context in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer-options.php on line 250

Deprecated: Non-static method CodeColorerOptions::ParseBoolean() should not be called statically, assuming $this from incompatible context in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer-options.php on line 161

Deprecated: Non-static method CodeColorerOptions::ParseBoolean() should not be called statically, assuming $this from incompatible context in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer-options.php on line 177

Deprecated: Non-static method CodeColorerOptions::ParseMode() should not be called statically, assuming $this from incompatible context in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer-options.php on line 73

Deprecated: Non-static method CodeColorerOptions::ParseMode() should not be called statically, assuming $this from incompatible context in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer-options.php on line 76

Deprecated: Non-static method CodeColorerOptions::ParseMode() should not be called statically, assuming $this from incompatible context in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer-options.php on line 79

Deprecated: Non-static method CodeColorerOptions::ParseMode() should not be called statically, assuming $this from incompatible context in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer-options.php on line 82

Deprecated: Non-static method CodeColorerOptions::ParseMode() should not be called statically, assuming $this from incompatible context in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer-options.php on line 85

Deprecated: Non-static method CodeColorerOptions::ParseMode() should not be called statically, assuming $this from incompatible context in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer-options.php on line 88

Deprecated: Non-static method CodeColorerOptions::ParseMode() should not be called statically, assuming $this from incompatible context in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer-options.php on line 91

Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer-core.php on line 54

Strict Standards: call_user_func_array() expects parameter 1 to be a valid callback, non-static method CodeColorerLoader::CallAfterHighlightCodeBlock() should not be called statically in /home/iloveitaly/mabblog.com/blog/wp-includes/plugin.php on line 170

Strict Standards: Non-static method CodeColorer::GetInstance() should not be called statically in /home/iloveitaly/mabblog.com/blog/wp-content/plugins/codecolorer/codecolorer.php on line 226

Note: this was a draft from 2009. I never finished documenting everything, and I (thankfully!) no longer have to integrate with the MS SQL database mentioned here. However, I figured I throw this information out there in case someone was running into the same configuration issues I was.

Usually I am lucky enough that most of my clients don’t have many of their internal operations tied into their web site’s databases; if they are using MS SQL or some other database engine that isn’t open source I’m able to easily convince them to transfer their data over to MySQL. Recently I was working with a client where this wasn’t the case, there was no way around it: I would have to integrate with a MS SQL database.

Information on connecting to MS SQL from PHP exists on the web, but most of it is either unclear, incomplete, or outdated. I hope to bundle together most of the information I found into one blog post to make it a bit simpler to compile a custom PHP installation with MS SQL capabilities using the freetds library.

In the PHP installation script I changed/added the freetds installation line to this:

SYBASE={$INSTALLDIR}
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$SYBASE/lib
export SYBASE LD_LIBRARY_PATH

cd ${SRCDIR}/${FREETDS}
./configure --prefix=${INSTALLDIR} --with-tdsver=8.0 --enable--msdblib --enable-sybase-compat
make
make install

References:





Strict Standards: call_user_func_array() expects parameter 1 to be a valid callback, non-static method K2::init_scripts() should not be called statically in /home/iloveitaly/mabblog.com/blog/wp-includes/plugin.php on line 405

Strict Standards: call_user_func_array() expects parameter 1 to be a valid callback, non-static method K2::setup_rolling_archives() should not be called statically in /home/iloveitaly/mabblog.com/blog/wp-includes/plugin.php on line 405