Re: Gambas + ODBC + MSSQLS

[First Last <d4t4full@xxxxxxxxx>]

On Fri, Apr 19, 2024 at 4:45 PM System64 Development <64xcode@xxxxxxxxx>
wrote:

> Hello.
> I am trying to connect to a mssql server database, which is hosted on a
> windows server, through ODBC.
> Try installing: unicodc, fretds, etc. I even installed a mirosoft driver
> but I can't connect to the database.
> The fact is that in gambas it seems the ODBC connector is linked to
> unixodbc so if I want to use gambas to extract data from that database
> I will have to use unixodbc I suppose.
> Has anyone done any of this? And a "recipe" of steps to follow to set
> everything up?
> Thanks.
> Martin.
>

Martin,

Hi, I contributed some code 9 years ago to Gambas' ODBC component (I'm not
the original author, though).
My intention was to be able to completely bypass most, if not all, the
INI-file-juggling needed by UnixODBC's driver manager.
With the help of Benoît himself, ODBC is possible in Gambas. It has one or
two hiccups, though, like SELECTs not returning row counts, but that is by
design in ODBC.

You should get at least UnixODBC, and FreeTDS (the low-level driver) to
connect to Microsoft SQL Server.
You must also use the odbcinst tool to install (register) the necessary
database driver to ODBC's driver manager, or... edit the INI by hand (the
only INI to edit, IIRC).

On Gambas, the Connection object can be told to use a ConnectionString
instead of the DSN stored in UnixODBC's various INIs. This mimics Windows'
VB6, .Net, etc, although the connstring is obviously different.

To do so:

  Dim conn As Connection

  Inc Application.Busy  ' This gives the hourglass/busy pointer

  conn = New Connection
  conn.Timeout = 5  ' Never remember if this is seconds or milliseconds;
beware.
  conn.Type = "odbc"
  ' In the next line you can try different values in TDS_Version, make sure
your app works with the chosen one.
  ' I found that  TDS_Version=7.2 works the most, and supports MSSQLServer
2003 and up.
  conn.Host = "Driver=FreeTDS; TDS_Version=7.2; Server=<sqlIPOrName>;
Port=<sqlServerPort>; Database=<initialDatabaseName>; UId=<sqlUser>;
Pwd=<sqlPassword>"

  Try
    conn.Open()
  Finally
    Dec Application.Busy  ' When Application.Busy is zero, pointer gets
back to normal/arrow
  Catch
    Message.Title = Application.Title
    Message.Error("Error connecting: '" & Error.Text & "' in " &
Error.Where)

Once the connection's open, you can run queries as follows:

  Dim res As Result

  res = conn.Execute("SELECT * FROM Table1")
  While res.Available()

    ' Do something with result's field data array res[fldIndex]

    If (res.MoveNext()) Then
      Break  ' Exit the loop
    Endif

  Wend

That was extracted (not complete, just the barebones) from a working
program I have made and so far runs flawlessly.
I also tried my program with SQLite and Firebird drivers successfully.

Hope it helps,
zxMarce.