databases Archives - Michael Bianco

Executing Multiple SQL Statements in Elixir

Working with raw SQL in the Elixir/Ecto world is not easy. I thought executing a multi-line SQL file in a database migration would be easy; it’s not. Here’s what I learned: The execute function does not support executing multiple statements at once. It’s perplexing that there’s no direct solution for this in Elixir. While AyeSQL claims raw SQL support, it mandates a one-statement-at-a-time approach. This limitation is particularly problematic when importing SQL designed to create triggers spanning multiple lines. For simple SQL tasks, you might consider splitting the raw SQL into multiple lines: But only if your SQL is well-formed and relatively simple…

Understanding Postgres Database Triggers

I’ve always known that my knowledge and usage of postgres was elementary. Triggers and extensions were two topics that intrigued me, and I recently found a good excuse to spend the time to understand them. Here’s what I learned!..

Convert MS SQL Database to CSV or MySQL

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…

PHP, MS SQL, and Linux

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…

Tag: databases