AnsweredAssumed Answered

insert into sql table from geoprocessing service

Question asked by dmortenson on Oct 15, 2014

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()

Outcomes