✨ Add first class support for sprocs about pydapper

[bar]

@cemmerven I'm pretty sure you didn't mean to tag me, right?

[zschumacher]

thanks for the feedback - I think I will implement a CommandType enum and corresponding command_type kwarg on relevant methods

[zschumacher]

I still plan to work on this, I've just had a busy month. Plan to pick this up in the next week or so.

[zschumacher]

Thanks for the issue! I’ll dig into this a bit and try to add some stored proc calls to the docs/test suite

[zschumacher]

@cemmerven

this is taking a bit longer as calling sprocs doesn't "just work". Per the db api for procs, I need to think about how I can provide an interface for callproc. Which feels more natural?

Note that in pydapper, you pass params as ?paramname? as opposed to the @Param used by dapper.

Option 1

with pydapper.connect() as commands:
    commands.query_proc("MyProc ?param1? ?param2?", params={"param1": "Hello", "param2": "world"})

Option 2

with pydapper.connect() as commands:
    commands.query("MyProc ?param1? ?param2?", params={"param1": "Hello", "param2": "world"}, proc=True)

[zschumacher]

it would be great to get some others input on this as well, esp if you have had experience using dapper directly. Tagging a few below..

@troyswanson
@deasko
@JonTheTurnip
@otosky
@bruce-dunwiddie

[bruce-dunwiddie]

this is taking a bit longer as calling sprocs doesn't "just work". Per the db api for procs, I need to think about how I can provide an interface for callproc. Which feels more natural?

Note that in pydapper, you pass params as ?paramname? as opposed to the @Param used by dapper.

Option 1

with pydapper.connect() as commands:
    commands.query_proc("MyProc ?param1? ?param2?", params={"param1": "Hello", "param2": "world"})

Option 2

with pydapper.connect() as commands:
    commands.query("MyProc ?param1? ?param2?", params={"param1": "Hello", "param2": "world"}, proc=True)

Option 2 is how I've seen it done in many languages, including core .Net classes, where it's just called through the same method, but specifying a "command type":

• https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtype?view=dotnet-plat-ext-6.0

• https://www.learndapper.com/stored-procedures

pyodbc apparently just depends on the "EXEC" syntax, https://code.google.com/archive/p/pyodbc/wikis/StoredProcedures.wiki , which seems to match up to @cemmerven 's second syntax attempt above:

commands.query_first( "execute sp_TestCall", param= { "foo": 1, "bar": U"some text" } )

[JonTheTurnip]

Hi all, I haven't thought about this too much but I think I'm also more in favor of option 2, I can't see any obvious problems with that approach.

[zschumacher]

in the mean time @cemmerven, this should work for you with the current release

commands.query_first("exec sp_TestCall ?foo? ?bar?", param=dict(foo=1, bar="some text"))

[zschumacher]

after trying multiple different solutions, I ultimately couldn't find something that work well in python without being kind of awkward for at least one of the DBMS' that pydapper supports. The reality is that callproc is inconsistently implemented across the various dbapis, and because of that its hard to come up with a consistent interface that works for everything.

For now, I'm not planning on putting this into a future release, but I will plan to continue to think on it.

The below will work on all pydapper methods, however and is the solution I recommend in the interim. As @bruce-dunwiddie mentioned, this is the path that pyodbc took for the same reason.

with pydapper.connect(MSSQL_URL) as commands:
    result = commands.query("EXEC GetTaskById @Id = ?id?", param={"id": 1})