topic Re: Can you use arcpy to get the database connection type? in Python Questions

Can you use arcpy to get the database connection type?

AronBird — Thu, 26 Mar 2015 20:22:37 GMT

Anyone know if you can use arcpy to get the database connection type? i.e. Oracle, Application Server, SQL Server, etc.

I'm trying to find a way to identify all of the Application Server connection files in a series of folders so that I can change them to Oracle Direct Connections. To further clarify, if you look at the connection properties for a Database Connection in ArcCatalog you will see this in the Database Platform combobox.

Aron

Re: Can you use arcpy to get the database connection type?

BlakeTerhune — Thu, 26 Mar 2015 20:34:57 GMT

ListUsers() has the IsDirectConnection property. I think If it's not direct connect, then it's application server.

Re: Can you use arcpy to get the database connection type?

JoshuaBixby — Sat, 11 Dec 2021 19:16:01 GMT

ListUsers() is focused on existing/current connections to an SDE database, not SDE connection files on disk, which is what I think the OP is after. I believe the connectionProperties of the Workspace properties of the Describe object will allow the OP to get the information.

>>> desc = arcpy.Describe(r'Database Connections\LGDB.sde')
>>> cp = desc.connectionProperties
>>> cp.instance
u'sde:sqlserver:(localdb)\\MSSQLLocalDB'

For an application server, the instance property will look something like: u'5152:SDE'

Re: Can you use arcpy to get the database connection type?

AronBird — Thu, 26 Mar 2015 22:10:44 GMT

Thanks Blake and Joshua.

Yes, I am trying to find the SDE connection files on disk and not those connected to the database. This does work but unfortunately you have to enter a username and password otherwise it will fail. It would be nice to return a 'Database Platform' value like the screenshot below rather than inferring it is an Application Server connection from the returned instance service number and it would also be nice to not have to connect to the database but either way, I think I can make it work for what I need it to do.

Thanks,

Aron

Re: Can you use arcpy to get the database connection type?

JoshuaBixby — Mon, 30 Mar 2015 18:09:44 GMT

Since Application Servers are dead at ArcGIS 10.3, I don't think Esri is going to invest anything in making it easier to identify them. The "Database Platform" field you reference is relatively new-ish, starting at ArcGIS 10.1. Although the GUI shows this new field, I don't believe there were any changes with the workspace connection properties data structure on the backend. In the end, returning a Database Platform value would have to be from a function that processes the connection properties and not directly from the connection properties themselves.

You raise an interesting, and annoying, part of processing SDE connection files. I primarily work with operating system authentication, so the issue of having the username/password prompt doesn't usually impact me. That said, it is frustrating that one has to fully authenticate for ArcPy to see any of the connection properties. When working with the GUI, a user can look at several connection properties without having to actually connect to the database. It would be nice to see similar behavior in ArcPy if no username or password are given.

Re: Can you use arcpy to get the database connection type?

JoshuaBixby — Sat, 11 Dec 2021 19:16:04 GMT

If you are willing to install comtypes, then the SDE connection properties can be extracted without actually connecting to the database. I ginned up the following function, just pass it the SDE connection file and it returns a dictionary with all of the connection properties.

def GetConnectionPropertiesFromFile(in_workspace):
    from comtypes.client import CreateObject, GetModule
    import os
    
    comDirectory = os.path.join(
        os.path.join(arcpy.GetInstallInfo()['InstallDir']), 'com'
    )
    esriDataSourcesGDB = GetModule(os.path.join(comDirectory, 'esriDataSourcesGDB.olb'))
    esriGeoDatabase = GetModule(os.path.join(comDirectory, 'esriGeodatabase.olb'))
    
    pWSF = CreateObject(esriDataSourcesGDB.SdeWorkspaceFactory,
                        interface=esriGeoDatabase.IWorkspaceFactory)
    pPropSet = pWSF.ReadConnectionPropertiesFromFile(in_workspace)
    names, values = pPropSet.GetAllProperties()
    
    connectionProperties = {}
    for i in range(len(names)):
        connectionProperties[names] = values
        
    return connectionProperties

Re: Can you use arcpy to get the database connection type?

nadja — Thu, 18 Jul 2024 09:27:07 GMT

We tried this syntax a couple month ago with an .sde-file and it worked. Now I always get the error "DescribeData: Method connectionProperties does not exist". What are possible reasons for this?