insert into sql table from geoprocessing service

DorothyMortenson — Wed, 15 Oct 2014 18:01:18 GMT

Hello.

I have a geoprocessing service that kicks off a python script.

In that python script:

make a map
take the map as a png file and insert it into a sql table

Python script works fine on the server.

Python script works fine as a custom ArcTool.

So I create a geoprocessing service. It fails.

I have stripped the code down to just the insert. It appears I can insert into the sql table as long as I do not include the map. but if I do, it does not work in the geoprocessing service. I thought it might be a pathname issue, so I tried putting the png in the same directory as the script.

Here is the code:

=============================================================================

import pypyodbc

rec_creation_userid = '1234'

wl_id = 99

mime_type = 'image/png'

map_file_name = '99.png'

# tried with pathname - does NOT work in gp service

well_location_map = 'C:\\pathname\\maps\\99.png'

# tried with null- does work in gp service

well_location_map= ""

# tried with no pathname - does NOT work in gp service

well_location_map = '99.png'

parameters = rec_creation_userid, wl_id, mime_type, map_file_name, well_location_map

cnxn = None;

cnxn = pypyodbc.connect('DRIVER={SQL Server Native Client 10.0};SERVER=myserver\\AAAA;DATABASE=database;UID=username;PWD=password')

cursor = cnxn.cursor()

#==== uses a stored procedure for the insert=======

sp = "set nocount on ; "

sp = sp + "DECLARE @return_value int; "

sp = sp + "DECLARE @new_serial_id int; "

sp = sp + "DECLARE @return_msg varchar(250); "

sp = sp + "exec @return_value = dbo.wrd_wl_pend_map_attachment_insert @new_serial_id OUTPUT, \

@return_msg OUTPUT, \

@current_userid = ?, \

@wl_id = ?, \

@mime_type = ?, \

@map_file_name = ? , \

@map_attachment = ? "

sp = sp + "select @return_value as return_value, @new_serial_id as new_serial_id, @return_msg as return_msg ; "

cursor.execute(sp, parameters)

row = cursor.fetchone()

if (row[0] > 0):

logfile.write("There was an error: " + str(row[0]) + "\n\n")

print row

cursor.close()

cnxn.commit()

cnxn.close()

topic insert into sql table from geoprocessing service in ArcGIS Enterprise Questions

insert into sql table from geoprocessing service