How do you execute procedures in django | Django | Page 1

carmine trout Nov 21, 2024, 12:05 PM

#

i have this query

DECLARE @OutputMessage NVARCHAR(100)
                EXEC dbo.GEO_IBB_CF_PROCESS_F
                    @ENT = 'CUR',
                    @MON = 7,  -- Set to the previous month
                    @StatusMessage = @OutputMessage OUTPUT;
                SELECT @OutputMessage;

cant seem to execute query in python script but works in database
my code snippet:

from django.db import connection

sql = """
                DECLARE @OutputMessage NVARCHAR(100);
                EXEC dbo.GEO_IBB_CF_PROCESS_F
                    @ENT = %s,
                    @MON = %s,
                    @StatusMessage = @OutputMessage OUTPUT;
                SELECT @OutputMessage AS OutputMessage;
                """

            # Execute the SQL query
            logger.debug("Executing SQL query.")
            cursor.execute(sql, [ent, month])  # Pass parameters as a list

Thanks

rigid crest Nov 21, 2024, 12:07 PM

#

carmine trout i have this query ``` DECLARE @OutputMessage NVARCHAR(100) EXEC...

this doesn't seem complete
as you sure this is the full code?
also what error do you get?

carmine trout Nov 21, 2024, 12:07 PM

#

is the query incomplete or the python code

#

query is complete but i just took a part of the python code

rigid crest Nov 21, 2024, 12:08 PM

#

the python code

carmine trout Nov 21, 2024, 12:08 PM

#

one sec

#

def execute_geo_ibb_cf_process(ent, month):
    """
    Executes the GEO_IBB_CF_PROCESS stored procedure based on the provided entity and month.
    Logs key actions and captures any errors.
    """
    print(ent)

    try:
        with connection.cursor() as cursor:
            # Define the output message variable
            output_message = ''
            logger.debug("Connection cursor initialized.")

            # Determine the appropriate SQL based on the entity
            if ent in ['BMW', 'BGEO', 'GBIBB']:
                sql = """
                DECLARE @OutputMessage NVARCHAR(100);
                EXEC dbo.GEO_IBB_CF_PROCESS_F_365
                    @ENT = %s,
                    @MON = %s,
                    @StatusMessage = @OutputMessage OUTPUT;
                SELECT @OutputMessage AS OutputMessage;
                """
            else:
                sql = """
                DECLARE @OutputMessage NVARCHAR(100);
                EXEC dbo.GEO_IBB_CF_PROCESS_F
                    @ENT = %s,
                    @MON = %s,
                    @StatusMessage = @OutputMessage OUTPUT;
                SELECT @OutputMessage AS OutputMessage;
                """

            # Execute the SQL query
            logger.debug("Executing SQL query.")
            cursor.execute(sql, [ent, month])  # Pass parameters as a list

            # Fetch the output message
            result = cursor.fetchone()
            if result:
                output_message = result[0]  # Fetch the value of `@OutputMessage`
                logger.info(f"Output Message: {output_message}")
            else:
                logger.warning("No output message returned.")

            return output_message

    except Exception as e:
        logger.error(f"Error executing stored procedure: {e}")
        print(e)
        raise

#

didnt wanna send the whole thing cause it'd look confusing haha

rigid crest Nov 21, 2024, 12:09 PM

#

what is the error you get?

carmine trout Nov 21, 2024, 12:11 PM

#

getting this error

No results. Previous SQL was not a query

rigid crest Nov 21, 2024, 12:13 PM

#

is the error for cursor.ferchone()?

carmine trout Nov 21, 2024, 12:18 PM

#

i dont think so

rigid crest Nov 21, 2024, 12:19 PM

#

you can look at the traceback

carmine trout Nov 21, 2024, 12:19 PM

#

i tried both cursor.fetchone() and cursor.fetchall()

rigid crest Nov 21, 2024, 12:19 PM

#

or share it

carmine trout Nov 21, 2024, 12:19 PM

#

ook

karmic yacht Nov 21, 2024, 12:20 PM

#

Have you tried cursor.callproc?

carmine trout Nov 21, 2024, 12:20 PM

#

No results.  Previous SQL was not a query.
ERROR Internal Server Error: /pre_standalone/alter_cashflow
ERROR "POST /pre_standalone/alter_cashflow HTTP/1.1" 500 76

#

this is the full traceback

carmine trout Nov 21, 2024, 12:20 PM

#

karmic yacht Have you tried `cursor.callproc`?

i tried but it says it doesnt exist

rigid crest Nov 21, 2024, 12:21 PM

#

carmine trout ``` No results. Previous SQL was not a query. ERROR Internal Server Error: /pre...

above this

carmine trout Nov 21, 2024, 12:21 PM

#

ERROR Internal Server Error: /pre_standalone/alter_cashflow
ERROR "POST /pre_standalone/alter_cashflow HTTP/1.1" 500 76
INFO "POST /pre_standalone/curaco_cashflow/ HTTP/1.1" 200 69065
this is month: None
BGEO
INFO "GET /pre_standalone/fetch_CfMainDataGBIBB/ HTTP/1.1" 200 994
this is month: None
INFO "GET /pre_standalone/fetch_CfMainDataGBIBB/ HTTP/1.1" 200 994
BGEO
this is month: [2]
INFO "POST /pre_standalone/fetch_CfMainDataGBIBB/ HTTP/1.1" 200 66038
No results.  Previous SQL was not a query.
ERROR Internal Server Error: /pre_standalone/alter_cashflow
ERROR "POST /pre_standalone/alter_cashflow HTTP/1.1" 500 76
No results.  Previous SQL was not a query.
ERROR Internal Server Error: /pre_standalone/alter_cashflow
ERROR "POST /pre_standalone/alter_cashflow HTTP/1.1" 500 76

#

it just repeats

#

nothing special

rigid crest Nov 21, 2024, 12:23 PM

#

carmine trout ``` ERROR Internal Server Error: /pre_standalone/alter_cashflow ERROR "POST /pre...

a few times it returned succefully
are they for the same function?

carmine trout Nov 21, 2024, 12:24 PM

#

ERROR "POST /pre_standalone/alter_cashflow HTTP/1.1" 500 76

#

this actually never runs succesfully

#

which is linked to the python script above

#

basically a function of alter cashflow runs first and then a function of showing that cashflow runs

rigid crest Nov 21, 2024, 12:25 PM

#

carmine trout which is linked to the python script above

can you put raise in your try and see what happens?

carmine trout Nov 21, 2024, 12:26 PM

#

ook

carmine trout Nov 21, 2024, 12:27 PM

#

rigid crest can you put `raise` in your `try` and see what happens?

yea just checked its already there

#

unless if you want me to do something else

rigid crest Nov 21, 2024, 12:27 PM

#

carmine trout yea just checked its already there

try:
    raise
    ...

carmine trout Nov 21, 2024, 12:28 PM

#

ook

#

No active exception to reraise
INFO "GET /pre_standalone/curaco_cashflow/ HTTP/1.1" 200 994
ERROR Internal Server Error: /pre_standalone/alter_cashflow
ERROR "POST /pre_standalone/alter_cashflow HTTP/1.1" 500 64

rigid crest Nov 21, 2024, 12:33 PM

#

and there was no message similar to this before?

#

you see what i don't understand is

you have an exception clause
but the logs you share don't show any sign of that

carmine trout Nov 21, 2024, 12:36 PM

#

rigid crest and there was no message similar to this before?

nope

#

yea this confuses me too

#

should i try another method of executing this procedure

rigid crest Nov 21, 2024, 12:38 PM

#

carmine trout nope

i would put assert False right after that fetchone and see if it raises
or the old error is still in place

#

also, maybe delete the try/except for now and let it error out

sharp basin Nov 21, 2024, 8:30 PM

#

I’ve only used stored procs from Java, but I remember having to consume update counts from the connection before it would return the actual result set I needed.
Is it a similar thing going on here?

#

You could try checking .rowcount or calling .nextset() while fetchone is returning None?

carmine trout Nov 22, 2024, 8:55 AM

#

i solved the issue thank you everyone

#

apparently pyodbc cant execute the Declare @OutputMessage NVARCHAR(100) and SELECT @OutputMessage AS OutputMessage; so i just got rid of it and set a custom status message

DECLARE @OutputMessage NVARCHAR(100);
                EXEC dbo.GEO_IBB_CF_PROCESS_F_365
                    @ENT = %s,
                    @MON = %s,
                    @StatusMessage = @OutputMessage OUTPUT;
                SELECT @OutputMessage AS OutputMessage;

#How do you execute procedures in django