return result from sql statement on geodatabase

418
8
Jump to solution
02-15-2024 07:19 AM
nadja_swiss_parks
Occasional Contributor II

I've got a geodatabase from which I can get my FeatureClassDefinitions - so I know the gdb exists and is accessible. I can also query its content using a python script and a sql-statement - that works too. 

I just can't figure out how to send a sql-statement from within the dockpane to my gdb and get the result.

I tried DatabaseClient.ExecuteStatement(geodatabase, statement); but that doesnot return the result (but also no error). I tried geodatabase.OpenQueryTable() - but I don't get it to work.

I'd really appreciate if anyone could tell me, how I can send a sql-statement to my geodatabase and get the result from that query back, e.g. as string? 

Tags (2)
0 Kudos
1 Solution

Accepted Solutions
GKmieliauskas
Esri Regular Contributor

Hi,

I have found python sample in ArcGIS Pro SDK community samples. Sample is called DeepThought. Below is sample of c# code which uses that sample python code (answer.py)

            string geoprocPath = @"D:\ArcGIS_SDK_Samples\arcgis-pro-sdk-community-samples-master\Geoprocessing\DeepThought\Toolboxes\toolboxes";
            string toolboxName = "DeepThought.tbx";
            string toolName = "Answer";
            string fullToolPath = Path.Combine(geoprocPath, toolboxName, toolName);

            var gpResult = Geoprocessing.ExecuteToolAsync(fullToolPath, null, null, null, null, GPExecuteToolFlags.AddToHistory);
            gpResult.Wait();

            if (gpResult.Result.IsFailed)
            {
                MessageBox.Show("Geoprocessing failed");
            }
            else
            {
                var resultValues = gpResult.Result.Values;

                if (resultValues == null)
                {
                    // Nothing to read
                }
                else
                {
                    MessageBox.Show($"Returned value: {resultValues[0]}");
                }
            }

It returns the same value as tool executed from Geoprocessing pane.

View solution in original post

0 Kudos
8 Replies
GKmieliauskas
Esri Regular Contributor

Hi,

I would like to refer to your earlier question.

Make python tool which executes query and return result. 

Call that tool using geoprocessing. For which part of process do you need a help?

0 Kudos
nadja_swiss_parks
Occasional Contributor II

Hi 

Thank you for your reply. I've got the assignment to make it work in the dockpane without python - that's the main reason I didn't want to use python. A second reason was because we wanted to test, which method is fastest (we need the most performant possibility) and the third reason is, I simply can't get the result from my python-script back into my dockpane. The result is somehow stuck in my python-script but I need it  to further process in my dockpane. Can you provide a minimal example of a python script, which returns the result from your python script to the calling instance and how to access that result from within the visual studio xaml.cs file? thank you.

0 Kudos
GKmieliauskas
Esri Regular Contributor

Hi,

I have found python sample in ArcGIS Pro SDK community samples. Sample is called DeepThought. Below is sample of c# code which uses that sample python code (answer.py)

            string geoprocPath = @"D:\ArcGIS_SDK_Samples\arcgis-pro-sdk-community-samples-master\Geoprocessing\DeepThought\Toolboxes\toolboxes";
            string toolboxName = "DeepThought.tbx";
            string toolName = "Answer";
            string fullToolPath = Path.Combine(geoprocPath, toolboxName, toolName);

            var gpResult = Geoprocessing.ExecuteToolAsync(fullToolPath, null, null, null, null, GPExecuteToolFlags.AddToHistory);
            gpResult.Wait();

            if (gpResult.Result.IsFailed)
            {
                MessageBox.Show("Geoprocessing failed");
            }
            else
            {
                var resultValues = gpResult.Result.Values;

                if (resultValues == null)
                {
                    // Nothing to read
                }
                else
                {
                    MessageBox.Show($"Returned value: {resultValues[0]}");
                }
            }

It returns the same value as tool executed from Geoprocessing pane.

0 Kudos
nadja_swiss_parks
Occasional Contributor II

thank you, @GKmieliauskas 

unfortunately, that's not my problem. my problem is how i return the value from the python script to the gpResult-variable. maybe I can make it clearer with a minimal example. 

I've got my python-toolbox:

import arcpy
if arcpy.GetLogHistory():
   arcpy.SetLogHistory(False)
if arcpy.GetLogMetadata():
    arcpy.SetLogMetadata(False)
from tkinter import messagebox
from osgeo import gdal
import os
import ast

from arcpy.arcobjects import convertArcObjectToPythonObject
import sys


class Toolbox(object):
    def __init__(self):
        """Define the toolbox (the name of the toolbox is the name of the
        .pyt file)."""
        self.label = "Toolbox"
        self.alias = "toolbox"

        # List of tool classes associated with this toolbox
        self.tools = [ParcsVectors]

def load_vector():
    try: 
        messagebox.showinfo(title="test", message="Hello - this message is from a TEST Python script")
        return "test"

    except Exception as err:
       messagebox.showinfo(title="Load vectors failed", message="Exception: " + str(err))

class ParcsVectors(object):
    def __init__(self):
        """Define the tool (tool name is the name of the class)."""
        self.label = "ParcsVectors"
        self.description = ""
        
    def getParameterInfo(self):
        """Define parameter definitions"""
        params = None
        return params

    def isLicensed(self):
        return True

    def updateParameters(self, parameters):
        return

    def updateMessages(self, parameters):
        return

    def execute(self, parameters, messages):
        result = load_vector()
        messagebox.showinfo(title="testing", message= result)
        return result

The messagebox with "testing" and my result is shown, meaning it works until here. 

Then I tried to get it back into my dockpane with your code:

string installPath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);
string toolboxPath = System.IO.Path.Combine(installPath, "ParcsData.pyt\\ParcsVectors");
var gpResult = Geoprocessing.ExecuteToolAsync(toolboxPath, null, null, null, null, GPExecuteToolFlags.AddToHistory); 
gpResult.Wait();

if (gpResult.Result.IsFailed)
{
	ArcGIS.Desktop.Framework.Dialogs.MessageBox.Show("Geoprocessing to read vectors failed");
}
else
{
	var resultValues = gpResult.Result.Values;
	ArcGIS.Desktop.Framework.Dialogs.MessageBox.Show(resultValues.Count().ToString(), "Returned value:");

	if (resultValues == null)
	{
		ArcGIS.Desktop.Framework.Dialogs.MessageBox.Show("No vectors to read");
	}
	else
	{
		ArcGIS.Desktop.Framework.Dialogs.MessageBox.Show($"Returned value: {resultValues[0]}");
	}
}

 The message gave me first an index out of range error, that's why I tried to check if anything is returned at all. It tells me, that there is nothing in my result. How can I return my result back into my dockpane?  I assume it's a fairly simple problem but I cant find the solution anywhere...

0 Kudos
GKmieliauskas
Esri Regular Contributor

Hi,

Have you take a look at answer.py file?

Last line of the file returns result from python code:

 

arcpy.SetParameter(0, sixbynine.compute())

 

Because there is no input parameters, index of SetParameter method is 0. If your python tool has input parameters, so output index value must be equal to your input parameters count.

0 Kudos
nadja_swiss_parks
Occasional Contributor II

at first not, thanks for pointing that out. but even with that I can't get it to return my string... I played around with the options and googled it, but I'm still stuck. 


 

class ParcsVectors(object):
    def __init__(self):
        """Define the tool (tool name is the name of the class)."""
        self.label = "ParcsVectors"
        self.description = ""
        
    def getParameterInfo(self):
        """Define parameter definitions"""
        result = arcpy.Parameter(
            displayName="Output Features",
            name="out_features",
            datatype="GPString",
            parameterType="Required",
            direction="Output")

        params = [result]

        return params

    def isLicensed(self):
        return True

    def updateParameters(self, parameters):
        return

    def updateMessages(self, parameters):
        return

    def execute(self, parameters, messages):
        # parameters[0] = load_vector()
        result = load_vector()
        messagebox.showinfo(title="testing", message= result)
        arcpy.SetParameter(0, result)
        return

 

How do can I return my result from within my arcpy-toolbox? it shouldn't be THAT hard to figure out!

0 Kudos
GKmieliauskas
Esri Regular Contributor

I think the main difference is how we call geoprocessing tool. I use the same way as in sample I have referred above (from tbx). I suggest you create tbx file and in python script file leave only calculation code + returning of results.

Or try to ask Python group. One of questions from python group is here

0 Kudos
nadja_swiss_parks
Occasional Contributor II

Thank you @GKmieliauskas for your reply. I had a tbx at the very beginning of the project, but with a tbx some of the functionalities I needed weren't available. That's why I cannot use that solution. I've asked now in the python group, see here, and found there the working python code. combined with your .NET SDK code I now can return a string. finally 🙂

0 Kudos