英文:
Calling Snowflake stored procedure from Go driver
问题
我在Snowflake中有一个名为SP_MERGE_ASSETS的存储过程。我可以通过以下方式从Snowflake控制台调用它：
CALL MY_DATABASE.MY_SCHEMA.sp_merge_assets(0)
这个方法可以正常工作并且达到了我预期的效果。然而，当我尝试从Snowflake的Go库中调用完全相同的存储过程时：
query := "CALL MY_DATABASE.MY_SCHEMA.sp_merge_assets(?)"
_, err := client.db.ExecContext(ctx, query, 0)
我会得到一个SQL编译错误，错误信息是Unknown user-defined function MY_DATABASE.MY_SCHEMA.SP_MERGE_ASSETS。
我想这是有道理的，因为在我的账户中没有名为"MY_DATABASE.MY_SCHEMA.SP_MERGE_ASSETS"的用户定义函数（UDF）。但是这个错误掩盖了实际的问题：我的存储过程没有被调用。我在这里做错了什么？
英文:
I have a stored procedure in Snowflake called SP_MERGE_ASSETS. I can call it from the Snowflake console by doing this:
CALL MY_DATABASE.MY_SCHEMA.sp_merge_assets(0)
This works and does exactly what I expect it to. However, when I try the exact same procedure from Snowflake's Go library:
query := "CALL MY_DATABASE.MY_SCHEMA.sp_merge_assets(?)"
_, err := client.db.ExecContext(ctx, query, 0)
I get an SQL compilation error stating, Unknown user-defined function MY_DATABASE.MY_SCHEMA.SP_MERGE_ASSETS.
I suppose this makes sense as there isn't a UDF named "MY_DATABASE.MY_SCHEMA.SP_MERGE_ASSETS" defined in my account. But this error masks the actual problem: my stored procedure isn't being called. What am I doing wrong here?
答案1
得分: 1
原来，在Snowflake中调用存储过程与SQL Server不同，需要使用QueryContext函数而不是ExecContext函数。如果使用ExecContext，Snowflake会将查询解释为对用户定义函数的调用。将代码更改为
query := "CALL MY_DATABASE.MY_SCHEMA.sp_merge_assets(?)"
_, err := client.db.QueryContext(ctx, query, 0)
问题得到解决。
英文:
It turns out that, unlike in SQL server, calling a stored procedure in Snowflake requires using the QueryContext function instead of the ExecContext function. If you use ExecContext, Snwoflake will interpret the query as a call to a user-defined function. Changing the code to
query := "CALL MY_DATABASE.MY_SCHEMA.sp_merge_assets(?)"
_, err := client.db.QueryContext(ctx, query, 0)
fixed the issue.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论