UDL wizard

[Michael Mattias]

it is possible to control the contents of the Provider and Connection tabs (including the 'Allow Saving Password' checkbox) by storing a pre-configured connection string in the connection object prior to calling the udl as follows...

That works, but it IS 'picky'.

What I wanted to do was set ONLY the "Persist Security Info" thing, but that works ONLY if you include at least one of the 'security' parameters - User ID or Password - which then get put into the Wizard's UID/PW controls, where I wanted blanks.

It also gets funny if you use the 'test connection' thing if you supply a slightly different provider name... eg I supplied "ORAOLEDB.Oracle", which works just fine, except when you do the "wizard selection" it uses "ORAOLEDB.Oracle.1" and then on return it tells you the connection failed because the specified provider was not the same as that which is already in use. (No problem if you don't do the connection test, or if you don't supply ANY connection string).

In any event, I think this will help me deal with my situtation snce I only have to support Oracle 8i or 10g. The 8i Client uses the MS provider (MSDAORA), but the 10g client installs the ORAOLEDB.ORACLE provider. MSDAORA is not officially supported beyond 8i, but it works OK with 10g anyway.

The fun part comes when the user upgrades the Oracle database on the server (8i==>10g) , but does not install a new client on all the user terminals. Everything still works, but it shoots my 'selection criteria' all to hell.

Oh, well, I can make this work with this.

MCM
(Kind of a fun thread. huh?)

[Michael Mattias]

it is possible to control the contents of the Provider and Connection tabs (including the 'Allow Saving Password' checkbox) by storing a pre-configured connection string in the connection object prior to calling the udl as follows

I shall give that a go.

FWIW, I reported the bug in the COM browser (whatever is supplied with PB/CC 5.0.1.) which results in a forward reference (resulting in "undefined type" compile-time error) when creating that "msdasc.inc" file from the "Microsoft OLE DB Service Component 1.0 Type Library"

All you have to do to get the above to work is copy these items into the main code and eschew the #INCLUDE of msdasc.inc.

' for reference:
' Generated by: PowerBASIC COM Browser v.2.00.0070
' Date & Time : 6/19/2009 at 3:45 PM
' ------------------------------------------------
' Library Name: MSDASC
' Library File: C:\Program Files\Common Files\System\Ole DB\oledb32.dll
' Description : Microsoft OLE DB Service Component 1.0 Type Library
' GUID : {2206CEB0-19C1-11D1-89E0-00C04FD7A829}
' LCID : 0
' Version : 1.0


$PROGID_MSDASC_DataLinks = "DataLinks"
$IID_MSDASC_IDataSourceLocator = GUID$("{2206CCB2-19C1-11D1-89E0-00C04FD7A829}")

' Interface Name  : IDataSourceLocator
' Description     : Microsoft OLE DB Service Component Automation Interface
' Class Name      : DataLinks
' ClassID         : $CLSID_MSDASC_DataLinks
' ProgID          : $PROGID_MSDASC_DataLinks
' Version ProgID  : $PROGID_MSDASC_DataLinks
INTERFACE IDataSourceLocator $IID_MSDASC_IDataSourceLocator
    INHERIT IDISPATCH

    PROPERTY GET hWnd <1610743808> () AS LONG
    PROPERTY SET hWnd <1610743808> (BYVAL phwndParent AS LONG)
    METHOD PromptNew <1610743810> () AS IDISPATCH
    METHOD PromptEdit <1610743811> (BYREF IN ppADOConnection AS IDISPATCH) AS INTEGER
END INTERFACE

MCM

[David Warner]

Hi Michael,

it is possible to control the contents of the Provider and Connection tabs (including the 'Allow Saving Password' checkbox) by storing a pre-configured connection string in the connection object prior to calling the udl as follows...

#INCLUDE "Win32api.inc"
#INCLUDE "ADODB28DISP.INC"
#INCLUDE "msdasc.inc"

FUNCTION PBMAIN () AS LONG
LOCAL oDataLinks AS IDataSourceLocator
LOCAL oConn AS INT__Connection
LOCAL i_oConn AS INTEGER

LOCAL v_ConnectionString AS VARIANT
LOCAL s_ConnectionString AS STRING

    LET oConn = NEWCOM $PROGID_ADODB_Connection
    LET oDataLinks = NEWCOM $PROGID_MSDASC_DataLinks

    'Pre-set values for 'Default Provider', 'Data Source' and 'Allow Saving Password' checkbox in the connection object...

    'Microsoft Access example...
    's_ConnectionString = UCODE$("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.mdb;Persist Security Info=True")

    'SQL Server example...
    's_ConnectionString = UCODE$("Provider=SQLOLEDB.1;Password=TestPassword;Persist Security Info=True;User ID=TestUser;Initial Catalog=TESTDB;Data Source=TESTSERVERPC\SQLSERVER")

    'Oracle example...
    s_ConnectionString = UCODE$("Provider=MSDAORA.1;Password=TestPassword;User ID=TestUser;Data Source=TESTORACLESERVER;Persist Security Info=True;")

    OBJECT LET oConn.ConnectionString = s_ConnectionString

    CALL oDataLinks.PromptEdit(oConn) TO i_oConn

    'If the call returns a valid Connection object, then retrieve the ConnectionString property of the Connection object.
    IF ISOBJECT(oConn) _
       AND ISOBJECT(oDataLinks) _
       AND i_oConn <> 0 _
       AND ISFALSE ERR THEN

        PRINT "Connection object is valid"
        PRINT ""

        'Retrieve the ConnectionString value from the connection object
        OBJECT GET oConn.ConnectionString TO v_ConnectionString
        s_ConnectionString = VARIANT$(v_ConnectionString)

        PRINT "Connection string..."
        PRINT ""
        PRINT s_ConnectionString
        PRINT ""

        PRINT "Open the connection (Optional)"
        PRINT ""
        OBJECT CALL oConn.Open

        PRINT "Close the connection"
        PRINT ""
        OBJECT CALL oConn.Close

        SET oConn = NOTHING
        SET oDataLinks = NOTHING

        PRINT "PRESS ANY KEY TO CONTINUE"
        WAITKEY$

    END IF

END FUNCTION

Regards,

David

[Michael Mattias]

Very handy.
TIP:

If you want the password back as part of your connection string, you have to check the "Allow Saving Password" box on the "Connection" tab... OR....
set the "Persist Security Info" value to TRUE on the "All" tab.

In any event, you could test the returned string for the presence of a "Password=" token and if not found, send the user back to the wizard with instructions to check the box this time.

(I will want this behavior in at least one Oracle application).

I "think" I should be able to set that property in the Connection Object before calling the wizard, but right now if I even list the properties collection of the 'new' connection object, when I call the wizard it hides the "Provider" tab... which I need to find the Oracle provider. When it comes back it's using the default Microsoft Provider for ODBC, which don't work too good against my Oracle DB.

I found some blog entries for this wizard function and some of those authors also noted that you can alter the appearance of the wizard depending on the order in which you set things. (Except I did not "SET" anything, I just listed the properties:

FUNCTION GetConnectionProperties (oConn AS Int__Connection) AS LONG
' there may be an enum for this but I will just do it the old fashioned way
' How did I do the errors thing with a collection?  don't need any objects, just an index

 LOCAL vNProp AS VARIANT, vIProp AS VARIANT
 LOCAL nProp AS LONG, iProp AS LONG
 LOCAL vPropName AS VARIANT, vPropValue AS VARIANT
 LOCAL oProp     AS Int_Property, vProp AS VARIANT



        OBJECT GET   oconn.Properties.Count  TO vnProp
        nProp      = VARIANT#(vnProp)

        PRINT USING$ ("Found # properties in unopened connection", nProp)
        ' it found 14 but did not get any names or values when unopened.

        IF nProp THEN

             FOR iProp   =  0 TO nProp-1

                LET    vIProp =  iProp
                OBJECT GET  oConn.Properties.Item(ViProp).NAME  TO vPropName
                OBJECT GET  oConn.Properties.Item(ViProp).Value TO vPropValue
               
               ' hmm, do I have to get the TYPE of vPropvalue? Or are they strings?

                PRINT USING$ ("Property index #  Name '&'  Value as number # value as string '&'", _
                       iProp, VARIANT$(vPropName), VARIANT#(VPropValue), VARIANT$(vPropValue))
                ' I wonder if thise might not all be strings?
                ' no they are not.
                
                 
            NEXT iProp

        ELSE
             PRINT "Count (properties) is ZERO"
        END IF


END FUNCTION

MCM

[David Warner]

Hi Erich,

try this...

#INCLUDE "Win32api.inc"
#INCLUDE "ADODB28DISP.INC"
#INCLUDE "msdasc.inc"

FUNCTION PBMAIN () AS LONG
LOCAL oDataLinks AS IDataSourceLocator
LOCAL oConn AS INT__Connection
LOCAL i_oConn AS INTEGER

LOCAL v_ConnectionString AS VARIANT
LOCAL s_ConnectionString AS STRING

    LET oConn = NEWCOM $PROGID_ADODB_Connection
    LET oDataLinks = NEWCOM $PROGID_MSDASC_DataLinks

    CALL oDataLinks.PromptEdit(oConn) TO i_oConn

    'If the call returns a valid Connection object, then retrieve the ConnectionString property of the Connection object.
    IF ISOBJECT(oConn) _
       AND ISOBJECT(oDataLinks) _
       AND i_oConn <> 0 _
       AND ISFALSE ERR THEN

        PRINT "Connection object is valid"
        PRINT ""

        'Retrieve the ConnectionString value from the connection object
        OBJECT GET oConn.ConnectionString TO v_ConnectionString
        s_ConnectionString = VARIANT$(v_ConnectionString)

        PRINT "Connection string..."
        PRINT ""
        PRINT s_ConnectionString
        PRINT ""

        PRINT "Open the connection (Optional)"
        PRINT ""
        OBJECT CALL oConn.Open

        PRINT "Close the connection"
        PRINT ""
        OBJECT CALL oConn.Close

        SET oConn = NOTHING
        SET oDataLinks = NOTHING

        PRINT "PRESS ANY KEY TO CONTINUE"
        WAITKEY$

    END IF

END FUNCTION

Regards,

David

[José Roca]

Still not clear to me is how I can capture the connection string.

If the call returns a valid Connection object, then call the ConnectionString property of the Connection object.

[Erich Schulman]

Here is what I have so far.

#INCLUDE "Win32api.inc"
#INCLUDE "ADODB28DISP.INC"
#INCLUDE "msdasc.inc"

FUNCTION PBMAIN () AS LONG
LOCAL oDataLinks AS IDataSourceLocator
LOCAL oConn AS INT__Connection
LOCAL i_oConn AS INTEGER
    LET oConn = NEWCOM $PROGID_ADODB_Connection
    LET oDataLinks = NEWCOM $PROGID_MSDASC_DataLinks

    CALL oDataLinks.PromptEdit(oConn) TO i_oConn
    OBJECT CALL oConn.Open

END FUNCTION

The wizard does open. I selected a MS Access file, and I got no runtime error from the OBJECT CALL. Since I did no SELECTing, I am aware that the database connection may or may not have really been made. I just wanted to get this far first.

Still not clear to me is how I can capture the connection string. If I have it and stop by the PBCrypto site, I can offer the user a secure cache of the most recently used databases.

[David Warner]

Hi Erich,

possible to do something like
MyConnStr = UDLwiz

I found a reference to Microsoft Data Links deep in some old VB info which I have been hanging on to for some reason.

http://doc.ddart.net/mssql/sql2000/h...ewfeatures.htm

MDAC Technical Articles
New Features in ADO, ADOX, and JRO

It is also possible to use Microsoft Data Links to prompt the user for connection information. The following code demonstrates how to launch the Microsoft Data Links UI from code. In order to run this code, you'll need to add a reference to Microsoft OLE DB Service Component 1.0 Type Library in your project.

Private Sub Command1_Click()

   Dim cnn     As New ADODB.Connection
   Dim dl      As New DataLinks

   On Error Resume Next

   dl.hWnd = Me.hWnd
   If dl.PromptEdit(cnn) Then
      cnn.Open
   End If

   cnn.Close

End Sub

This might get you going on PB'fying what you want.

Regards,

David

[Erich Schulman]

Ideally, yes, if it is possible to do something like
MyConnStr = UDLwiz

I have not found any information on how to call the wizard, let alone get results back therefrom. I thought someone here would have used it before, alleviating the need to plough through the OpenOffice.org source code.

[Trevor Lane]

Eric,

Do you want to call this like a dialog "shell" to it. And then get the connection string back?

[Michael Mattias]

Connecting via ODBC? ADO?

In either case, there are options to supply part of the connection string, and if the driver can connect with only that it does so; and if not, can prompt the user for any information it needs but was not supplied.

And again in either case, once a connection is established, you can get the complete connection string used for that connection simply by asking the software.

[Erich Schulman]

I want to be able to get at that wizard and receive a full connection string back based on whatever database the user chooses.

[Trevor Lane]

Create a new text file on the desktop called 'test.txt'

Change the extension to '.udl'

Double click and set all of the properties. Click 'Test connection' All is well if the test suceeded.

Close the wizard and open the file again in Notepad.

The file contains the connection string.

Is that what you are after?

[Erich Schulman]

At Connecting to Microsoft Access:

If you don't know the connection string, click on Browse. This opens the “ Universal Data Link (UDL) Wizard” of Windows. You see a dialog with the title Data Link Properties. The dialog pages Provider, Connections, Advanced, and All can be used in any order. When you close the dialog, the field Data source URL contains the constructed connection string.

Has anyone made any examples of using this wizard within a PB-made application?