B4J Question [solved] Swap Databases Without Reconnecting

Discussion in 'B4J Questions' started by Squiffy, Aug 22, 2015.

  1. Squiffy

    Squiffy Member Licensed User

    Is it possible in B4J to change the database mid application without relogging in? A bit like PHP's select_db() or whatever it's called.
     
  2. Squiffy

    Squiffy Member Licensed User

    Bumped this question as it's become relevant to me again.

    Researching jdbc suggests using connection.setCatalog("newdbname") but I can't find out how to do that. No such command on the pool object or the connection object. Both of these cause an exception :

    Code:
    ' Both cause exceptions, setCatalog not found.
    Dim jo as JavaObject = Main.dbpool
    jo.RunMethod(
    "setCatalog",Array As Object("newdb"))

    con=Main.dbpool.GetConnection
    Dim jo as JavaObject = con
    jo.RunMethod(
    "setCatalog",Array As Object("newdb"))
    Executing USE doesn't seem to do anything at all.
     
  3. Squiffy

    Squiffy Member Licensed User

    I'm being exceptionally thick.

    I just prefix the database to the table in the SQL, as I'm not changing servers just the database.
     
  4. mindful

    mindful Active Member Licensed User

    If you use mysql or mariadb you could use a query like: "USE `db_name`"

    The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or another USE statement is issued.
     
  5. Squiffy

    Squiffy Member Licensed User

    Doesn't seem to work though. Reading on SO the general advice is against it even though some do recommend it, but unless I'm doing it wrong it just seems to do nothing at all.

    Do you use it yourself successfully? If so, could you please post a small example snippet? I just did this :

    Code:
    con = Main.dbpool.GetConnection
    con.ExecNonQuery2(
    "USE newdb")
    No errors, no database change.
     
  6. mindful

    mindful Active Member Licensed User

    You missed the ` surrounding the database name and ExecNonQuery2 requires the Args parameter .. so you should try:
    Code:
    con.ExecNonQuery("USE `newdb`")
    If that doesn't work this is how I use it but this should not be the problem:
    I do not initialize the pool with the database name in the connection string:
    Code:
    Main.dbpool.Initialize("org.mariadb.jdbc.Driver""jdbc:mariadb://127.0.0.1:3306/?characterEncoding=utf8""myusername""mypassword")
    and each time i need a connection I have call this:
    Code:
    Sub GetNewSQLConnection(DatabaseName As Stringas SQL
      
    Dim SQLObject as SQL = Main.dbpool.GetConnection
      SQLObject.ExecNonQuery(
    "USE `" & DatabaseName & "`")
      
    Return SQLObject
    End Sub
     
  7. mindful

    mindful Active Member Licensed User

    You can verify which database is set as default for you connection using this query:
    Code:
    dim myDB As String = con.ExecQuerySingleResult("SELECT DATABASE()")
     
    Squiffy likes this.
  8. Squiffy

    Squiffy Member Licensed User

    I typed in that code instead of cut/pasting. I was using ExecNonQuery.

    Anyway, embarrassing error - I was changing the database on one handle then using another to run my queries. That and I was disobeying basic debugging rules, like showing the selected database ... bleh, it's Saturday...

    Wood / trees / etc.

    Thanks for your input, helped me get there.

    BTW it works for me without the ` around the db name.

    Cheers.
     
  9. mindful

    mindful Active Member Licensed User

    @Squiffy I tryed it now and it works ... I bumped into the ` around the db in the past when I was using mysql 5.0 or 5.1 and the database name contained "_" character .. since then i use ` around the db name ... anyway glad you got it working :D
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice