Connecting to a DB File from Powerbuilder

[Former Member]

Can anybody help on how to connect to a database file (db) from powerbuilder code (full syntax) without using a datasource name (DSN

[Breck_Carter]

Here are several samples of DSN-less connections from PowerBuilder...

// ---------------------------------------------------------------------------

// Auto-start local database with auto-stop enabled.

gtr_local = CREATE u_tr_sqlca

gtr_local.DBMS = 'ODB'

gtr_local.DBParm &
    = "ConnectString='Driver=SQL Anywhere 12;" &
    + "UID=dba;PWD=sql;" &
    + "START=C:\\Program Files\\SQL Anywhere 12\\Bin64\\dbeng12.exe -o C:\\data\\dbeng12_log_local.txt -os 10M -x none -zl -zp -zt;" &
    + "DBF=C:\\data\\local.db'," &
    + "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'"

CONNECT USING gtr_local;

IF gtr_local.SQLCODE <> 0 THEN
    MessageBox ( 'Error', 'CONNECT USING gtr_local failed:' &
        + '~r~nSQLCode = ' &
        + String ( gtr_local.SQLCode ) &
        + '~r~nSQLDBCode = ' &
        + String ( gtr_local.SQLDBCode ) &
        + '~r~n' &
        + gtr_local.SQLErrText )
    RETURN
END IF

MessageBox ( 'OK', 'CONNECT USING gtr_local OK.' )

// ---------------------------------------------------------------------------

// Connect to network database.

gtr_network = CREATE u_tr_sqlca

gtr_network.DBMS = 'ODB'

gtr_network.DBParm &
    = "ConnectString='Driver=SQL Anywhere 12;" &
    + "UID=dba;PWD=sql;DBN=network;SERVER=network'," &
    + "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'"

CONNECT USING gtr_network;

IF gtr_network.SQLCODE <> 0 THEN
    MessageBox ( 'Error', 'CONNECT USING gtr_network failed:' &
        + '~r~nSQLCode = ' &
        + String ( gtr_network.SQLCode ) &
        + '~r~nSQLDBCode = ' &
        + String ( gtr_network.SQLDBCode ) &
        + '~r~n' &
        + gtr_network.SQLErrText )
    RETURN
END IF

MessageBox ( 'OK', 'CONNECT USING gtr_network OK.' )

// ---------------------------------------------------------------------------

SQLCA.DBMS = 'ODB'

SQLCA.DBParm &
    = "ConnectString='Driver=SQL Anywhere 11;" &
    + "UID=dba;PWD=sql;" &
    + "START=C:\\Program Files\\SQL Anywhere 11\\bin32\\dbeng11.exe;" &
    + "ASTOP=NO;" &
    + "DBF=C:\\projects\\$SA_templates\\ddd11.db;" &
    + "ENG=ddd11;" &
    + "DBN=ddd11;'," &
    + "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'"

CONNECT USING SQLCA;

IF SQLCA.SQLCODE <> 0 THEN
    MessageBox ( 'Error', &
        'CONNECT 1 failed in open:' &
        + '~r~nSQLCode = ' &
        + String ( SQLCA.SQLCode ) &
        + '~r~nSQLDBCode = ' &
        + String ( SQLCA.SQLDBCode ) &
        + '~r~n' &
        + SQLCA.SQLErrText )
    RETURN
END IF

MessageBox ( 'CONNECT 1', 'OK' );

DISCONNECT USING SQLCA;

// ---------------------------------------------------------------------------

SQLCA.DBMS = 'ODB'

SQLCA.DBParm &
    = "ConnectString='Driver=SQL Anywhere 11;" &
    + "UID=dba;PWD=sql;" &
    + "ENG=ddd11;" &
    + "DBN=ddd11;'," &
    + "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'"

CONNECT USING SQLCA;

IF SQLCA.SQLCODE <> 0 THEN
    MessageBox ( 'Error', &
        'CONNECT 2 failed in open:' &
        + '~r~nSQLCode = ' &
        + String ( SQLCA.SQLCode ) &
        + '~r~nSQLDBCode = ' &
        + String ( SQLCA.SQLDBCode ) &
        + '~r~n' &
        + SQLCA.SQLErrText )
    RETURN
END IF

MessageBox ( 'CONNECT 2', 'OK' );

// ---------------------------------------------------------------------------

SQLCA.DBMS = 'ODB'

SQLCA.DBParm &
    = "ConnectString='Driver=SQL Anywhere 11;" &
    + "UID=dba;PWD=sql;DatabaseName=ruralfinds_local;EngineName=ruralfinds_local'," &
    + "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' );

// ---------------------------------------------------------------------------

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

// ---------------------------------------------------------------------------

SQLCA.DBMS = 'ODB'

SQLCA.DBParm &
    = "ConnectString='Driver=Adaptive Server Anywhere 8.0;" &
    + "UID=DBA;PWD=SQL;" &
    + "START=C:\\Program Files\\Sybase\\SQL Anywhere 8\\win32\\dbeng8.exe;" &
    + "DBF=" &
    + aaa &
    + "'"

CONNECT USING SQLCA;

IF SQLCA.SQLCODE <> 0 THEN
    MessageBox ( 'Error', &
        'CONNECT failed in bbb:' &
        + '~r~nSQLCode = ' &
        + String ( SQLCA.SQLCode ) &
        + '~r~nSQLDBCode = ' &
        + String ( SQLCA.SQLDBCode ) &
        + '~r~n' &
        + SQLCA.SQLErrText )
    RETURN
END IF

// ---------------------------------------------------------------------------

itr_sqlca = CREATE u_tr_sqlca

itr_sqlca.DBMS = 'ODB'

itr_sqlca.DBParm &
    = "ConnectString='Driver=Adaptive Server Anywhere 8.0;" &
    + "UID=DBA;PWD=SQL;" &
    + "START=C:\\Program Files\\Sybase\\SQL Anywhere 8\\win32\\dbeng8.exe;" &
    + "DBF=" &
    + is_filespec &
    + "'"

CONNECT USING itr_sqlca;

IF itr_sqlca.SQLCODE <> 0 THEN
    MessageBox ( 'Error', 'CONNECT failed in ue_post_open:' &
        + '~r~nSQLCode = ' &
        + String ( itr_sqlca.SQLCode ) &
        + '~r~nSQLDBCode = ' &
        + String ( itr_sqlca.SQLDBCode ) &
        + '~r~n' &
        + itr_sqlca.SQLErrText )
    RETURN
END IF

[Former Member]

Hi Breck, the code shown for version 8.0 (which I am using) is giving error SQLCODE 01004 (data tructed) where the same is working fine with DSN used. At the starting of the software, I have used a login window where the user names saved in a table are populated and the same information is saved in an array. After submitting, it checks the password. While opening this window, it is giving the above error message, after connecting to the database

[Breck_Carter]

Show us the exact code that you used.