Python Script uses Ole Db Connection does not work on production server....

AnastasiaAourik · ‎02-20-2012

I have python scripts that read oracle table and recreates spatial featureclass (sde is on sql server) each night...
My connection to oracle table 'must be' ole db connection since I have no sde on oracle.
First, I noticed my script does not work when deployed to a production server
windows 2003 x64 -
I saw oracle client installed on this machine, by someone else, before I got here.

sql plus works fine to connect, even oracle enterprise manager works fine,
but in ArccATALOG try to set up new old db connection and I can't set this up.

Would like to confirm that I can use oracle client 32 bit on windows 2003 64 bit machine
and would also like to confirm my assumption that since ArcCatalog/ArcMap.exe are 32-bit apps
I MUST use oracle client 32 bit...

Here's my python script...
# ---------------------------------------------------------------------------
# makeWellsGeneral.py
# Created on: 2011-09-29 11:35:46.00000
#   (generated by ArcGIS/ModelBuilder)
# Description:
# Make Event Layer from ORACLE WCSS.BITREC_MV
# ---------------------------------------------------------------------------

# Import arcpy module
import arcpy

# Local variables:
WCSS_BITREC_MV = "Database Connections\\OLE DB ConnectionMYETAK.odc\\WCSS.BITREC_MV"
WCSS_Layer = "WCSS_Layer"
siiview_SIIGIS_WG_BITREC = "Database Connections\\Connection to sql04.sde\\siiview.SIIGIS.WELLSGENERAL\\siiview.SIIGIS.WG_BITREC"

try:

    if arcpy.Exists(WCSS_BITREC_MV):
# Process: Make XY Event Layer
arcpy.AddMessage("WCSS.BITREC Materialized View Exists so Proceed")
arcpy.MakeXYEventLayer_management(WCSS_BITREC_MV, "longitude", "latitude", WCSS_Layer, "GEOGCS['GCS_WGS_1984',DATUM['D_WGS_1984',SPHEROID['WGS_1984',6378137.0,298.257223563]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]];-400 -400 1000000000;-100000 10000;-100000 10000;8.98315284119522E-09;0.001;0.001;IsHighPrecision", "")
arcpy.AddMessage("Done MakeXY Event Layer")
if arcpy.Exists(WCSS_Layer):
arcpy.AddMessage("Internal Scratch Layer Exists")
# Process: Copy Features
if arcpy.Exists(siiview_SIIGIS_WG_BITREC):
   arcpy.AddMessage("Delete Previous GIS FeatureClass")
   arcpy.Delete_management(siiview_SIIGIS_WG_BITREC)
   arcpy.AddMessage("Done Delete")
arcpy.CopyFeatures_management(WCSS_Layer, siiview_SIIGIS_WG_BITREC, "", "0", "0", "0")
arcpy.AddMessage("All Features Copied Done")

except:
   arcpy.AddError("Python Messages: " + arcpy.GetMessages())

I welcome feedback.

AnastasiaAourik · ‎03-21-2012

RESOLUTION...

I)   Do not use ArcCatalog relative path "Database Connections\\OLE DB ConnectionMYETAK.odc\\WCSS.BITREC_MV".
     You must explicitly specify the drive letter and the full path name and make sure that the Run As owner can access that folder.
     I have chosen to copy my ArcCatalog data connections folder to another location for use by my Python Scripts just to be on the safe side.

II) Test your connection thoroughly in Python via arcpy.Exists before you proceed.
      Something like this will suffice:
# Import arcpy module
import arcpy, os, datetime
arcpy.env.overwriteOutput = True

# Local variables:
WCSS_SOURCE = "D:/SpatialAdapters/ArcCatalog/OLE DB ConnectionPYTHON.odc/WCSS.POMD_JOBREC_MV"
WCSS_Layer = "WCSS_Layer"
SIIGIS_WG_LAYER = "d:/SpatialAdapters/ArcCatalog/Connection to sql04.sde/siiview.SIIGIS.WELLSGENERAL/siiview.SIIGIS.WG_POMDJOBS"
now = datetime.datetime.now()

print "GisBuild of PATHFINDER JOBS - testing with new python connection that has blanks in odc file name"
print "Started: " + str(now)

try:

    if arcpy.Exists(SIIGIS_WG_LAYER):
print "we found sde featureclass"
    else:
print "we did not find SDE FEATURECLASS"

    if arcpy.Exists(WCSS_SOURCE):
print "we found the ole db connection source file"
    else:
print "we did NOT find ole db connection of source file"

except:
   arcpy.AddError("Python Messages: " + arcpy.GetMessages())

ChrisBeaudette · ‎10-22-2012

I am having a very similar but subtly different issue: I have an OLE DB connection to an Oracle 11g database using ArcGIS v10 SP2 and python v2.6. I can connect to the database and when I set my arcpy.env.workspace and list the tables, I can see all tables in the entire database. But arcpy.Exists(table) works for some tables and not others. And of course for the one table I'm interested in, it doesn't work. The table I'm interested in is in the schema for the user that the OLE DB connection uses ("survey").

Here's the code:

import sys, string, arcpy
from arcpy import env
import traceback

arcpy.env.overwriteOutput = True

arcpy.env.workspace = "C:/Temp/My_DB_Connection.odc"

tableList = arcpy.ListTables()
for table in tableList:
    print table  # <-- WORKS


if not arcpy.Exists("C:/Temp/My_DB_Connection.odc/SURVEY.CONTROL"):
    print "table does NOT exist"
else:
    print "Oracle table exists"

Prints "table does NOT exist".

Also note that when I connect to the DB in ArcCatalog using the same exact connection file, I can see all tables in the DB (not just SURVEY tables, but I do see them all).

JamesCrandall · ‎10-23-2012

I am having a very similar but subtly different issue: I have an OLE DB connection to an Oracle 11g database using ArcGIS v10 SP2 and python v2.6. I can connect to the database and when I set my arcpy.env.workspace and list the tables, I can see all tables in the entire database. But arcpy.Exists(table) works for some tables and not others. And of course for the one table I'm interested in, it doesn't work. The table I'm interested in is in the schema for the user that the OLE DB connection uses ("survey").

Here's the code:
import sys, string, arcpy
from arcpy import env
import traceback

arcpy.env.overwriteOutput = True

arcpy.env.workspace = "C:/Temp/My_DB_Connection.odc"

tableList = arcpy.ListTables()
for table in tableList:
    print table  # <-- WORKS


if not arcpy.Exists("C:/Temp/My_DB_Connection.odc/SURVEY.CONTROL"):
    print "table does NOT exist"
else:
    print "Oracle table exists"
Prints "table does NOT exist".

Also note that when I connect to the DB in ArcCatalog using the same exact connection file, I can see all tables in the DB (not just SURVEY tables, but I do see them all).

Perhaps it is a permissions issue for those tables? Not sure exactly if that is your problem, but you may want to check with the Oracle DBA the permissions for the login you are using and the tables you are expecting to see.

I get weird results too (ArcGIS 9.3.1) when simply adding an OLE DB conn to an 11g Ora db: some tables/views can be dropped into ArcMap, others cannot (even though I can see them in ArcCatalog). To get around this issue I opted to nix the OleDb connection stuff and pass in SQL statements directly into the database and return just the results I want via cx_Oracle library. Of course this now means I have to go thru the effort to convert the result into something that is ArcGIS friendly (a fairly simple, but limited, field mapping and conversion to an IN_MEMORY table).

ChrisBeaudette · ‎10-23-2012

Thanks for the reply James.

I don't think it's user permissions since I'm able to select from the problem table in SQLDeveloper. If I view the GRANTS in SQLDeveloper for a problem table and one that I'm able to see in arcpy, there is no difference in the grants. (There actually aren't any at all for either table, but that could be not because they're not there, but because of what my login ID has permission to view).

I just downloaded cx_Oracle and may take the same tack. If you have any code to share, I'd be most grateful.

JamesCrandall · ‎10-24-2012

Thanks for the reply James.

I don't think it's user permissions since I'm able to select from the problem table in SQLDeveloper. If I view the GRANTS in SQLDeveloper for a problem table and one that I'm able to see in arcpy, there is no difference in the grants. (There actually aren't any at all for either table, but that could be not because they're not there, but because of what my login ID has permission to view).

I just downloaded cx_Oracle and may take the same tack. If you have any code to share, I'd be most grateful.

Sorry for the late reply. Here is some code (THIS IS FOR arcgisscripting(9.3)!!!!) snippet from a larger codebase I have implemented. It may not work if directly plopped into your codebase, you'll have to go thru it carefully --- also, if you have additional fields to map, you will have to add them (this ex only shows string, float and datetime conversion from Ora to ArcGIS).

But it is fairly straight forward. (not sure how well this gets formatted as a forum posting, so you might have to clean things up a bit!)

Lots of examples out there too, it's not necessarily a "GIS" thing! Here's the reference base:

http://cx-oracle.sourceforge.net/html/module.html

        ### create the temporary table
 gp.createtable_management("IN_MEMORY", "TempDT", "", "")

 ### Build a DSN (can be subsitited for a TNS name)     
 dsn = cx_Oracle.makedsn("servername", service_name, "database")     
 oradb = cx_Oracle.connect("user", "password", dsn)     
 cursor = oradb.cursor()   

        ### Build the SQL statement and execute it on the cursor object
 cursor.execute("""SELECT field1, field2 
       FROM table1
      WHERE table1.field1 = somevalue""")
        
        
        ### locate the temporary table we just created and use the results 
        ### of the cursor to contsruct it's structure
 
 tables = gp.ListTables()
 for tbl in tables:   
    if tbl=="TempDT":
        
       for i in range(0, len(cursor.description)):
  
  val1 = str(cursor.description[0])
  val2 = str(cursor.description[1])
  val3 = str(cursor.description[2])
 
  if val2=="<type 'cx_Oracle.STRING'>":
     fldType = "Text"
     val3 = cursor.description[2]
     gp.AddField(tbl, str(cursor.description[0]), fldType, val3)
  
  if val2=="<type 'cx_Oracle.NATIVE_FLOAT'>":
     fldType = "Float"
     gp.AddField(tbl, str(cursor.description[0]), fldType)
  
  if val2=="<type 'cx_Oracle.DATETIME'>":
     fldType = "Date"
     gp.AddField(tbl, str(cursor.description[0]), fldType)
     

        ### populate the in_memory table we just constructed from the cx_Oracle cursor
 tables = gp.ListTables()
 for tblNew in tables:
   if tblNew=="TempDT":      
 
    ### now populate the table
    insRows = gp.InsertCursor(tblNew)
    cxRows = cursor.fetchall()
    for cxRow in cxRows:
       insRow = insRows.newRow()
 
       for i in range(0, len(cursor.description)):
   insRow.setvalue(str(cursor.description[0]), cxRow)
                
       insRows.insertRow(insRow)
 
    
 cursor.close() 
 oradb.close()

AnastasiaAourik · ‎03-21-2016

Hey world!

So my scripts have been tried-and-true for years until

https://support.microsoft.com/en-us/kb/3126587

was installed on my production server.

I re-installed Oracle client, I am able to run the script but now at the first line of my script that calls

WCSS_SOURCE = "D:/SpatialAdapters/ArcCatalog/OLE DB ConnectionWCSS.odc/WCSS.DMJOB_EOWR_MV"

try:
    print "now let us do the arcpy.exist function call"
    if arcpy.Exists(WCSS_SOURCE):
            print "Source Table Exists"
    now = datetime.datetime.now()
    print "Ended: " + str(now) + "\n"

except:
arcpy.AddError("Python Messages: " + arcpy.GetMessages())

I get these messages:

D:\SpatialAdapters\DMJob Attachments>python simple.py

GisBuild of DMJOB_EOWR

Started: 2016-03-21 12:51:30.914000

now let us do the arcpy.exist function call

LoadLibraryFromPath: FQ Dll C:\Windows\system\oci.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll C:\Windows\oci.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll C:\PROGRAM FILES (X86)\ARCGIS\DESKTOP10.2\BIN\oci.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll D:\app\wellsgis-a\product\11.1.0\client_1\BIN\bin\oci.dll found

LoadLibraryFromPath: FQ Dll C:\Windows\system\SQLLib80.dll is not found, error:0x7e

LoadLibraryFromPath: FQ Dll C:\Windows\SQLLib80.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll C:\PROGRAM FILES (X86)\ARCGIS\DESKTOP10.2\BIN\SQLLib80.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll D:\app\wellsgis-a\product\11.1.0\client_1\BIN\bin\SQLLib80.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll C:\Program Files\HP\NCU\SQLLib80.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll C:\Windows\system32\SQLLib80.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll C:\Windows\SQLLib80.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll C:\Windows\System32\Wbem\SQLLib80.dll is not found,error: 0x7e

LoadLibraryFromPath: FQ Dll C:\Windows\System32\WindowsPowerShell\v1.0\SQLLib80.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll C:\Windows\idmu\common\SQLLib80.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SQLLib80.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\SQLLib80.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLLib80.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\SQLLib80.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll C:\Program Files\Microsoft SQL Server\100\DTS\Binn\SQLLib80.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll d:\Python27\ArcGIS10.2\SQLLib80.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll C:\Program Files\HP\HP BTO Software\lib\SQLLib80.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll C:\Program Files\HP\HP BTO Software\bin\SQLLib80.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll C:\Program Files\HP\HP BTO Software\bin\win64\SQLLib80.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll C:\Program Files\HP\HP BTO Software\bin\win64\OpC\SQLLib80.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll C:\Program Files\OmniBack\bin\SQLLib80.dll is not found, error: 0x7e

LoadLibraryFromPath: FQ Dll C:\Program Files\1E\NomadBranch\SQLLib80.dll is notfound, error: 0x7e

Source Table Exists

Ended: 2016-03-21 12:51:37.154000

MichaelVolz · ‎03-21-2016

Are you using a Windows Server 2003 or Windows server 2008 to run this script?

AnastasiaAourik · ‎03-21-2016

window server 2008 r2 64 bit.

The script has been in place for several years - no issue.

It seems that since that KB was installed and server was restarted, we now see these loadlibrary errors

BUT the script still runs as you can see from the log messages I issue using the python print command.

Plus, the work that needs to get done is being done without issue.

Just do not like to see all these weird msgs

Seems like I see each line for each Path entry in environment variable (not 100% sure though)

Any clues???