Database ATTACH

[Uros_Zgur]

Consider a case when you have multiple databases available and you want to use any one of them at a time. SQLite ATTACH DATABASE statement is used to select a particular database, and after this command, all SQLite statements will be executed under the attached database.

Syntax

Following is the basic syntax of SQLite ATTACH DATABASE statement.

ATTACH DATABASE ‘DatabaseName’ As ‘Alias-Name’;

The above command will also create a database in case the database is already not created, otherwise it will just attach database file name with logical database ‘Alias-Name’.

What syntax would be for ATTACH for CubeSQL server using Xojo? Any idea?

[David_Cox]

Here is the manual entry:

[Tim_Hare]

Note that ATTACH is for when you want to address 2 databases at once. Otherwise, you would just Connect to each one in turn.

[Uros_Zgur]

Many thanks, Tim

yes, this is exactly what I would like to.
Connect two different databases (not tables from the same database) and use them later for SELECT command.

[Uros_Zgur]

Many thanks David,

Both databases are on the same remote server. There is no problem to connect to any of them, separately. Is there a problem, because both databases are on the same remote Volumes?

[Uros_Zgur]

Hi David,

I have 2 databases on remote server: db1.sqlite and db2.sqlite. Server IP address: 192.168.60.10
I did the connection with both databases:

db1 = New CubeSQLServer
db1.Host = “192.168.60.10”
db1.Port = 4430
db1.UserName = “admin”
db1.Password = “admin”
if (db1.Connect = false) then
MsgBox “Connection failed: " + db1.ErrorMessage + " (” + Str(db1.ErrorCode) + “)”
return
end if

db2 = New CubeSQLServer
db2.Host = "192.168.60.10
db2.Port = 4430
db2.UserName = “admin”
db2.Password = “admin”
db2.DatabaseName = “db2.sqlite”
if (db2.Connect = false) then
MsgBox “Connection failed: " + db2.ErrorMessage + " (” + Str(db2.ErrorCode) + “)”
return
end if

db2.ExecuteSQL(“ATTACH DATABASE db1.sqlite AS dbName;”)

Dim rsNames As RecordSet = db1.SQLSelect(“SELECT * FROM db1.Number INNER JOIN dbName.Name ON NumberID = NameID”)

Unfortunately there is some mistake, because it doesn’t work. I got the following error message:

[Tim_Hare]

You don’t need 2 CubeSQLServer objects. Connect to the first, then use that connection to execute the ATTACH statement on the second database file.

[Uros_Zgur]

Hi Tim,
many thanks for your help.
I still have 2 databases on remote server: db1.sqlite and db2.sqlite. Server IP address: 192.168.60.10
I did the connection with one databases as you suggested:

db2 = New CubeSQLServer
db2.Host = "192.168.60.10
db2.Port = 4430
db2.UserName = “admin”
db2.Password = “admin”
db2.DatabaseName = “db2.sqlite”
if (db2.Connect = false) then
MsgBox “Connection failed: " + db2.ErrorMessage + " (” + Str(db2.ErrorCode) + “)”
return
end if

db2.ExecuteSQL(“ATTACH DATABASE db1.sqlite AS dbName;”)

Dim rsNames As RecordSet = db2.SQLSelect(“SELECT * FROM Number INNER JOIN Name ON NumberID = NameID”)

where “Number” is Column from db1.sqlite and “Name” is Column from dbName (db1.sqlite) database.
The problem remains the same. Is it possible to get the code from you?

[Tim_Hare]

You need to specify the database that the table is in. “Main” is the name you use to refer to the database you originally connected to.

select * from main.Number inner join dbName.Name on main.Number.NumberID = dbName.Name.NameID

[Uros_Zgur]

Hi Tim,
Still doesn’t work. Obviously I did not give you all necessary data about the dabasases. Now I put the databases on “localhost” instead on remote server. I would like to send you the Xojo code with databases. Is it possible to get your e-mail?

1. Database: db_P.sqlite
   Table: Numbers
   Column: Number
2. Database: db_N.sqlite
   Table: Names
   Column: Name

[Uros_Zgur]

The Xojo code, …

[Uros_Zgur]

And databases:

[Uros_Zgur]

Hi Tim,

I realise that the problem was full-path missing in ATTACH command. It works perfectly well now if the databases are on “localhost”:

db_P = New CubeSQLServer

db_P.Host = “localhost”

.

.

.

db_P.ExecuteSQL(“ATTACH DATABASE /Library/cubesql/databases/db_N.sqlite AS dbName;”)

.
But if the databases are on remote server I got the error Message.
db_P = New CubeSQLServer

db_P.Host = “192.168.10.47”

.

.

.

db_P.ExecuteSQL(“ATTACH DATABASE /Volumes/cubeSQL/databases/databases/db_N.sqlite AS dbName;”)

Any idea what is wrong here?

[Tim_Hare]

I’m sorry, I don’t have any experience with CubeSQL in order to guide you on the path to use. Try trimming parts off from the front. It might be /databases/databases/db_N.sqlite

[Uros_Zgur]

Many Thanks Tim for the Tip,

for some reason the full Path should start with: /volume1/cubeSQL/databases/… instead with /Volumes/cubeSQL/databases/…
It works perfectly now.