I'm running an application in PB9 and connecting via and INI file to and ASA8 ODBC connection on a local PC. I connect using dba/sql.
What I want to do is connect to the same database on the PC mentioned above using the same application but from a laptop (or any other PC). The laptop is connected to the PC via a network cable through a modem/router. The laptop can see/access the files on the PC and visa-versa.
I have created an user 'alex' on the same DB who has the same rights as the dba/sql mentioned above.
On the laptop I've created an INI file ODBC connection that points through a mapped network drive 'Z' to the database file on the PC. The application on the laptop connects successfully to the database (on the PC) using either 'alex or 'dba'.
My problem is I can't connect simultaneously from the PC (using bda) and the laptop (using alex). It seems when the application is connected from either the PC or the laptop it locks the other out even though I use different db user names to connect.
I have browsed read about single users and multiple users and personal servers (dbeng9.exe) and network servers (dbsrv9.exe) for the last couple of months and got confused and given up hope and now trying again.
Is there someone that can advise where I'm going wrong and what exactly it is that I need to do.
Thank you very much. Alex
asked 30 Jul '13, 07:44
There is a difference between "connection" and "user id". The same user id can be used to make more than one connection, so creating 'alex' neither helped nor hurt your cause.
Also note: PowerBuilder is not Excel. Client connections are NOT made directly to a database file. Only the engine connects directly to the database, and client connections are made to the engine, and through the engine, indirectly to the database. It's a subtle point, but hugely important in the following.
You should show us the relevant contents of the INI file, plus the PB9 code that makes the connection, so we can see whether you are using an ODBC DSN, or are using a "DSN-less" connection.
Conspiracy theorists claim creation of ODBC is the reason Bill Gates donates so much money to charity.
It sounds like your ODBC configuration is set up to "auto start" the database with the first connection, and you absolutely SHOULD NOT do that if you are trying to connect to one database via the network from more than one computer. Another thing you should absolutely SHOULD NOT do, even if you are using only one computer, is to auto-start a database file that exists on another computer; TCP/IP is not reliable for database I/O performed by the engine... TCP/IP is OK for client connections only.
So... what you have been doing (I think) is trying to start the same database file twice, using two different engines running on two different computers, and that is impossible.
What SHOULD you do? First, use dbsrv8.exe, not dbeng8.exe, because only dbsrv8.exe supports network connections. Second, start the database.db file ahead of time, on one computer only, using dbsrv8.exe. Third, set up the ODBC configurations to NOT auto-start, and to specify only the runtime server and database names, NOT the database file name, when connecting from either computers.
One connection from the same computer that's running dbsrv8.exe can use SharedMemory as the connection protocol, the other one should use TCPIP... you may or may not have to specify that, depending on how funky the network is.
That's what this forum is for :)
Here is a Windows command file for starting the engine:
Because the dynamic database and server names were not explicitly specified via dbsrv8 -n options, the database name defaults to "ddd8" and the server name defaults to "ddd8". Specify those values, and NOT the "start line" OR the "database file" in the ODBC setup.
Here is a snippet of PowerScript that worked OK in PB 11.5, but should probably run in PB9 as well; it does a "DSN-less" connection, making setup easier (no steenking INI files required :)
SQLCA.DBMS = 'ODB' SQLCA.DBParm & = "ConnectString='Driver=Adaptive Server Anywhere 8.0;" & + "UID=dba;PWD=sql;DBN=ddd8;ENG=ddd8'," & + "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'" CONNECT USING SQLCA; IF SQLCA.SQLCODE <> 0 THEN MessageBox ( 'Error', & 'CONNECT failed in open:' & + '~r~nSQLCode = ' & + String ( SQLCA.SQLCode ) & + '~r~nSQLDBCode = ' & + String ( SQLCA.SQLDBCode ) & + '~r~n' & + SQLCA.SQLErrText ) RETURN END IF MessageBox ( 'CONNECT', 'OK' );
Please show us the ODBC settings you're using. It sounds like on both machines you're trying to start an engine with the same database file. That's not possible, as you've detected. You'll rather want to start the database on one machine, specifying a "server name" for that engine (-d <name>). On the other machine enter the same server name into the ODBC data source (under Database).
answered 30 Jul '13, 08:28
I'm still not winning with getting the laptop connected to the ASA database on the PC.
Thinking about it further the current ODBC profile (on the laptop) has the 'Adaptive Server Anywhere 8.0' database driver selected. But in a 'real' network setup, there wouldn't be a ASA database engine (dbeng/dbserv) on the client machine's - only on the PC that is running as a server. Am I correct ? If so, then the ASA driver should not be present on the client machine (laptop). Then what does one choose in it's ODBC profile ?
Thanks in advance Alex
answered 31 Jul '13, 10:48
Thanks for your prompt responses.
When I read the responses I can confirm that I am connecting to 2 database engines pointing to the same database file - which of course is not working.
Here is the relevant part from my ini file:
[DataBase] DBMS = "ODBC" database = 'DEMO' AutoCommit = False DBParm = "ConnectString='DSN=DEMO;UID=dba;PWD=sql'"Here's the PBcode that connects:
open() event of the application object
Following are print screens from the ODBC admin tool: (This forum won't let me upload images because I don't have >100 points.) I will describe the ODBC Data Source Administrator screen:
System DSN: First Tab - ODBC: data source name = DEMO (rest default) Second tab - Login: User ID = dba , Password = sql Third tab - Database: Server name = DEMO_SRV , Database file = C:\Loadcon\DB_DEMO\demo.db Network and Advanced tab left untouchedIf I'm not mistaken I have used the PB 'create MDI Application' tool with database connection, which has done the script for the database connection. Not 100% sure as this was a long time ago.
Great ! looking forward to getting this this working.
Kind Regards Alex
Thanks. I got the database running on the PC via the command prompt dbserv8.exe c:\loadcon\db_demo\demo.db and have edited the ODBC 'Data Source Name' and 'Server Name' to show 'DEMO' with login dba/sql and using Shared Memory option. and have changed the PB DB connect script to do as above to connect to the database. Great it's working!
Now onto the laptop. I have edited the ODBC to show the same names as the PC ODBC but as recommended above and instead of Shared Memory I should use TCP/IP. Well, I have been fiddling with a couple of options here but keeps on displaying the 'CONNECT failed in open' error mssg. The laptop has the same new database connect code as the PC. It probably boils down to the value of the TCP/IP field. What should go in there? Also PC running Win XP, laptop Win 7
I gone a bit further with the laptop to PC connection. However not connecting yet.
In the ODBC panel on the laptop I have entered my PC's IP addresses. The IP address values I got from running cmd and typing ipconfig - but they didn't work.
Then I stumbled upon http://www.whatismyip.com/ and got a different IP address - same result didn't work.
Then stumbled upon entering 'host=ip_address;port=2638' in the TCP/IP field. Which prompted me with a different error mssg: 'Connection failed: Database server not found.'
The firewalls on both the PC and laptop are off. ????
answered 31 Jul '13, 06:23