ASA server connection

Good Day.

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

connection-parameter

asked 30 Jul '13, 07:44

alexszl
36●1●1●5
accept rate: 0%

flat view

6 Answers:

active answers oldest answers newest answers popular answers

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.

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.

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

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.

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.

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.

Is there someone that can advise where I'm going wrong and what exactly
it is that I need to do.

That's what this forum is for :)

Here is a Windows command file for starting the engine:

"%ASANY8%\win32\dbeng8.exe" "C:\data\ddd8.db"

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' );

permanent link

answered 30 Jul '13, 08:38

Breck Carter
32.5k●541●726●1050
accept rate: 20%

edited 30 Jul '13, 11:10

Reimer Pods
4.5k●38●48●91

flat view

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

This scenario requires communication between the to PC's, which may be limited by a firewall. In this case make shure, that the database engine (dbeng8.exe or dbsrv8.exe) is allowed having incoming requests thru TCP/IP and maybe UDP.

permanent link

answered 30 Jul '13, 08:28

Reimer Pods
4.5k●38●48●91
accept rate: 11%

flat view

Hi again.

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

permanent link

answered 31 Jul '13, 10:48

alexszl
36●1●1●5
accept rate: 0%

Correct... dbsrv8.exe will NOT be present on a network client computer.

However, the ODBC driver MUST be present on the network client computer, otherwise an ODBC connection is not possible. The ODBC administrator, the ODBC DSN and the ODBC driver must all be present on the client computer. The challenges of distributing "client components" of SQL Anywhere are discussed in the Help... the magic search word is "deploying", and one of the V8 topics you might be interested in is this...

Adaptive Server Anywhere Programming Guide - 12. Deploying Databases and Applications - Deploying client applications - Deploying ODBC clients - ODBC driver required files

If you don't have a copy of the Compiled HTML Help for ASA 8, send me an email at breck dot carter at gmail.

(31 Jul '13, 11:20) Breck Carter

"Connected For The First Time" is a SQL Anywhere badge which should be worn with honor... because of all the options, all the myriad possibilities, all the freedom, connecting to a SQL Anywhere database for the first time is far harder than with, say, Oracle or HANA. Over the years, many changes have been made to "make connecting easier", and these changes have simply made it harder because they haven't really been "changes", but "additions", and all the old techniques remain. The result? A full description of the many ways to connect to SQL Anywhere could easily require many tens of thousands of words.

(31 Jul '13, 11:28) Breck Carter

flat view

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

//-- Connect to the database
lnv_connectserv = create n_loadcon_connectservice 
If lnv_connectserv.of_ConnectDB ( ) = 0 Then
    Open (w__frame)
End if
if isValid(lnv_connectserv) then destroy lnv_connectserv

constructor() event of lnv_connectserv  object
string ls_dbms, ls_database, ls_userid, ls_dbpass, ls_logid, ls_logpass
string ls_server, ls_dbparm, ls_lock, ls_autocommit
int     li_retVal
li_retVal = of_GetConnectionInfo ( ls_dbms, ls_database, ls_userid, ls_dbpass, ls_logid, ls_logpass, ls_server, ls_dbparm, ls_lock, ls_autocommit )
choose case li_retVal
    case -1
        // error
        messageBox("Database", "Error occurred connecting to the database. " + &
        "Probable cause - LOADCON.INI file not found or the path set is incorrect. " + &
        "Further detail will follow, then closing Application")
case 1
    SQLCA.DBMS          = ls_dbms
    SQLCA.Database      = ls_database
    SQLCA.UserID        = ls_userid
    SQLCA.DBPass        = ls_dbpass
    SQLCA.LogID         = ls_logid
    SQLCA.LogPass       = ls_logpass
    SQLCA.ServerName    = ls_server
    SQLCA.DBParm        = ls_dbparm
    SQLCA.Lock          = ls_lock

    Choose Case Lower ( ls_autocommit ) 
        Case "1", "true", "on", "yes"
            SQLCA.AutoCommit    = True
        Case "0", "false", "off", "no"
            SQLCA.AutoCommit    = False
        Case Else
            SQLCA.AutoCommit    = False
    End Choose



End choose
function of_getconnectioninfo()
as_dbms         = ProfileString ( gs_ini, "Database", "DBMS",           "")
as_database     = ProfileString ( gs_ini, "Database", "Database",       "")
as_userid       = ProfileString ( gs_ini, "Database", "UserID",     "")
as_dbpass       = ProfileString ( gs_ini, "Database", "DBPass",     "")
as_logid            = ProfileString ( gs_ini, "Database", "LogID",          "")
as_logpass      = ProfileString ( gs_ini, "Database", "LogPassword",    "")
as_server       = ProfileString ( gs_ini, "Database", "Servername", "")
as_dbparm       = ProfileString ( gs_ini, "Database", "DBParm",     "")
as_lock         = ProfileString ( gs_ini, "Database", "Lock",           "")
as_autocommit   = ProfileString ( gs_ini, "Database", "AutoCommit", "")
if as_dbms = "" or as_dbparm = "" then
    return -1
end if
Return 1

function of_connectdb()
Connect using SQLCA;
If SQLCA.SQLCode <> 0 Then
    MessageBox ("Cannot Connect to Database", SQLCA.SQLErrText )
End If
Return SQLCA.SQLCode

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 untouched

If 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

permanent link

answered 30 Jul '13, 10:35

alexszl
36●1●1●5
accept rate: 0%

edited 30 Jul '13, 11:24

Reimer Pods
4.5k●38●48●91

As Breck wrote, you'd better start the database on a network engine (dbsrv8) initially. So your DSN shouldn't specify then database file, but just the database name "demo".

(30 Jul '13, 11:28) Reimer Pods

flat view

Good Day.

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

Thanks Alex

permanent link

answered 31 Jul '13, 04:02

alexszl
36●1●1●5
accept rate: 0%

edited 31 Jul '13, 07:50

Reimer Pods
4.5k●38●48●91

flat view

Hi.

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

Alex

permanent link

answered 31 Jul '13, 06:23

alexszl
36●1●1●5
accept rate: 0%

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

connection-parameter ×44

question asked: 30 Jul '13, 07:44

question was seen: 5,291 times

last updated: 31 Jul '13, 11:28

SAP SQL Anywhere

ASA server connection

constructor() event of lnv_connectserv object

End choose

function of_getconnectioninfo()

function of_connectdb()

Follow this question

Related questions