Unable to connect to the database with sysprogress through sqlexp - Forum - OpenEdge RDBMS - Progress Community

Unable to connect to the database with sysprogress through sqlexp

 Forum

Unable to connect to the database with sysprogress through sqlexp

This question is answered

Hello,

OpenEdge: 10.1B (I know, very old :-) )
OS: Windows
Databases: the same problem for both the client's database and sports2000.

I was faced with the following problem: unable to connect to the database with sysprogress through sqlexp.

Database is started. User SYSPROGRESS is created through Data Dictionary. But when connect to database through sqlexp with SYSPROGRESS then SQL Explorer does not start, instead return to the OS command line without any error messages (sqlexp.log and SQLExplorer.exceptions -  there is no any messages also).

At the same time I have no problem to connect to database by ODBC from MS Excel (through Microsoft Query) with this user.

I have no idea why this is happening and how to fix it to be able to connect via SQL Explorer.

Has anyone encountered a similar problem?

Regards,
Valeriy

Verified Answer
  • Hi Valeriy,

    I noticed that the password contains special character '@' and this is what is causing the problem. I can remember that we have fixed this issue in later versions of OE.

    Can you try changing the password of sysprogress user such that it does not include '@'  and then try connecting through sqlexp?

    Thanks,

    Akthar.

All Replies
  • Small addition - another user, but without DBA rights , can connect to the database through sqlexp. This strange problem refers only for SYSPROGRESS.

  • I have created a copy of sports2000 database and created a sysprogress user using a ABL procedure file.

    Procedure file contains:

    ----------------------------------

    RUN createSQLDBA.

    MESSAGE "Finished adding SQL DBA user SYSPROGRESS.".

    PROCEDURE createSQLDBA:

    /* Create a user named sysprogress with SQL DBA privs */

       CREATE _User.

       ASSIGN

           _User._Userid       = "sysprogress"

           _User._Password     = encode("xxx")

           _User._Domain-Name  = ""

           _User._User-Name    = "sysprogress"

           _User._Description  = "Password = xxx"

           .

    END PROCEDURE. /* createSQLDBA */

    ------------------------------------

    --Created a database

    prodb testdb sports2000

    --Started the database

    proserve testdb -S 2111

    --Ran the procedure file that creates the sysprogress user

    ${PROEXE} -b -i -p ./addsqldba.p testdb > addsqdba.out

    --SQLEXP connection succeeded

    sqlexp -char  -db testdb -S 2111 -user sysprogress -password xxx

    OpenEdge Release 11.6ALPHA as of Tue Mar 17 00:59:14 EDT 2015

    Connecting user "sysprogress" to URL "jdbc:datadirect:openedge://localhost:2111;databaseName=testdb"... (8920)

    SQLExplorer>

    Please try creating the sysprogress user the procedure file above. we can change the password in the .p file accordingly.

  • What is the difference between creating sysprogress through the Data Dictionary and this program?

  • I tried creating sysprogress user from Data Dictionary and tried connecting. The connection succeeded.

  • I believe you :-)   the way that you suggest is the easiest case, but it has nothing to do with my problem.

    I have it works for another machine with the same version OpenEgde, but it does not work only for that machine. At the same time it's very strange situation, because another user, such as USER1, connected in the same place without any problems through SQL Explorer. And I suspect that there is some problem with the OpenEdge installation, or there is some setting that disables connect to the database with SYSPROGRESS user through SQL Explorer, or there is some other problem at the level of the operating system. But I don't know how to find this out.

  • Are there any errors in database log file?

  • I am unable to reproduce in later versions of Progress. The first thing I would suggest is to make sure the -user is case sensitive. I know it should not make a difference but who knows with 10.1B. The second thing I would try is SQuirreL since this is a JDBC tool like sqlexp.  The third thing I would try is to use the -driverURL syntax rather than the easy syntax like in your example. And finally I would see if adding -Djvmstart.debug=1 -DLogLevel=5 in sqlexp.bat provided any additional information.

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • username is NOT case sensitive in 10.1B as well

  • Hi Paul,

    Paul Koufalis

    The first thing I would suggest is to make sure the -user is case sensitive. I know it should not make a difference but who knows with 10.1B.

    Not case sensitive, the result the same.

    Paul Koufalis

    The second thing I would try is SQuirreL since this is a JDBC tool like sqlexp.

    Downloaded, but did not understand how to configure it.

    Paul Koufalis

     The third thing I would try is to use the -driverURL syntax rather than the easy syntax like in your example.

    proenv> sqlexp -user sysprogress -password P@ssw0rd –url jdbc:datadirect:openedge://localhost:6789;databaseName=sports2000
    OpenEdge Release 10.1B as of Wed Jan 10 12:21:31 EST 2007
    Invalid arguments specified: jdbc:datadirect:openedge://localhost:6789
    For additional help, type "sqlexp -help".

    What I do wrong?

    Paul Koufalis

    And finally I would see if adding -Djvmstart.debug=1 -DLogLevel=5 in sqlexp.bat provided any additional information.

       %JVMSTRT% -a "%TOOLSPROP%"::%TOOLSGRP% -o stderr -s -m silent "%JVM%" -DInstall.Dir="%DLC%" Djvmstart.debug=1 -DLogLevel=5 %PROSQLJVMARGS% %SQLEXPCLASS% %PARMS%
    Right?
  • Dileep Dasa

    Are there any errors in database log file?

    There are no suspicious messages in database log file
  • With SQuirrel, you need to configure a new driver type and add a few JAR files to the CLASSPATH of that new driver you defined. Then you pick the com.ddtek.openedge.OpenEdgeDriver:

    The cmd line syntax in 10.2B is with -driverUrl:

    sqlexp -driverUrl "jdbc:datadirect:openedge://localhost:5000;databaseName=sports;User=sysprogress;Password=whatever"

    Your JVMSTART lines looks about right but I did not try it.

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • Oh - in 10.1B I think that there were more JARs required in the CLASSPATH. Check the KB: there was progress.jar, util.jar, tools.jar...

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • Hi Valeriy,

    I noticed that the password contains special character '@' and this is what is causing the problem. I can remember that we have fixed this issue in later versions of OE.

    Can you try changing the password of sysprogress user such that it does not include '@'  and then try connecting through sqlexp?

    Thanks,

    Akthar.

  • Hi Akthar,

    It helped :) Thank you very much!

    Many thanks to all for your help!

    Ragards,

    Valeriy

  • Glad that you found answer. But, you said that it worked on another machine. Just curious to know if you have different password on that machine