Accessing Multi SQL instances

1

I have two almost idental DB’s on different SQL instances. They are on separate servers with each company using the exact same application, just installed in different locations.
One install is continuing to be operational, the other is now for reference but I still need to be able to access the data, ideally through the remaining application.

I’ve copied the (identical) file structures in the dictionary and given them a different Owner name and label to the second instance. This appears to work well and I can open the two databases and view the two identical files in the different MS SQL instances using the different table labels.

The opening of the old location DB with the different label opens the connection and ( can do a manual ‘Get’ to the old DB without any problem.

The issue that I’m struggling with is with T-SQL calls.

So… in short code it looks like this :

Two identical tables

Orders (Owner name = current location)
Orders (Owner name = Old location)

I can ‘Send’ a Select statement to the SQLFile (with an explicit ‘Use Old database’ statement that I have defined (as I do with all other hand written calls) and it returns no SQL errors, but neither does it return any data to the SQL file.

SQL Call looks like this :

Sendstring = ‘USE [Olddatabase] select * from Orders where…’
SQLFile{Prop:SQL}=CLIP(SendString)

This does not return any errors and is identical to all other calls that I make in the App… with the exception of the changed DB info.

When I try to read back from the SQLFile I get the message :

‘Connection is busy with results for another hstmt’.

There is no processing or other calls to either DB anywhere in the App at this point.

So my questions are…

Is there anything obvious that I’m doing wrong ? Is my methodology fundamantally flawed ?

Should the definition of the SQLfile be in the old database or the current ? I’m presuming current but I’m struggling for answers.

2

Hi,

If you look at the trace file I think you will find that Clarion doesn’t bother to bind the file buffer to the results of that query because so far as it is concerned it doesn’t return anything (doesn’t start with a select, it starts with USE). So first thing is that I think you need to use a PROP:SQLRowSet.

Second thing. Yes, MSSQL needs to which database from which to grab the data, but you also need to know that your data ends up in the file buffer you expect it to. I’m not sure what you are actually doing, because I’m pretty sure that the Clarion dictionary will not let you have two files called orders. But if you had orders_old for the file belonging to the old database and orders_new for the new, then everything on both sides would be fine, without the use database. Or, if you don’t really need to work with both databases at the same time then just having one file (orders) on which you change the owner string before first access, would also be fine. For example, I have an app that can switch between using the real data and a test database just by changing the owner string.

1 Like
3

Hi John

Thanks for the reply - I’m not aware of the issue and it sort of ties in with some SQL forums where I’ve read that the USE database statement can cause issues with some programs.

I’m also not familiar with the Prop:SQLRowSet which I’ll investigate now - thank you.

With regards to the dictionary - I have it set up so that the tables are duplicated but have a different label… So ‘Orders’ becomes ‘OrdersOld’ and with a different prefix and a different owner string pointing to the old database. The Full path name remains the same.

This works seamlessly and I can access identical tables from two different databases from within the same app using a different prefix.

Thank you for your input - I’m off to do some more research.

4

Hi John

I’ve just re-read your kind input again.

I’m very open to being proved wrong, but the issue that I (believe !) have is that both tables are named identically in the DB… both are ‘Orders’.

So whilst I can name the tables with different labels, prefixes and pathnames in the dictionary, if I’m doing a manual SQL call I still need to refer to the fields as ‘orders.???’ in the TSQL… so I need to point to the source of the data … and as I understand it this has to be through a ‘Use DB’ statement.

I’ve been playing with the SQLRowset statement and TBH I don’t really understand what the difference is between that and a standard Prop:SQL call, but so far it hasn’t made any difference.

If anyone can point me towards anywhere that I can read more info on this then I’d be grateful as the Clarion help is of very little help.

5

You might try something like this
Sendstring = ‘select * from Olddatabase.dbo.Orders where…’
SQLFile{Prop:SQL}=CLIP(SendString)

1 Like
6

The first time you open a table you are setting up a connection to the database. Anything else executed on that connection will be view things according to that connection. If you set up a second connection using a different connection string, then things you execute on that connection (i.e. using tables that use that second connection string), will be executed “from there”.

For example, if my initial connection is to fredflintstone@mydatabase then I will see all of fredflintstones tables and can just refer to them as wife, car, whatever, no schema name. Unless BarneyRubble has allowed me the ability to see his tables, I cannot look at them or do anything with them. However, if BarneyRubble has granted me some rights on his tables, then I (logged on as FredFlintstone) can probably refer to them as BarneyRubble.Car, and so on. That’s what jarodov1’s answer is telling you.

If Barney has given me his logon info, then I can also log on as BarneyRubble (by opening the barneyrubble.car table or similar in the dictionary), and doing stuff on that connection “car” will be barney rubble’s car, wife etc. I can have both connections open at the same time.

So above you really have two different methods going on: 1. you are logged on as a particular user (fred flintstone), but are specifically asking about another user’s tables (barney rubble’s). 2. you have logged on to the database as two different users, each of which is looking at their own tables.

The “use database” command is basically, “I’m logged in as FredFlintstone, but temporarily I’d like you to to make things look as though I’m logged in as FredFlintstone on BarneyRubble’s database”. Firstly, use database is a command in and of itself. So if you are going to use it in clarion you would first send: Use whatever_database; and then you would send “select whatever from sometable”. And that select will be the table that you see logged on as “x” but using database “y”. So that’s a third method, and I would say it is the most confusing, and should probably be avoided from a clarion program.

I’m an Oracle user, so mostly what you MSSQL users refer to as a “database” I would refer to as a schema or a user. If your two “databases” are on the same server, then so long as your user can see both its tables and the other user’s tables then the dot notation otheruser.table will probably be fine. If they are on completely different servers then in the Oracle world you have to have a database link set up between the two servers, and would then refer to the other table as something like orders@barneysserver. I think that is similar in the MSSQL world.

PROP:SQLRowset just tells Clarion: this probably doesn’t look to you like a command that returns data, but it really is, so set things up so that any data the server returns goes into the file buffer. Without that (i.e. plain PROP:SQL that does not start with select), clarion will assume that nothing is going to be returned.

HTH

1 Like
7

Jeremy,
You should have no issue connecting to 2 instances of SQL Server. In your dictionary did you set the owner attribute for the specific file/tables to the correct server name and database name? Also, the send command is included for backward compatibility and you should use the PROP:SQL syntax. I don’t have any examples at the moment. Finally, you do not need the USE Database command, the driver should know which it is based on the SQLFile owner. I suspect as previously mentioned that the driver is not executing the select statment since as it executed the USE statement first hence the “already busy: msg”

1 Like
8

Thank you everyone for your kind responses and time - I’ve learned a lot through this and I’m very grateful for everyones input.

I’ve implemented everyones suggestions and this morning I’m getting the results that I wanted - the data is being successfully returned to the SQLFile ‘buffer’ from the old database.

I will spend the morning unpicking the calls line by line so that I can work out exactly where I was going wrong…

Thank you all again.
… and a Merry Christmas !!

2 Likes