#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    12
    Rep Power
    0

    SQL to Interbase with vb.net? How...?!


    Hi there, I hope someone out there can help me... please!

    I find myself tasked with trying to access the database used by an old (but still used) app. I need to be able to SQL SELECT/INSERT/UPDATE etc., like I can with SQL Server.

    The database is a single .gdb file, which is Interbase either v6 or v7.5 (How does one tell which?!) running on a server. The app runs on my PC (Windows 7) but all attempts to connect to it via vb.net (2010 Express) have ended up in hair pulling so far

    I can't even get an ODBC connection to work, to show the tables in Crystal Reports. I've been googling for days and everything seems to be a dead end, or I'm the only person in the world trying to do this!! All the drivers or providers seem to be expensive or obsolete payware, I just want something simple and cheap if at all possible.

    I was put off Firebird at first because I prefer anon forums (God Bless you here!) to the horribly old fashioned reams of email lists on the Firebird site - which just screams "Go Away! Don't bother us!" to a newbie - there doesn't seem to be a suitable list (even if I did fancy the hassle of joining Yahoo Groups) for a newcomer to ask "Help! I'm lost in endless FAQs and STILL don't know the answers to basic questions!!"

    I persevered and installed v2.6 of the Firebird ADO.NET data provider .MSI and added a reference in my test vb project - changing the compile option as required to work with framework v4.

    I seem to be having some luck with this :

    Code:
    Dim connstr As String = "Server=THESERVER:;Database=THESERVER:D:/DATABASE/LIVE/APPDATA.GDB;User ID=sysdba;Password=mypass"
    
    Dim oConn As New FirebirdSql.Data.FirebirdClient.FbConnection
    oConn.ConnectionString = connstr
    oConn.Open()
    ... BUT it errors on the .Open
    Index was out of range. Must be non-negative and less than the size of the collection.
    Parameter name: index
    I've tried slightly different conn strings
    Code:
    "data source=THESERVER;servertype=0;User id=sysdba;Password=mypass;Initial Catalog=D:/DATABASE/LIVE/APPDATA.GDB"
    - or a dataset fill based upon :
    Code:
    Dim Fdataa As New FirebirdSql.Data.FirebirdClient.FbDataAdapter("select * from smalltable", "servertype=0;username=sysdba;password=mypass;database=THESERVER:D:/DATABASE/LIVE/APPDATA.GDB")
    - but still no luck.

    If I add a random z to the server name or file path it complains with a suitably specific error, but if I restore them to the correct values it's back to the above. Likewise a z in the user id or password gives me a login error, so I must be doing something right, yes?!

    So why does the .Open fail? Exception detail :
    Code:
    System.ArgumentOutOfRangeException was unhandled
      Message=Index was out of range. Must be non-negative and less than the size of the collection.
    Parameter name: index
      ParamName=index
      Source=mscorlib
      StackTrace:
           at System.Collections.ArrayList.get_Item(Int32 index)
           at FirebirdSql.Data.Client.Managed.Version10.GdsDatabase.GetServerVersion() in C:\Users\Jiri\Desktop\NETProvider\source\FirebirdSql\Data\Client\Managed\Version10\GdsDatabase.cs:line 597
           at FirebirdSql.Data.Client.Managed.Version10.GdsDatabase.Attach(DatabaseParameterBuffer dpb, String dataSource, Int32 port, String database) in C:\Users\Jiri\Desktop\NETProvider\source\FirebirdSql\Data\Client\Managed\Version10\GdsDatabase.cs:line 223
           at FirebirdSql.Data.FirebirdClient.FbConnectionInternal.Connect() in C:\Users\Jiri\Desktop\NETProvider\source\FirebirdSql\Data\FirebirdClient\FbConnectionInternal.cs:line 224
           at FirebirdSql.Data.FirebirdClient.FbConnectionPool.Create() in C:\Users\Jiri\Desktop\NETProvider\source\FirebirdSql\Data\FirebirdClient\FbConnectionPool.cs:line 196
           at FirebirdSql.Data.FirebirdClient.FbConnectionPool.CheckOut() in C:\Users\Jiri\Desktop\NETProvider\source\FirebirdSql\Data\FirebirdClient\FbConnectionPool.cs:line 160
           at FirebirdSql.Data.FirebirdClient.FbConnection.Open() in C:\Users\Jiri\Desktop\NETProvider\source\FirebirdSql\Data\FirebirdClient\FbConnection.cs:line 593
           at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
     .. etc ...
    Is it a bug, or am I doing something wrong? Is Firebird just unable to work with such an old database? Is there something else I need to install?

    MANY many thanks if you can help!
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    12
    Rep Power
    0
    Please don't tell me I've run into the Open Source "Lone Developer Problem"?!

    Much as I love the idea of FOSS it's really frustrating when there aren't enough developers to care about your bugs, or to be able to replicate them, or the community around the project can't help either.

    Ok then, if the .net provider isn't fit for purpose yet, can anyone recommend an ODBC driver that works, is cheap or free, and is actively developed in case of bugs?

    Embarcadero
    The current owner of the old Borland codebase? Selling their wares at http://edn.embarcadero.com/interbase
    ADO.NET 2.0 driver for InterBase Developer Edition
    http://cc.embarcadero.com/Free.aspx?id=25497

    IBProvider
    Selling their OLE DB provider for Interbase at http://www.ibprovider.com/ - not cheap.

    SIBPROvider
    Can't make sense of their website http://www.sibprovider.com/ but a number of downloads site offer SIBPROvider Interbase OLE DB Provider 1.0 as Shareware $51

    IbOleDb.dll
    FOSS Project at http://iboledb.sourceforge.net/about.php
    which appears dormant and could be incomplete for all I know.

    Intersolv (ODBC)
    From Intersolv / DataDirect http://www.datadirect.com/ - don't appear to list it in the product - maybe a download site can help you find Interbase Intersolv Driver (IBINT13.DLL ver 3.11.01.00 27/02/2001) - who knows? I just happened to have it on my PC when I landed this gig

    Easysoft (ODBC)
    "Easysoft produce the only ODBC driver officially endorsed by Borland for use with InterBase 6 and InterBase 7"
    More payware, their IBODBC.DLL (4.02.00.00, 13/07/2007) (or IDODBC32.DLL now?) is apparently what you'll need if Intersolv is failing you.

    Gemini ODBC
    Was ibdatabase.com - is now at www.ibase.ru/interbase.htm - and is only in Russian. And it'll cost you.

    ZStyleGroup OLE DB driver for Interbase/Firebird
    http://www.zstyle.com.ua/Products/ibole.htm
    I couldn't find a download link or price details, but it's payware. Yawn.

    Not a great range of choices.

    It would be ideal if the Firebird .net provider could be persuaded to work with old Interbases!

    Thanks
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    12
    Rep Power
    0
    I took a look at the source code, here's where it errors at line 597 :

    Code:
    public virtual string GetServerVersion()
    {
       byte[] items = new byte[]
         {
             IscCodes.isc_info_firebird_version,
             IscCodes.isc_info_end
         };
    
      return this.GetDatabaseInfo(items, IscCodes.BUFFER_SIZE_256)[0].ToString();
    }
    Needs some bounds checking?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    12
    Rep Power
    0
    ODBC success
    Just in case some other poor unfortunate SnowedUnder comes a-looking for the same sort of thing...

    I have had the Intersolv driver on the system all along, but I hadn't managed to get it working until just now. The right format of servername+path helps

    I can get as far as a list of tables in Crystal Reports "create connection" with my DSN, but adding any of the listed tables to the report fails with a SQL -104 error. Maybe because Crystal is trying to do something v.7.5-ish with the tables and the Intersolv driver is limited to v6?

    Anyhow, in VB.net this does the trick ...

    Code:
    ' ODBC System DSN: IBTEST  = Interbase Intersolv Driver (IBINT13.DLL ver 3.11.01.00 27/02/2001) 
    ' Field: Datebase Name = THESERVER:D:\DATABASE\LIVE\APPDATA.GDB
    ' Tab: Advanced tab = leave defaults (blanks)
    Dim ConnStr As String = "dsn=IBTEST;username=sysdba;password=mypass"
    ' you may add a different db name here : ";database=THESERVER:D:\DATABASE\LIVE\TESTING.GDB"
    oConn = New Odbc.OdbcConnection(ConnStr)
    oConn.Open()
    
    Dim oDSet = New System.Data.DataSet
    Using oAdaptor = New Odbc.OdbcDataAdapter("SELECT * FROM TESTTABLE", oConn)
    Try
       oAdaptor.Fill(oDSet)
        .... etc ......
    
    DataGridView1.DataSource = oDSet.Tables(0)
    ... I can load a whole table easily into a DataGridView control. From here it should be straightforward......

    I hope that helps someone, somewhere, one day
    I'd rather not be using ODBC, but it's better than nothing.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    12
    Rep Power
    0
    Even better, without a DSN set up - connection string :

    Code:
    "Driver={Interbase Intersolv Driver};uid=sysdba;password=mypass;database=THESERVER:D:\DATABASE\LIVE\APPDATA.GDB"
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    12
    Rep Power
    0
    re: the bug in GetServerVersion

    Someone suggested a fix (only a year and a half ago!) at :
    http://www.mail-archive.com/firebird.../msg06673.html

    - is there any chance of it being incorporated into a new version?

    Code:
    public virtual string GetServerVersion()
    {
        byte[] items = new byte[]
          {
                IscCodes.isc_info_firebird_version,
                IscCodes.isc_info_end
          };
    
        //20090922 DC - Attempt to support IB2009
        //return this.GetDatabaseInfo(items,IscCodes.BUFFER_SIZE_128)[0].ToString();
    
        ArrayList info = this.GetDatabaseInfo(items, IscCodes.BUFFER_SIZE_128);
    
        if (info.Count > 0)
          {
            return info[0].ToString();
          }
        else
          {
            return string.Empty;
          }
    }
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    12
    Rep Power
    0
    Would probably fix this one, too - which has been ignored as it couldn't be reproduced
    http://tracker.firebirdsql.org/browse/DNET-367
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    12
    Rep Power
    0
    The plot thickens......

    The lone developer Jiri Cincura has just released a new version 2.6.5
    ( ADO.NET provider for Firebird version 2.6.5 released ) and has read my comments on his blog and answered me.

    Unfortunately, it was just :
    "If youíre using InterBase, use .NET provider for InterBase. Not for Firebird. Thatís the root cause of your problem."
    An earlier page on his blog ( http://blog.cincura.net/230835-fireb...and-interbase/ ) says :

    Iím getting more than average (read: more than zero) emails, asking whether FirebirdClient works with InterBase. Similarly in mailing list.

    Well, the short answer is no. The long: FirebirdClient is created as part of Firebird project. Hence itís focused on Firebird. And even both Firebird and InterBase have same history, now the engines, and more in future, are more and more different. So it may work (with some tweaks) with current versions of InterBase, but itís tested with (sponsored by, focused on) Firebird only.
    On the face of it I'd say "Fair enough." and leave it at that. However, comments on that page from Richard Clarke (richclarkdesign.com?) suggests that the above fix (David Clegg's) will allow this provider to actually work with old Interbase databases!

    October 22nd, 2009

    "I have successfully got FirebirdSql.Data.FirebirdClient v2.5.0.0 working with Interbase 7.1 with ONLY 1 tweak.

    and that is in the ServerVersion method. for Managed10 code zone.

    I have not tested it with previous versions. But i DO know that its much quicker than using the Free Interbase ODBC Driver. And also, the Firebird Client is working like a dream with RemoteEvents.

    So Ė as far as i can see Ė its actually 99% ALL GOOD :-)

    ------
    Jiri: Yes. But this may not be true in future.
    ------

    Sure Ė I agree. But for anyone wanting to use the Firebird.NET data provider for Interbase, i guess they can try it Ė but of course you will not support it.

    The main reason we are using it, is because of its speed. Also its very stable, and we have not seen any stability issues with it at all."
    So, for the sake of a one minute fix to the code - a basic error trap which improves the code anyway - Jiri has the chance to make it work with Interbase. What's the alternative?

    It sends me to sleep trying to follow the history on Interbase via Borland/Inprise/Borland, FOSS forks, CodeGear, Embarcadero, etc (this page has loads of details http://www.cvalde.net/ibRoadmap.htm - too many details )
    - but the current solutions seem to have strings attached. Shareware, registrations, payware! Argh!!!

    I just want, if possible, pretty please
    * FREE and preferably -
    * Open Source and
    * Actively Developed and
    * Well Supported

    So where now? Could Jiri possibly make a quick change to keep us happy? (me and those people he admits have been requesting it on his blog)

    Even if it's just one final tweak, so that a v2.6.6 (or whatever) is enough to do the job for old Interbases.... PLEASE Mr Cincura
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    12
    Rep Power
    0
    I have left a blog comment...

    Thanks for answering me Jiri.

    I have commented on this in the easiest place for me, <A href="http://forums.devshed.com/firebird-sql-development-61/sql-to-interbase-with-vb-net-how-820596.html#post2650555">on the forum - comment #8 </A> and shown the limited choices in an edit to <A href="http://forums.devshed.com/firebird-sql-development-61/sql-to-interbase-with-vb-net-how-820596.html#post2649171">comment #2</A>.

    Please reconsider the very quick change which sounds like it would make a lot of people happy in our search for a .net provider for old Interbases, even if this is the last time you go along with it.

    PLEASE
    ... so I'll just have to wait and see if he can be persuaded. It would be ridiculous to have to fork his codebase and set up another sourceforge project just for a couple of lines of code changes!
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    12
    Rep Power
    0
    I can't believe I'm having to spell this out to him ....

    I appreciate you have your reasons, but I'm not asking for on-going support, just a one simple addition of a few lines of code (already suggested) as an error trap around one problem line of code, which doesn't hurt and makes your code stronger - and that's it, I'll go away

    The fix has been suggested by a couple of people who report that it works for them. It's a couple of minutes of your time to make yourself very useful to many other people, that's all.

    Cheers
    It's a bit like being somewhere the tourists love, and a loved-up couple approach you holding out their camera and ask you to take their photo by the great view. Most people will be only too happy to take a minute of their time to take the photo and give the happy couple a nice picture that they'll appreciate. It's called being considerate, not being a douche. I mean, come on, would you really say "No." and walk away, leaving the couple astounded?

    We could get into arguments about Open Source philosophy and how grateful we should be for anything released to us that the wonderfully generous developers see fit to let us have, and I appreciate that BUT at the same time turning down a simple request for no well explained reason can only make people very puzzled at such seemingly strange stubborness.

    There may be many reasons for being a FOSS developer, but I would assume that some amount of glory-seeking comes into the equation for many of them. Even if it's not a prime motivator, when you're trying to impress future employers it can't hurt that a google search on your name shows that you work on a well appreciated FOSS project.

    I have to wonder why people would willingly risk a google search for their name to lead to forum threads like this one
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    12
    Rep Power
    0
    Is it me? Am I being unreasonable?
    All the time he's spent answering me so far, he could have done it by now! No wonder MS scoff at Open Source - when it's good it's great but when it's bad it sucks big time!

    You haven't done a great job of communicating your reasons why a two minute fix with no knock-on issues is too much to ask of you!

    I'll have to copy your source code, CHANGE THAT ONE LINE, and upload it to sourceforge as a new project explaining exactly why I had to jump through hoops to get there...... and I'll call it something like the "Jiri Cincura Is A **** IB Provider".

    If that's the legacy you want, so be it, it will look great for your prospective clients... remember the internet never forgets
    For those of you deciding to use this man's project, let the record show how responsive he is to not unreasonable requests! He's happy to crow about it on his LinkedIn profile, but one simple error trap request? No... "Go away!" .. in effect.

  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    12
    Rep Power
    0
    I'll stick with ODBC then
    I've even got it working with PowerShell, which is handy for a quick SELECT query :

    (testodbc.ps1)
    Code:
    function Get-ODBC-Data
    {
      param([string]$serverName=$(throw 'serverName is required.'), [string]$databaseName=$(throw 'databaseName is required.'),
            [string]$query=$(throw 'query is required.'))
    
      $conn=New-Object System.Data.Odbc.OdbcConnection
      $connStr = "Driver={Interbase Intersolv Driver};uid=sysdba;password=mypass;database=$serverName" + ":$databaseName"
      $conn.ConnectionString= $connStr
    
      # display :
    " "
    "Connection :"
    $connStr
    " "
    "SQL :"
    $query
    " "
    
      [void]$conn.open
      $cmd=new-object System.Data.Odbc.OdbcCommand($query,$conn)
      $cmd.CommandTimeout=15
      $ds=New-Object system.Data.DataSet
      $da=New-Object system.Data.odbc.odbcDataAdapter($cmd)
      [void]$da.fill($ds)
      $ds.Tables[0] | out-gridview
      [void]$conn.close()
    }
    
    
    # main:
    Get-ODBC-Data -server THESERVER -database D:\DATABASE\LIVE\APPDATA.GDB -query "select * from table1"
    Either right-click testodbc.ps1 and "Run with PowerShell" or set up test.bat for a quicker double-click method :
    @echo off
    rem Before running, run PowerShell and execute command: PS C:\Wherever> Set-ExecutionPolicy RemoteSigned

    powershell -noexit C:\your\path\testodbc.ps1
    HTH

IMN logo majestic logo threadwatch logo seochat tools logo