Class: Tina4::Drivers::OdbcDriver

Inherits:
Object
  • Object
show all
Includes:
Tina4::DatabaseAdapter
Defined in:
lib/tina4/drivers/odbc_driver.rb

Constant Summary

Constants included from Tina4::DatabaseAdapter

Tina4::DatabaseAdapter::CONNECT_TIMEOUT_SLACK_SECONDS, Tina4::DatabaseAdapter::CONNECT_TIMEOUT_VAR, Tina4::DatabaseAdapter::CONTRACT, Tina4::DatabaseAdapter::DEFAULT_CONNECT_TIMEOUT_SECONDS

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from Tina4::DatabaseAdapter

bounding_connect, connect_timed_out!, connect_timeout_seconds, connect_timeout_whole_seconds, implemented_by?

Instance Attribute Details

#connection ⇒ Object (readonly)

Returns the value of attribute connection.



15
16
17
 
# File 'lib/tina4/drivers/odbc_driver.rb', line 15

def connection
  @connection
end

Instance Method Details

#apply_limit(sql, limit, offset = 0) ⇒ Object

Build paginated SQL. Tries OFFSET/FETCH NEXT (SQL Server, newer ODBC sources) first. Falls back to LIMIT/OFFSET for sources that support it (MySQL, PostgreSQL via ODBC). The caller (Database#fetch) already gates on whether LIMIT is already present. Every append goes on a NEW LINE: inline it lands inside a trailing -- comment and the source silently ignores it (see the note on Drivers::SqliteDriver#apply_limit). The ORDER BY probe reads the SCRUBBED SQL, so an ORDER BY that only appears in a comment or a string literal no longer counts as one.



144
145
146
147
148
149
150
151
152
153
154
155
156
157
 
# File 'lib/tina4/drivers/odbc_driver.rb', line 144

def apply_limit(sql, limit, offset = 0)
  offset ||= 0
  if offset > 0
    # SQL Server / ANSI syntax — requires ORDER BY; add a no-op if absent
    if Tina4::Database.scrub_sql_text(sql).upcase.include?("ORDER BY")
      "#{sql}\nOFFSET #{offset} ROWS FETCH NEXT #{limit} ROWS ONLY"
    else
      # LIMIT/OFFSET fallback (MySQL, PostgreSQL via ODBC, SQLite via ODBC)
      "#{sql}\nLIMIT #{limit} OFFSET #{offset}"
    end
  else
    "#{sql}\nLIMIT #{limit}"
  end
end

#begin_transaction ⇒ Object 



159
160
161
162
163
 
# File 'lib/tina4/drivers/odbc_driver.rb', line 159

def begin_transaction
  return if @in_transaction
  @connection.autocommit = false
  @in_transaction = true
end

#close ⇒ Object 



78
79
80
81
 
# File 'lib/tina4/drivers/odbc_driver.rb', line 78

def close
  @connection&.disconnect
  @connection = nil
end

#columns(table_name) ⇒ Object

Return column metadata for a table via ODBC metadata.



196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
 
# File 'lib/tina4/drivers/odbc_driver.rb', line 196

def columns(table_name)
  stmt = @connection.columns(table_name.to_s)
  result = []
  while (row = stmt.fetch_hash)
    name    = row["COLUMN_NAME"]    || row[:COLUMN_NAME]
    type    = row["TYPE_NAME"]      || row[:TYPE_NAME]
    nullable_val = row["NULLABLE"]  || row[:NULLABLE]
    default = row["COLUMN_DEF"]     || row[:COLUMN_DEF]
    result << {
      name: name.to_s,
      type: type.to_s,
      nullable: nullable_val.to_i == 1,
      default: default,
      primary_key: false  # ODBC metadata does not reliably expose PK flag here
    }
  end
  stmt.drop
  result
rescue => e
  stmt&.drop rescue nil
  raise e
end

#commit ⇒ Object 



165
166
167
168
169
170
 
# File 'lib/tina4/drivers/odbc_driver.rb', line 165

def commit
  return unless @in_transaction
  @connection.commit
  @connection.autocommit = true
  @in_transaction = false
end

#connect(connection_string, username: nil, password: nil) ⇒ Object

Connect to an ODBC data source.

Connection string formats:

odbc:///DSN=MyDSN
odbc:///DSN=MyDSN;UID=user;PWD=pass
odbc:///DRIVER={SQL Server};SERVER=host;DATABASE=db

The leading scheme prefix "odbc:///" is stripped; the remainder is passed verbatim to ODBC::Database.new as a connection string. username: and password: are appended as UID/PWD if not already present in the connection string.

NOT bounded by TINA4_DATABASE_CONNECT_TIMEOUT, and this is a deliberate exclusion rather than an oversight. Three reasons, in order of weight:

  1. ruby-odbc is not a Tina4 dependency (it is in neither the gemspec nor the Gemfile) and its C extension will not build without unixodbc-dev, so it is installed in neither CI nor the lab. A change here could not be tested, and an untestable change to a connect path is exactly the kind of "protection" that turns out not to fire.
  2. An ODBC target is a DSN name, a file DSN or a local driver. In the general case there is no host and no port, so the contract's error message has nothing to name.
  3. The ODBC-standard bound is SQL_LOGIN_TIMEOUT, which the operator already controls: the DSN string below is passed through verbatim, so a driver-specific Login Timeout= / Connection Timeout= in it takes effect today with no framework code at all.

If ruby-odbc ever becomes testable here, the hook exists: ODBC::Database#login_timeout= maps to SQL_LOGIN_TIMEOUT (odbc.c:5475, bound at odbc.c:9366), reachable by constructing an unconnected ODBC::Database, setting it, then calling #drvconnect (odbc.c:9330) instead of connecting inside ::new.



50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
 
# File 'lib/tina4/drivers/odbc_driver.rb', line 50

def connect(connection_string, username: nil, password: nil)
  begin
    require "odbc"
  rescue LoadError
    raise LoadError,
          "The 'ruby-odbc' gem is required for ODBC connections. Install one of:\n" \
          "    bundle add ruby-odbc     # if your project uses Bundler\n" \
          "    gem install ruby-odbc    # bare driver"
  end

  dsn_string = connection_string.to_s
    .sub(/^odbc:\/\/\//, "")
    .sub(/^odbc:\/\//, "")
    .sub(/^odbc:/, "")

  # Append credentials if provided and not already embedded
  if username && !dsn_string.match?(/\bUID=/i)
    dsn_string = "#{dsn_string};UID=#{username}"
  end
  if password && !dsn_string.match?(/\bPWD=/i)
    dsn_string = "#{dsn_string};PWD=#{password}"
  end

  @connection = ODBC::Database.new(dsn_string)
  @in_transaction = false
  self
end

#connected? ⇒ Boolean

Returns:

  • (Boolean) 


83
84
85
 
# File 'lib/tina4/drivers/odbc_driver.rb', line 83

def connected?
  !@connection.nil?
end

#count_subquery_alias ⇒ Object

Postgres, MySQL, MSSQL and ODBC all REQUIRE a name for a derived table, so the COUNT probe in Database#count_probe wraps as FROM (sql) AS _count_query. SQLite and Firebird do not define this and get no alias - Firebird rejects AS in that position.



10
11
12
 
# File 'lib/tina4/drivers/odbc_driver.rb', line 10

def count_subquery_alias
  "_count_query"
end

#execute(sql, params = []) ⇒ Object

Execute DDL or DML without returning rows.



110
111
112
113
114
115
116
117
118
119
 
# File 'lib/tina4/drivers/odbc_driver.rb', line 110

def execute(sql, params = [])
  if params && !params.empty?
    stmt = @connection.prepare(sql)
    stmt.execute(*params)
    stmt.drop
  else
    @connection.do(sql)
  end
  nil
end

#execute_query(sql, params = []) ⇒ Object

Execute a SELECT query and return rows as an array of symbol-keyed hashes.



88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
 
# File 'lib/tina4/drivers/odbc_driver.rb', line 88

def execute_query(sql, params = [])
  stmt = if params && !params.empty?
    s = @connection.prepare(sql)
    s.execute(*params)
    s
  else
    @connection.run(sql)
  end

  columns = stmt.columns(true).map { |c| c.name.to_s.to_sym }
  rows = []
  while (row = stmt.fetch)
    rows << columns.zip(row).to_h
  end
  stmt.drop
  rows
rescue => e
  stmt&.drop rescue nil
  raise e
end

#last_insert_id ⇒ Object

ODBC does not expose a universal last-insert-id API. Drivers that support it can be queried via execute_query after insert.



123
124
125
 
# File 'lib/tina4/drivers/odbc_driver.rb', line 123

def last_insert_id
  nil
end

#placeholder ⇒ Object 



127
128
129
 
# File 'lib/tina4/drivers/odbc_driver.rb', line 127

def placeholder
  "?"
end

#placeholders(count) ⇒ Object 



131
132
133
 
# File 'lib/tina4/drivers/odbc_driver.rb', line 131

def placeholders(count)
  (["?"] * count).join(", ")
end

#rollback ⇒ Object 



172
173
174
175
176
177
 
# File 'lib/tina4/drivers/odbc_driver.rb', line 172

def rollback
  return unless @in_transaction
  @connection.rollback
  @connection.autocommit = true
  @in_transaction = false
end

#tables ⇒ Object

List all user tables via ODBC metadata.



180
181
182
183
184
185
186
187
188
189
190
191
192
193
 
# File 'lib/tina4/drivers/odbc_driver.rb', line 180

def tables
  stmt = @connection.tables
  rows = []
  while (row = stmt.fetch_hash)
    type = row["TABLE_TYPE"] || row[:TABLE_TYPE] || ""
    name = row["TABLE_NAME"] || row[:TABLE_NAME]
    rows << name.to_s if type.to_s.upcase == "TABLE" && name
  end
  stmt.drop
  rows
rescue => e
  stmt&.drop rescue nil
  raise e
end