B4J Question [solved] Swap Databases Without Reconnecting

[Squiffy]

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.

 

[Squiffy]

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 :

' 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.

 

[Squiffy]

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.

 

[mindful]

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.

 

[Squiffy]

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 :

con = Main.dbpool.GetConnection
con.ExecNonQuery2("USE newdb")

No errors, no database change.

 

[mindful]

You missed the ` surrounding the database name and ExecNonQuery2 requires the Args parameter .. so you should try:

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:

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:

Sub GetNewSQLConnection(DatabaseName As String) as SQL
  Dim SQLObject as SQL = Main.dbpool.GetConnection
  SQLObject.ExecNonQuery("USE `" & DatabaseName & "`")
  Return SQLObject
End Sub

 

[mindful]

You can verify which database is set as default for you connection using this query:

dim myDB As String = con.ExecQuerySingleResult("SELECT DATABASE()")

 

[Squiffy]

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.

 

[mindful]

@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