Connecting to SQLA running as a service

[Former Member]

I am trying to get a SQL Anywhere 9 database to run as a service and connect to it from PowerBuilder 10.

My OS is Windows 7 64bit. The database is the one that comes with PB10 - easdemo9.db.

No surprise - Sybase Central doesn't work, it gets a Java error when attempting to connect. So I used the command line to create the service. So I have a service and can't connect from PowerBuilder. I am guessing that the settings on the service aren't ideal either.

The error message is: SQLSTATE = IM002 [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

The connection setting is: SQLCA.DBParm = "ConnectString='eng=myeng;dbn=easdemo9;uid=dba;pwd=sql'," + & "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'"

I ran regedit and found these values for the service 'ASANYe_myserv'.

ImagePath: C:\\Program Files (x86)\\Sybase\\SQL Anywhere 9\\win32\\dbeng9.exe -hvASANYe_myserv

Parameters: -n myeng -c8m "C:\\Program Files (x86)\\Sybase\\Shared\\PowerBuilder\\easdemo9.db"

[Breck_Carter]

A database started as a service is no different than any other when it comes to connecting.

Try adding this to your ConnectString: Driver=Adaptive Server Anywhere 9.0;

SQLCA.DBParm = "ConnectString='Driver=Adaptive Server Anywhere 9.0;eng=myeng;dbn=easdemo9;uid=dba;pwd=sql'," + & 
"ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'"

PowerBuilder uses ODBC to connect to SQL Anywhere, and since you are attempting a DSN-less connection (no DSN= in the ConnectString) you need to tell it something about what flavor of ODBC you are using.

...or, you could create a DSN and use that instead of Driver.

FWIW here is a sample of tested PowerBuilder code showing a DSN-less connection to a version 9 database; it includes some extra parameters you probably don't need...

SQLCA.DBMS = 'ODBC'

SQLCA.DBParm &
    = "ConnectString='Driver=Adaptive Server Anywhere 9.0;" &
    + "UID=dba;PWD=sql;DatabaseName=asademo;EngineName=asademo9;" &
    + "AutoStop=No;Integrated=No;" &
    + "CommLinks=SharedMemory,TCPIP{HOST=tsunami;DOBROAD=NONE};" &
    + "Compress=No',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

[VolkerBarth]

Just to add: In case you're about to use a DSN, make sure you use the appropriate ODBC Administrator for the bit-ness of your application (32 bit, I suppose). On Windows 7 64 Bit, there is a ODBCAD32.exe both for 32 and 64 bit, and you need to choose the correct one.

[Former Member]

I have no problems connecting on XP, the issue is that on my Windows 7 laptop the service isn't configured correctly because Sybase Central is a load of heaping brown stuff and won't run due to some java error. The help on dbsvc isn't of much help.

[Breck_Carter]

Please tell us what your exact symptoms are. Also tell us what build of SQL Anywhere you are running. In order to be successful on Windows 7 you may need to install the final EBF. If you want help with dbsvc just ask.

[Former Member]

I have it running on Windows XP and SQL Anywhere 9.0.1. I'll have to revisit my Windows 7 laptop and maybe try it with a newer version of SQL Anywhere. The symptom was a Java error starting the Sybase Central that comes with SA9. So then I tried using the command line to create a service but the documentation doesn't explain it very well.

[Former Member]

On XP & SA9 I used Sybase Central to add a service. The parameters are:

-c 8m -n engDemoDB "C:Program FilesSybaseSharedPowerBuildereasDemo10.db" -n dbnDemoDB

My PowerBuilder connection looks like this:

sqlca.DBMS = "ODBC" sqlca.DBParm = "ConnectString='Driver=Adaptive Server Anywhere 9.0;ENG=engDemoDB;DBN=dbnDemoDB;UID=dba;PWD=sql', "ConnectOption='SQL_DRIVER_CONNECT, SQL_DRIVER_NOPROMPT'"

[Former Member]

Sorry about the lack for formatting of my last reply, the comment block doesn't give the nice formatting options as where you enter the question.