Skip navigation
Up to Discussions in Openfire Dev
10022 Views 6 Replies Latest reply: Oct 25, 2007 4:35 PM by John Kaster RSS
Bronze
John Kaster
5 posts since
Oct 17, 2007
Currently Being Moderated

Oct 17, 2007 12:06 PM

Support for databases that have "time" as a reserved word

 

Hi. We've created an OpenFire schema definition for Blackfish SQL so we can use our preferred database for our chat services on the CodeGear Developer Network and we've run into a minor snag.

 

 

Blackfish SQL treats "time" as a reserved word, so in order to retain that field name we've had to quote it. However, that means a) all queries need to quote the time field, which means lots of query changes, b) that the time field becomes case sensitive in the queries, and c) that we have to modify the OpenFire source code to support Blackfish SQL.

 

 

The other option, to avoid case sensitivity issues and quoting syntax, is to rename the time field to something else. This change still results in c) above.

 

 

I'm surprised to find that "time" has not been an issue with other SQL servers. We'd like to provide a schema for Blackfish SQL support, particularly because the liberal license we provide for deploying it could mean easy adoption for open source projects like OpenFire. Does anyone have suggestions for ways to support Blackfish SQL with OpenFire that minimize the impact or changes to the source? Has anyone else reported issues with "time" being a reserved word for their database backend?

 

 

Thanks for any advice you can offer.

 

 

Tags: openfire, sql, blackfish, schema, support
  • Gaston Dombiak Jiver
    Gaston Dombiak
    3,863 posts since
    Sep 26, 2001
    Currently Being Moderated
    Oct 17, 2007 3:21 PM (in response to John Kaster)
    Re: Support for databases that have "time" as a reserved word

    Hey John,

     

    Welcome to Openfire! I'm certainly interested in adding Blackfish SQL support to Openfire. I haven't used Blackfish SQL before so I will need some basic information from you.

     

    1. Is there a free version of Blackfish SQL that I can download? I would like to have one for my local test environment.

    2. Where can I get the JDBC drivers?

    3. What's the format of the URL of the JDBC driver?

    4. I'm using DbVisualizer as my DB client to execute/test my SQL statements. Do you know if I can use that client with your DB? If not which db client do you recommend using?

     

    time column.BTW, I filed JM-1153 for 3.4.0 and right now I'm testing the renaming of the

     

    Thanks,

     

      -- Gato

    • Bronze
      John Kaster
      5 posts since
      Oct 17, 2007
      Currently Being Moderated
      Oct 25, 2007 2:30 PM (in response to Gaston Dombiak)
      Re: Support for databases that have "time" as a reserved word

       

      Gato,

       

      Thanks for your quick reply. We've been busy writing our own custom plug-in for Openfire and enhancing JWChat. While that work has been under way, I'm also trying to figure out the easiest way to get your Blackfish SQL. Blackfish SQL is actually the .NET implementation of what was previously called JDataStore. We have not yet released Blackish SQL for Java, but the JDBC drivers for JDataStore work just fine with Blackfish SQL for .NET.

       

      So, I'm trying to figure out the best way to get you a freely available version of Blackfish SQL (nee JDataStore) for Java so you can use it for your testing and dev purposes. It may be a beta version of the latest Blackfish SQL in its Java form, or it may be a copy of the previous version of JDataStore, which should be compatible for Openfire purposes.

       

       

      A good background on how we support both Java and .NET with the database can be found on the architect's blog at http://blogs.codegear.com/steveshaughnessy/archive/2007/09/06/38776.aspx.More technical information on Blackfish SQL can be found at http://dn.codegear.com/blackfish

       

       

      I'll get you an answer regarding Blackfish SQL as soon as I can. We have not "gone dark" with Openfire by any means, and in fact have been rapidly making customizations to it, and JWChat, that members of the CodeGear Developer Network will really appreciate. I've been documenting what we've done as we go about it, and as soon as the public beta starts I'll post information about our changes.

       

       

      Thanks again for your prompt reply and good support.

       

       

      -- John

       

       

    • Bronze
      John Kaster
      5 posts since
      Oct 17, 2007
      Currently Being Moderated
      Oct 25, 2007 2:35 PM (in response to Gaston Dombiak)
      Re: Support for databases that have "time" as a reserved word

       

      Sorry, I didn't answer your other questions:

       

       

      >  Is there a free version of Blackfish SQL that I can download? I would like to have one for my local test environment.

       

       

      > Where can I get the JDBC drivers?

       

       

      (Just answered in previous reply)

       

       

      > What's the format of the URL of the JDBC driver?

       

       

      jdbc:borland:dsremote://<server>/<path to jds file>

       

       

      > I'm using DbVisualizer as my DB client to execute/test my SQL

      statements. Do you know if I can use that client with your DB? If not

      which db client do you recommend using?

       

       

      DbVisualizer works with previous versions of JDataStore, so it should still work with Blackfish SQL.

       

       

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points