ODBC

From wiki.visual-prolog.com

Revision as of 16:03, 23 September 2007 by Thomas Linder Puls (talk | contribs) (categorized)

Visual Prolog (Commercial Edition) have an ODBC package, which gives access to databases that have an ODBC driver. The distribution contains two ODBC examples (In IDE: Help->Install Examples...).

Here is a little background information about ODBC.

ODBC is an abstraction layer. (There are many abstraction layers in Windows, and the general principle is the same in all cases; It's a pattern).

The purpose of the layer is to provide uniform access to different databases.

Applications run on top of the layer. They access the layer through an Application Programming Interface (API).

Below the layer database vendors (and others) can hook-in ODBC drivers, through the Device Driver Interface (DDI).

The API/DDI pattern is an abstraction pattern that Microsoft uses in numerous contexts.

The SQL syntax you should use towards ODBC is defined by ODBC, so in principle there should be no differences in syntax: It is the responsibility of the device drivers to use this dialect of ODBC. The truth is a bit more complex, and you can find a more accurate description in Interoperability of SQL Statements.

In ODBC a parameter is "?" like in:

Invalid language.

You need to specify a language like this: <source lang="html4strict">...</source>

Supported languages for syntax highlighting:

4cs, 6502acme, 6502kickass, 6502tasm, 68000devpac, abap, actionscript, actionscript3, ada, aimms, algol68, apache, applescript, apt_sources, arm, asm, asp, asymptote, autoconf, autohotkey, autoit, avisynth, awk, bascomavr, bash, basic4gl, bf, bibtex, blitzbasic, bnf, boo, c, c_loadrunner, c_mac, c_winapi, caddcl, cadlisp, cfdg, cfm, chaiscript, chapel, cil, clojure, cmake, cobol, coffeescript, cpp, cpp-qt, cpp-winapi, csharp, css, cuesheet, d, dart, dcl, dcpu16, dcs, delphi, diff, div, dos, dot, e, ecmascript, eiffel, email, epc, erlang, euphoria, ezt, f1, falcon, fo, fortran, freebasic, freeswitch, fsharp, gambas, gdb, genero, genie, gettext, glsl, gml, gnuplot, go, groovy, gwbasic, haskell, haxe, hicest, hq9plus, html4strict, html5, icon, idl, ini, inno, intercal, io, ispfpanel, j, java, java5, javascript, jcl, jquery, kixtart, klonec, klonecpp, latex, lb, ldif, lisp, llvm, locobasic, logtalk, lolcode, lotusformulas, lotusscript, lscript, lsl2, lua, m68k, magiksf, make, mapbasic, matlab, mirc, mmix, modula2, modula3, mpasm, mxml, mysql, nagios, netrexx, newlisp, nginx, nimrod, nsis, oberon2, objc, objeck, ocaml, ocaml-brief, octave, oobas, oorexx, oracle11, oracle8, oxygene, oz, parasail, parigp, pascal, pcre, per, perl, perl6, pf, php, php-brief, pic16, pike, pixelbender, pli, plsql, postgresql, postscript, povray, powerbuilder, powershell, proftpd, progress, prolog, properties, providex, purebasic, pycon, pys60, python, q, qbasic, qml, racket, rails, rbs, rebol, reg, rexx, robots, rpmspec, rsplus, ruby, rust, sas, scala, scheme, scilab, scl, sdlbasic, smalltalk, smarty, spark, sparql, sql, standardml, stonescript, systemverilog, tcl, teraterm, text, thinbasic, tsql, typoscript, unicon, upc, urbi, uscript, vala, vb, vbnet, vbscript, vedit, verilog, vhdl, vim, visualfoxpro, visualprolog, whitespace, whois, winbatch, xbasic, xml, xorg_conf, xpp, yaml, z80, zxbasic


SELECT Name FROM Person WHERE Age > ?

Native Oracle SQL uses a different syntax for parameters, but Oracle ODBC drivers use the "?".

Microsoft has defined ODBC and developed MS SQL hand-in-hand, so not surprisingly, MS SQL follows the ODBC syntax very closely. MS Access is slightly less accurate, but in most work you will not feel any differences.

In any case the accuracy depends on the driver that you have. Most database vendors deliver ODBC drivers for their databases, but there are also third party companies that deliver ODBC drivers (e.g. DataDirect).

You can get Microsoft's ODBC drivers in the MDAC package.

Besides the obvious drivers Microsoft it also contains drivers for comma-separated text files and Excel.

The intention of this is not to use text files as a database, but to provide a simple way to read, write and otherwise manipulate comma separated text files.

The Excel driver has a similar purpose. But you must realize that the access is SQL oriented, not Excel oriented. So you can deal with date which is organised in a very special way in Excel, and thus not exploit the full features of Excel. And formulas and formatting is completely unknown in the ODBC access.

References