Announcement

Collapse
No announcement yet.

UDL wizard

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Michael Mattias
    replied
    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?)

    Leave a comment:


  • Michael Mattias
    replied
    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.

    Code:
    ' 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

    Leave a comment:


  • David Warner
    replied
    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...


    Code:
    #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
    Last edited by David Warner; 19 Jun 2009, 07:35 PM.

    Leave a comment:


  • Michael Mattias
    replied
    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:

    Code:
    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
    Last edited by Michael Mattias; 19 Jun 2009, 05:38 PM.

    Leave a comment:


  • David Warner
    replied
    Hi Erich,

    try this...

    Code:
    #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
    Last edited by David Warner; 19 Jun 2009, 07:32 PM.

    Leave a comment:


  • José Roca
    replied
    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.

    Leave a comment:


  • Erich Schulman
    replied
    Here is what I have so far.

    Code:
    #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.

    Leave a comment:


  • David Warner
    replied
    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.
    Code:
    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

    Leave a comment:


  • Erich Schulman
    replied
    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.

    Leave a comment:


  • Trevor Lane
    replied
    Eric,

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

    Leave a comment:


  • Michael Mattias
    replied
    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.

    Leave a comment:


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

    Leave a comment:


  • Trevor Lane
    replied
    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?

    Leave a comment:


  • Erich Schulman
    started a topic UDL wizard

    UDL wizard

    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?
Working...
X