Exporting Data From Firebird Database to CSV File

Setup:

  • Max OS X 10.9

Warning

Ideally your firebird installation version is the same as the firebird database you want to open. Otherwise you will have issues connecting to it.

In this case I have a database that was created in version 1.5 so I installed that version of the database.

Choose file FirebirdCS-1.5.6-5026-i386.pkg.zip “Classic for MacOSX 10.4 + (x86)”

As I read the docs of Firebird you use 2 tools gsec and isql. You need to add path to bash_profile as shown here.

There is also a bash script if you want to remove a newer version of firebird. See here

Now go to where the database file is located using your Terminal. To connect to it use:

isql YourDatabaseName.fdb -user yourusername -password yourpassword

Once you connect it will show:

SQL>

You need to end your statements with a semicolon ;.

If you type enter without a semicolon it will show you:

CON>

To continue enter statements until you hit it with a ;.

SQL commands

To show all tables use:

SQL> SHOW TABLES;

Find Primary Key

Let’s say your table name is called ‘COMPANIES’.

select RDB$FIELD_POSITION,RDB$FIELD_NAME
from rdb$index_segments
where RDB$INDEX_NAME = (
select RDB$INDEX_NAME
from RDB$RELATION_CONSTRAINTS
where rdb$relation_name = 'COMPANIES'
and RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'
)
order by RDB$FIELD_POSITION;

Pending attribution to above code. I found it either on stackexchange or the net, in the middle of the night. I cannot remember the source. If you know who is the original author of this code please let me know so I can put their link here.

Find field names

Same example as ‘COMPANIES’

SELECT RDB$FIELD_NAME
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME='COMPANIES';

Output to a file

SQL> OUTPUT tables.txt;
SQL> SHOW TABLES;
SQL> shell;
$ cat tables.txt
$ exit
SQL>

The first line OUTPUT everything you give to SQL to tables.txt instead of the stdout.

Then you open the TABLES of the database you connected to.

You can go into the shell without exiting SQL.

Use cat to output the tables.txt to the stdout.

Then exit to go back to SQL.

You can also do any SQL statement after the OUTPUT statement such as:

SQL> OUTPUT emails.csv;
SQL> SELECT EMAILS FROM COMPANIES;

etc…

Command Line LS, ADD and APPEND

Warning

These commands work on a Mac or a Linux. If you want to try them on Windows you could install Cygwin.

Command Line Magic

You will use the commands ls, > and >>.

The command ls

It gives you a list.

(From here on I assume you are already in a terminal ready to type in commands).

If you are in a directory and you type in ls you will get a list of all directories and files.

You can type ls -l and you will get the “long” version. You will get details of each file and directory.

You can also use the ls with parameters.

For example:

$ls ~/Documents/

It will output a list of all directories and files in your home folder under Documents.

The command >

You can use the > command to add an input to an output.

Example

You are at the supermarket. You get an empty basket. You go shopping around.

If you use the > command it will be like:

You get something from the shelf and you put it into the basket.

If you use the > command again it will be like:

Empty the basket and put in the new item.

The > command, adds but it doesn’t append. It adds to the basket but replaces what was in the basket.

The command >>

Use the >> to append an input to an output.

You can have a basket and every time you use the >> append command you add to the basket but do not replace what was in the basket. Therefore, appends.

Using it for real now

Create a directory called groceries. Let’s say you are in your home folder Documents

$mkdir groceries
$cd groceries

Inside the groceries directory add these files:

$touch pears-are-green.jpg

$touch apples-are-red.jpg

$touch pineapples-are-yellow.jpg

With the command touch you can create files without having to open them.

Go back to the Command Line

Now it is time to use the magic.

$cd ..
$ls groceries/ > list_of_groceries.txt

This will go over the directory groceries get the name of every file and add it to the file list_of_groceries.txt

Now open that file to see if it worked.

Replace or Append?

Let’s say you create another directory called clothes

$mkdir clothes 
$cd clothes

(Keep in mind where you are. Use the command pwd to “print the working directory” otherwise you will get confused finding the files)

And you add some files to it

$touch tshirt.html
$touch pants.mp4
$touch socks.markdown

You want to add this list of names to your list_of_groceries.txt.

Let’s change the name of the file first.

$cd .. 
$mv list_of_groceries.txt list_of_things_to_buy.txt

If you use the > add (replace) command you will replace the current list:

$ls clothes/ > list_of_things_to_buy.txt

If you open such file you will see only clothes, while the food is gone.

If you wanted to append and not replace you should do instead this.

Again, remember where you are with pwd.

Your working directory should be Documents and you should have 2 directories (groceries, clothes) and 1 file (list_of_things_to_buy.txt)

$ls groceries/ > list_of_things_to_buy.txt
$ls clothes/ >> list_of_things_to_buy.txt

Now you will have a list of all file names inside list_of_things_to_buy.txt.

Applications

You can apply these concepts for:

  • Adding or appending data to a database
  • Cleaning data
  • Cleaning ginarmous excel files

Create a Twitter List With People You Follow That Do Not Follow You Back

WARNING

In this tutorial you must use the command line. If you don’t know what that is, please stop reading.

Requirements

  • Ruby 1.9.3 or 2.0.0 or 2.1.0
  • Twitter CLI Ruby gem
  • An application registerd on Twitter Developer

WARNING No 2

In this tutorial you can create a twitter list with people you follow that don’t follow you back…in 60 seconds.

If you still don’t know anything about the command line. Here are a few tutorials:

If you have Ubuntu. Install Ruby.

Follow some of this tutorial to install Ruby in Windows.

Why

Why would you create a list for people that you follow and don’t follow you back?

  • You want to connect with them
  • You want to delete some of them
  • You wonder why they don’t like you.

Here is a reason why they don’t like you. And here is another one.

in 60 Seconds

Once you have Ruby up and running. Go here and install the Twitter CLI gem.

Do a count of the number of people that you follow that don’t follow you back:

$t leaders | wc -l

In my case the result was 1096.

Now create a list:

$t list create NameoftheList

Add those people to the list. Keep in mind that it gets stuck up to 500 users. Just wait a few minutes and try it again. Go on twitter and check that the list was created, refresh the page until you see all your users added to the list.

$t leaders | xargs t list add NameoftheList

Boom Done!

If you got stuck somewhere put a comment down here or send me a tweet to @tomordonez

Octopress Bundle Install and gcc-4.2 No Such File or Directory

Setup

  • MacBook Air 10.9.2
  • Desktop Ubuntu (latest version)

Summary of the solution

Thanks to this answer. Here is the list of commands:

$ brew tap homebrew/versions && brew install apple-gcc42
$ brew link --force apple-gcc42
$ ln -nsf $(which gcc-4.2) /usr/bin/gcc-4.2

Lessons Learned

  • Don’t just copy/paste the output error into google. Read the error. Analyze why there is an error.
  • After this analysis you could try to fix it yourself
  • Or google a solution
  • Making sure you search for a specific term. “Past year” for example and not just “any time” results.

Details of the errors and the solution

I installed Octopress on an Ubuntu computer that can only be accessed on the LAN. This computer is actually a homemade server. Although it has a monitor, it doesn’t have a desk right now. It is uncomfortable to work there for more than a few minutes.. I wanted to use a Mac for development, push to github. Then pull github from Ubuntu.

Ideal setup would be to have an arm attached to the wall so you can stand up and type into the keyboard. Or ssh into the server (although this server cannot be accessed from the internet).

Steps to Octopress

Once everything was setup on the Ubuntu box, pushed to Github.

On the MAC you would do:

git clone githubrepoetcetera.git
cd localrepofolder
git checkout source
git pull origin source
rake generate
rake preview

After rake generate I got a lot of “this gem is not installed, etc, etc”

So I ran bundle install.

And this is where the problem started.

gcc-4.2 No such file or directory

After the first bundle install I got an error about not finding the gem ffi. At the end of the error it said to do:

gem install ffi -v '1.9.3'

I ran this command and there was some error about not finding the ffi gem and such version.

I did a gem list and saw I had an older version of that gem installed. So I added this specific version to my Gemfile.

Then ran bundle install again.

Now the error said:

Installing posix-spawn (0.3.9) with native extensions
Gem::Installer::ExtensionBuildError: ERROR: Failed to build gem native extension.

/Users/tomordonez/.rvm/rubies/ruby-1.9.3-p194/bin/ruby extconf.rb
creating Makefile

make
compiling posix-spawn.c
gcc: error trying to exec '/usr/bin/i686-apple-darwin11-gcc-4.2.1': execvp: No such file or directory
make: *** [posix-spawn.o] Error 255

Gem files will remain installed in /Users/tomordonez/.rvm/gems/ruby-1.9.3-p194/gems/posix-spawn-0.3.9 for inspection.
Results logged to /Users/tomordonez/.rvm/gems/ruby-1.9.3-p194/gems/posix-spawn-0.3.9/ext/gem_make.out

An error occured while installing posix-spawn (0.3.9), and Bundler cannot continue.
Make sure that `gem install posix-spawn -v '0.3.9'` succeeds before bundling.

I ran the command:

gem install posix-spawn -v '0.3.9'

But it didn’t work. It said that it couldn’t find the gem.

I decided to analyze the error. It is easy to fall into the trap of just googling for the error and copy/paste a bunch of solutions.

It is more important to read the output and think why is saying that.

In this case the error says something about not being able to build the gem and then that it couldn’t execute gcc.

Since last time I installed gcc, a few years ago I had the previous version of OSX. Then I upgraded to the latest one about 6 months ago or more.

I went to brew.

$brew --config

It showed me this:

HOMEBREW_VERSION: 0.9.5
ORIGIN: https://github.com/mxcl/homebrew.git
HEAD: (some number here)
HOMEBREW_PREFIX: /usr/local
HOMEBREW_CELLAR: /usr/local/Cellar
CPU: quad-core 64-bit sandybridge
OS X: 10.9.2-x86_64
Xcode: 5.1.1
CLT: 6.0.0.0.1.1405597879
gcc: error trying to exec '/usr/bin/i686-apple-darwin11-gcc-4.2.1': execvp: No such file or directory
GCC-4.2: build 0
Clang: 5.1 build 503
X11: N/A
System Ruby: 2.0.0-247
Perl: /usr/bin/perl
Python: /usr/bin/python
Ruby: /Users/tomordonez/.rvm/rubies/ruby-1.9.3-p194/bin/ruby

Looking at that line that says gcc: error. I went to the directory /usr/bin to search all files that had gcc. So I did some basic command like magic:

$ls -l /usr/bin/ | grep 'gcc'

And it showed me:

lrwxr-xr-x   1 root        wheel        54 Aug  8 16:06 gcc -> /usr/local/Cellar/apple-gcc42/4.2.1-5666.3/bin/gcc-4.2
lrwxr-xr-x   1 root        wheel        12 Aug  8 16:39 gcc-4.2 -> /usr/bin/gcc
-rwxr-xr-x   1 root        wheel     14224 Dec  1  2013 gcc_mavs
lrwxr-xr-x   1 root        wheel         5 Dec  1  2013 llvm-gcc -> clang

I removed gcc-4.2.

$sudo rm /usr/bin/gcc-4.2

Password:

And did again:

$brew --config

HOMEBREW_VERSION: 0.9.5
ORIGIN: https://github.com/mxcl/homebrew.git
HOMEBREW_PREFIX: /usr/local
HOMEBREW_CELLAR: /usr/local/Cellar
CPU: quad-core 64-bit sandybridge
OS X: 10.9.2-x86_64
Xcode: 5.1.1
CLT: 6.0.0.0.1.1405597879
GCC-4.2: build 5666
Clang: 5.1 build 503
X11: N/A
System Ruby: 2.0.0-247
Perl: /usr/bin/perl
Python: /usr/bin/python
Ruby: /Users/tomordonez/.rvm/rubies/ruby-1.9.3-p194/bin/ruby

I did a bundle install again. No luck

Installing posix-spawn (0.3.9) with native extensions
Gem::Installer::ExtensionBuildError: ERROR: Failed to build gem native extension.

/Users/tomordonez/.rvm/rubies/ruby-1.9.3-p194/bin/ruby extconf.rb
creating Makefile

make
compiling posix-spawn.c
make: /usr/bin/gcc-4.2: No such file or directory
make: *** [posix-spawn.o] Error 1

An error occured while installing posix-spawn (0.3.9), and Bundler cannot continue.
Make sure that `gem install posix-spawn -v '0.3.9'` succeeds before bundling.

I also did:

  • Install the command line tools again. Went to the apple page, downloaded the file and installed. Still didn’t work.

Followed some solutions about linking. In the /usr/bin I had these:

gcc -> /usr/local/Cellar/apple-gcc42/4.2.1-5666.3/bin/gcc-4.2
gcc-4.2 -> /usr/bin/gcc

When I run bundle install and it tries to build a gem, it says that it tries to execute the file gcc-4.2 but then it says “no such file”.

Looking at the files above as I understand it says “file gcc-4.2 points to /usr/bin/gcc, and gcc points to /usr/local/Cellar…”

To me it looks fine but for some reason is not working. I found this site and was able to resolve it.

It says to remove gcc-4.2. Remove the links. Install gcc-4.2 and add links again…sort of.

I ran the following:

$brew tap homebrew/versions && brew install apple-gcc42

Cloning into '/usr/local/Library/Taps/homebrew/homebrew-versions'...
remote: Counting objects: 2277, done.
remote: Compressing objects: 100% (3/3), done.
remote: Total 2277 (delta 0), reused 0 (delta 0)
Receiving objects: 100% (2277/2277), 729.81 KiB | 1.04 MiB/s, done.
Resolving deltas: 100% (1281/1281), done.
Checking connectivity... done.
Tapped 160 formulae
Warning: apple-gcc42-4.2.1-5666.3 already installed

I removed gcc42:

$brew remove apple-gcc42
Uninstalling /usr/local/Cellar/apple-gcc42/4.2.1-5666.3...

Installed it again:

$brew install apple-gcc42

==> Downloading http://r.research.att.com/tools/gcc-42-5666.3-darwin11.pkg
etc...
==> Summary
🍺  /usr/local/Cellar/apple-gcc42/4.2.1-5666.3: 104 files, 75M, built in 16 seconds

Then I linked it:

$brew link --force apple-gcc42

Warning: Already linked: /usr/local/Cellar/apple-gcc42/4.2.1-5666.3
To relink: brew unlink apple-gcc42 && brew link apple-gcc42

So I did that:

$brew unlink apple-gcc42 && brew link apple-gcc42

Unlinking /usr/local/Cellar/apple-gcc42/4.2.1-5666.3... 21 symlinks removed
Linking /usr/local/Cellar/apple-gcc42/4.2.1-5666.3... 21 symlinks created

Then:

$sudo ln -nsf $(which gcc-4.2) /usr/bin/gcc-4.2

Now running again bundle install

(etc, etc, here...)

Installing posix-spawn (0.3.9) with native extensions
Using yajl-ruby (1.1.0)
Using pygments.rb (0.6.0)
Installing redcarpet (3.1.2) with native extensions
Your bundle is complete! Use `bundle show [gemname]` to see where a bundled gem is installed.

Now rake generate and rake preview works.

Status Update August 2014

I got a few email over the last weeks.

“Tom. Where are you?”

“Tom I have not seen you at Code and Coffee”

“Tom. Are you alive?”

To which I answered:

“Yes I am still on earth”

“No I have not been coffeeing”

“I am alive I think…check pulse…I think so”

FAQ

Why are you not attending Code and Coffee?

I have been on an all-nighter binge.

If any human goes to bed at 2am. It’s improbable they will wake up at 6am to go to a 7am meetup.

What is your work Schedule?

I am Tom.

And I am a workaholic.

Hello Tom.

About 9am to 7pm.

Then I go home and spend time with the minions.

After chasing them around I start my night shift at 10pm.

Until about 2am

What do you do at night?

Any of the following:

  • Prepare exams for the team so they can improve their superpowers
  • Improve my Photoshop skills…think…memes.
  • Read tech news
  • Read about Regex
  • Work on my side projects
  • Find leads
  • Read programming books
  • Read beer brewing books
  • Read about gardening tea plants
  • Think of business opportunities
  • Think of startup ideas

Wow so those are a lot of things. So what is your priority?

  1. Study programming
  2. Develop products
  3. Find business opportunities

So it looks like they are related to making money?

Not all about making money. I want to help people get better at things. There is a lot of inefficiency in the world.

I get annoyed when somebody has an ugly website or a mobile unfriendly website.

I get stumped when someone doesn’t know how to do any of:

  • Sales
  • Marketing
  • Operations
  • Hiring
  • Tech

Maybe the reason is that I have done all this and more, while a lot of people out there have only done a few or just one.

I want to help people in areas where they are not so good at.

I want to help people to not learn from their mistakes.

Learning from mistakes is a waste of time and money.

I did a lot of this.

Why go over the same? Let me show you what NOT to do.

And you are STILL learning code (aka software development)?

When I was in college I was really bad at programming. My prerequisites included C, C ++, data structures.

I got a C in C. (Ha! this sounds funny)

I got a C- in C++

I failed data structures twice. I just couldn’t get the whole “pointers” thing.

Years passed by and I learned that I didn’t get it because it was difficult. I didn’t get it because I was not inspired.

Later on when I moved from college in Colombia to college in Miami. I had to do a senior project in Java.

Although my senior project professor was awesome. I was disgusted with Java.

Java did not inspire me.

Years passed by…

Wow you are so old…

Shut up.

I am not that old.

I didn’t say centuries passed by.

I said years.

Which could mean at least 2.

So years passed by.

Moved to Chicago and started working on some software projects.

A few blue screens of death.

But there was hope.

I took a 3-month programming course.

And suddenly I became inspired.

It was like I was blind before and suddenly I could see the matrix.

I didn’t get better at programming.

But I got better at “Behind the Scenes” that nobody has ever showed me before.

I also met a ton of people not only at this course but also outside at meetups.

I was inspired by the environment in Chicago.

I was inspired by Kevin Willer of 1871.

That strangers were willing to help you in some sort of pay it forward.

Although I am not a full time programmer. I enjoy getting involved in software.

I study a lot so I can keep up.

I love Vim.

I think the command line is the best way to do anything.

I love the feeling of success when solving a problem.

Mmmm. So what would you advice people that want to learn programming

In Miami there is a new coding school called Wyncode.

The teacher is Ed Toro. He is one of the smartest people alive.

The reality is that you cannot become a programmer in 3 months or 2 months or 4 months or 5 or 6.

It takes time.

Just like medical school.

Or law school.

You cannot swallow everything in a few months.

I would never a hire a 3-month programmer like a hospital would never hire a 3-month medical student.

You don’t put just anybody with little knowledge to operate on a patient.

But there is hope.

After 3 months you could:

  • Keep studying day and night for 3 more months
  • Study the books they study at CS schools (logics, algorithms, data structures)
  • Build your own server from scratch, build a website without framework and host it there.
  • Put your website on Hackernews so they can crash it.
  • Ask a coding mentor or friend to give you a technical interview
  • Lessons learned and loop. Learn more.
  • Apply for an internship

If your goal is not becoming a programmer you could:

  • Get training in project management
  • Get training in web design
  • Learn photoshop, illustrator and Indesign
  • Learn inbound marketing
  • Hustle into a job in the startup or tech industry

Coming back to you helping people. Looks to me like helping entrepreneurs.

Yes!

That’s more accurate.

Will you be attending more events?

Yes!

And by more. You mean attend to events. Because I have not been going to any.

Although I cannot promise the morning 7am Code and Coffee.

I might start going to WaffleWednesdays at Live Ninja.

RefreshMiami.

And a few other evening events.

Will you be organizing a code retreat?

No.

I stopped doing this event.

I didn’t do it last year.

The problem that I see in Miami is that they are not focused on quality.

Yes there are a few.

IPC, Bryce, etc.

But the majority don’t care about quality.

Code retreat is a way to get better at building quality controlled software.

The other problem is:

I was not getting better at software development. I am constantly training, studying, testing myself.

Organizing this event takes a few months of my free time.

I prefer to spend time helping people in the shorter term.

Any other questions here..a