Products Resources Support About Us

Unknown Error running SQL from Python Command

sql

#1

Hello, brand new to Universe and trying to learn the basics of using Python to interact with the Universe database.

I’m using the RESTful Web Services to call a Basic subroutine:


SUBROUTINE createBook ( BookDetails, ErrText, pyresult )
pymodule = PyImport("createBook")
pyresult = PyCallFunction("createBook", "create_book", BookDetails)
RETURN

Here is a simplified version of the Python code (createBook.py):


def create_book(book_details)
    import u2py

    last_id = u2py.Command("SELECT MAX(ID) FROM U2_BOOKS;").run()
    return str(last_id)

I get an error on the line with the Command and run:


error creating U2Error (10252) unknown Traceback (most recent call last):

  File "C:\U2\UV\incubator\u2_books\BOOKS_PP\createBook.py", line 4, in create_book
    last_id = u2py.Command("SELECT MAX(ID) FROM U2_BOOKS;").run()

  File "C:\U2\UV\bin\u2py.py", line 85, in run
    super().run()

u2py.U2Error: (10252) unknown

I get this error with any sort of SQL statement (e.g. “SELECT U2_BOOKS FIRST 10;”). However, if I change the command to something else (like “WHO”) it works fine.

I’ve tested out the SQL statement and it works fine if I run it from the U2 Python shell command prompt. It also works if I just put it into a simple python script and run it from the U2 shell using RUNPY command. But for some reason I’m getting this unknown error when running it from a Basic subroutine.

Any ideas?


#2

SQL aside, the main issue I see is that you are not capturing the output from the u2py.Command.run().

try:
last_id = u2py.run(“SELECT MAX(ID) FROM U2_BOOKS TOXML;”, capture=True )

Note that I added the TOXML keyword to the command, because otherwise it would be the text that is displayed to the screen. I find the XML easier to deal with.


#3

Agreed. I was just simplifying my code as much as possible. I tried your changes but still get that odd “Unknown error”.

The odd thing is that if I run this from the shell it works. The only time I’m getting this error is when I’m calling it from the RESTful web services as a subroutine resource.


#4

I can confirm after more testing that any time the RESTful web service calls the python code that runs a select sql statement (using either u2py.run() or u2py.Command().run()), it gives this error. However, running the exact same code from the shell works flawlessly.

Is this a bug with the RESTful web service or maybe I’m just missing a setting? At this point, I’m just running the trial versions of everything locally.

Alternatively, is there a better way to access Universe data from a javascript web client than the RESTful web services? I’d prefer to use Python instead of Basic and SQL over RetrieVe. I’m completely new to Universe.