Announcement

Collapse
No announcement yet.

UDL wizard

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

  • 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?
    Erich Schulman (KT4VOL/KTN4CA)
    Go Big Orange

  • #2
    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?

    Comment


    • #3
      I want to be able to get at that wizard and receive a full connection string back based on whatever database the user chooses.
      Erich Schulman (KT4VOL/KTN4CA)
      Go Big Orange

      Comment


      • #4
        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.
        Michael Mattias
        Tal Systems (retired)
        Port Washington WI USA
        [email protected]
        http://www.talsystems.com

        Comment


        • #5
          Eric,

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

          Comment


          • #6
            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.
            Erich Schulman (KT4VOL/KTN4CA)
            Go Big Orange

            Comment


            • #7
              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

              Comment


              • #8
                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.
                Erich Schulman (KT4VOL/KTN4CA)
                Go Big Orange

                Comment


                • #9
                  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.
                  Forum: http://www.jose.it-berater.org/smfforum/index.php

                  Comment


                  • #10
                    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, 08:32 PM.

                    Comment


                    • #11
                      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, 06:38 PM.
                      Michael Mattias
                      Tal Systems (retired)
                      Port Washington WI USA
                      [email protected]
                      http://www.talsystems.com

                      Comment


                      • #12
                        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, 08:35 PM.

                        Comment


                        • #13
                          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
                          Michael Mattias
                          Tal Systems (retired)
                          Port Washington WI USA
                          [email protected]
                          http://www.talsystems.com

                          Comment


                          • #14
                            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
                            Tal Systems (retired)
                            Port Washington WI USA
                            [email protected]
                            http://www.talsystems.com

                            Comment

                            Working...
                            X