Crystal-ODBC Driver

News
1

Just published crystal-odbc an ODBC Driver/Connector for Crystal. With the hope that it will make Crystal accessible to people who want to connect to databases like DB2, Oracle, MS SQL Server and so on :laughing:

10 Likes
2

Lovely!

Did you base the code in crystal-sqlite3 or started from scratch?

You used db/spec! Iā€™m happy.

I guess you are using this to extract data from a production database, right? Or you have a lot of experience with ODBC. Do you mind sharing a bit of context that pushed you here?

1 Like
3

Thanks Brian, Yes I looked at crystal-sqlite3 for implementation and db-api for other details. In past I have used ODBC and was looking for access to DB2 for small prototype, so that made me think of bringing things together and put on public use (in case someone else is having the similar requirements).

2 Likes
4

Wow, Thank you so much! :smiley: :tada:

1 Like
5

Hi,

i just started using your crystal-odbc, and I have a problem getting strings back from the DB.

Iā€™m using it with unixODBC 2.3.7 and the IBM informix driver.
I have everything configured correctly, and can use isql to connect to the DB interactively and get data.
I can also connect to the DB with crystal-odbc and get data, but anything that is Text in the db, I get only one or sometimes two characters:

colname (colno) (coltype) (collength) 
a (1) (258) (4)
ev (2) (266) (4365)
g (3) (257) (2)
e (4) (257) (2)
re (5) (257) (2)
p (6) (258) (4)

(colname is supposed to be the name of the column, wich would correctly look like this:

+---------------------------------------------------------------------------------------------------------------------------------+-------+--------+----------+
| colname                                                                                                                         | colno | coltype| collength|
+---------------------------------------------------------------------------------------------------------------------------------+-------+--------+----------+
| agentid                                                                                                                         | 1     | 258    | 4        |
| eventdatetime                                                                                                                   | 2     | 266    | 4365     |
| gmtoffset                                                                                                                       | 3     | 257    | 2        |
| eventtype                                                                                                                       | 4     | 257    | 2        |
| reasoncode                                                                                                                      | 5     | 257    | 2        |
| profileid                                                                                                                       | 6     | 258    | 4        |
+---------------------------------------------------------------------------------------------------------------------------------+-------+--------+----------+

the code is basically this:

require "db"
require "odbc"

def get_table_info(db, table)
  table_id = db.scalar("select tabid from systables where tabname = ?", table.downcase)
  db.query("select colname, colno, coltype, collength from syscolumns where tabid = ? order by colno", table_id) do |ad|
    puts "#{ad.column_name(0)} (#{ad.column_name(1)}) (#{ad.column_name(2)}) (#{ad.column_name(3)}) "
    ad.each do
      puts "#{ad.read} (#{ad.read(Int64)}) (#{ad.read(Int64)}) (#{ad.read(Int64)})"
      # using ad.read(String) makes no difference
      puts "#{ad.read(String)} (#{ad.read(Int64)}) (#{ad.read(Int64)}) (#{ad.read(Int64)})"
    end
  end
end

DB.open "odbc://DSN=datasourcename;UID=user;PWD=password" do |db| # values changed
    get_table_info(db, "tablename")
end

As I said, the DB is a ibm informix on a Cisco UCCX, from which I am trying to extract reporting data.

Any Ideas what could be going wrong, and where/how I would start looking for the problem?

6

Can you look at the schema definition and see what is the data type for colname? Also would be quicker if you can get on gitter and DM me directly.

7

Crystal:

colty = ad.read
puts colty.class, colty.inspect```

output:

String
"s\u0000\u0000\u0000"

The database manual states that the colname should be varchar(256) and the database schema itself has type 13 which according to the manual is VARCHAR.

8

Issue has been solved with update to the odbc shard.

Thanks @naqvis for the quick support and fix!