Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (5)

Resolved Suppress ODBC Login Screen for Connect to ASE

  1. How-to
0
Votes
Undo
  1. Charlie Cummins
    Charlie Cummins
  2. PowerBuilder
  3. Wednesday, 29 January 2020 17:25 PM UTC

Hi 

I am currently using PowerBuilder 2017 R3 and wanted to connect to our Sybase ASE 16 databases using ODBC.  My applications provide a login page so I wanted to suppress the ODBC login page and use the application's one.  I hope I can use DbParm to handle this.

Thank you, Charlie

 

NativePDF print after regular print uses same page...
Can the flat appearance in the PB development envi...
Accepted Answer
Charlie Cummins
Charlie Cummins Accepted Answer Pending Moderation
  1. Wednesday, 29 January 2020 18:53 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi Michael,

Thanks for your input.  I did see that suggestion from some other posts and tried it.  It might be clearer if I show you my dbparm.

Ideally I don't want to put a user name and password in my app ini file.  So I have been playing around with leaving them out or blank but I get the following error.

Cannot Connect to Database
SQLSTATE = 28000
[SAP][ASE ODBC Driverjlnvalid authorization specification

Examples of dbParms I tried:

DbParm=ConnectString='DSN=sybase_test_adap',ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'

DbParm=ConnectString='DSN=sybase_test_adap;UID=;PWD=',ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'

Thanks for your help, Charlie

 

 

 

 

Comment
Michael Kramer
  1. Michael Kramer
  2. Wednesday, 29 January 2020 20:16 PM UTC
You can build the ConnectString parm of your DBParm in-flight so you add no credential info to INI file.
  1. Helpful
Charlie Cummins
  1. Charlie Cummins
  2. Wednesday, 29 January 2020 20:34 PM UTC
Thanks again, Michael that's where I just started heading towards.
  1. Helpful
There are no comments made yet.
Responses (5)
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Wednesday, 29 January 2020 20:43 PM UTC
  2. PowerBuilder
  3. # 1

Example - Each function encapsulates separate piece that may change for different reason (single responsibility principle).

// Functions on my transaction base class
function string of_GetConnectString(string as_database, string as_userID, string as_password)
   string DSN, UID, PWD
   DSN = "DSN=" + as_database
   UID = "UID=" + as_userID
   PWD = "PWD=" + as_password
   return "ConnectString='" + DSN + ";" + UID + ";" + PWD + "'"

function string of_GetConnectOptions( )
   return "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMT'"

// Logon script -- Logon window
string ls_dbName, ls_user, ls_pwd
ls_dbName = sle_Database.Text
ls_user   = sle_UserID.Text
ls_pwd    = sle_Password.Text
// ---
SQLCA.DBMS = "ODBC"
SQLCA.DBParm = SQLCA.of_GetConnectString(ls_DB, ls_user, ls_pwd)
SQLCA.DBParm += "," + SQLCA.of_GetConnetOptions( )
SQLCA.DBParm += "," + "... more options ..."
CONNECT USING SQLCA;

HTH /Michael

Comment
Charlie Cummins
  1. Charlie Cummins
  2. Wednesday, 29 January 2020 20:47 PM UTC
super, thank you
  1. Helpful
There are no comments made yet.
Charlie Cummins
Charlie Cummins Accepted Answer Pending Moderation
  1. Wednesday, 29 January 2020 20:29 PM UTC
  2. PowerBuilder
  3. # 2

Hi Chris,

Actually I am preparing for a migration from ASE over to MS SQL Server 2017. 

I initially investigated using JDBC to connect to MS SQL Server but it looks like that is restricted to a early JRE.  So I started an initial investigation on what would be involved in utilizing ODBC.  In order to get a feel for what the impact would be on the PB apps, I setup an ODBC connection for the existing Test ASE db.

Thanks for interest, Charlie

 

Comment
Load more comments
Charlie Cummins
  1. Charlie Cummins
  2. Wednesday, 29 January 2020 21:15 PM UTC
thanks guys for the tips, I noticed a previous issue out on the community (can't find it at the moment) about migrating from ASE to SS, that mentioned datawindow embedded SQL, whether it was text for graphic and I believe a "ansi" connection parm option, I'll find it :)
  1. Helpful
Charlie Cummins
  1. Charlie Cummins
  2. Wednesday, 29 January 2020 21:18 PM UTC
https://community.appeon.com/index.php/qna/q-a/migrating-applications-from-ase-to-ms-sql-server
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Thursday, 30 January 2020 19:15 PM UTC
Thanks for posting that link Charlie! Just an FYI though that I was talking about using native DB drivers in that post. Your situation is complicated more because of using "middle-ware" - in this case ODBC. PB wants to efficiently convert a PBSelect into a native ANSI based DML commands for the DBMS its connected to. However, at run-time, it will see that your using ODBC and then generate ODBC SQL format instead. .The ODBC layer then hands the ODBC SQL to its DB driver who then converts the ODBC SQL to T-SQL (in your case). On the way back from the DBMS, PB normally expects a binary result set (in the DBMS's flavour). However, the SS RS, is set back to the ODBC/SS driver, who in turn converts this into an ODBC result set format. The ODBC Manager in turn, hands that to the PB ODBC driver & interface - which in turns converts the result set into a proper ANSI format (which PB is expecting from any DBMS. So in the long & short of it, any "middle-ware" DB connection will never be as DML robust or performance minded as a native driver. Over the decades, I have done many a DBMS performance study to confirm this. Just my $0.02.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 29 January 2020 20:12 PM UTC
  2. PowerBuilder
  3. # 3

Hi Charlie;

   Q: Why would you want to use the cumbersome and slow ODBC connection over to ASE vs PB's fast "native" ASE DB Driver?

IE:  SQLCA.DBMS = "ASE"

Regards ... Chris

Comment
There are no comments made yet.
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Wednesday, 29 January 2020 17:52 PM UTC
  2. PowerBuilder
  3. # 4

This should do the trick for any ODBC source:

SQLCA.DBParm += ",ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'"

Remember the leading comma unless DBParm is empty.

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.