本文介绍了“输入结束时出现语法错误”-尝试从数据库启动存储过程时的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在用 core编写程序,并尝试从数据库(Postgresql)启动存储过程。
I am writing in core and trying to launch stored procedure from database(Postgresql).
我收到一条错误消息,说输入结束时出现语法错误。 连接字符串正确(在调试过程中找到)。问题在于.FromSql()方法语法。
I am getting an error saying there is an "syntax error at end of input". Connection string is right(found out during debugging). The problem is with .FromSql() method syntax.
这是代码:
List<ActivePackageForOpenBillingPeriod> activeUserPackagesForOpenBillingPeriod =null; using(var conn = new NpgsqlConnection("Host=localhost;Port=xxx;Database=postgres;Username=xxx;Password=xxx;TrustServerCertificate=xxx;ApplicationName=xxx;")) { var s = "create function \"GetActiveUserPackagesForOpenBillingPeriod({0})\""; activeUserPackagesForOpenBillingPeriod = context.ActivePackageForOpenBillingPeriods.FromSql(s,DateTime.Now).ToList(); }这是我尝试调用的存储过程
This is the stored procedure I am trying to call
create function "GetActiveUserPackagesForOpenBillingPeriod"(date timestamp without time zone) returns TABLE("Amount" numeric, "Package" character varying, "User" text,"Account" int, "AcceptanceActID" int, "HasChangedPackage" boolean) language plpgsql as $$ DECLARE BEGIN RETURN QUERY SELECT a."ID" AS "AccountId", u."ID" AS "UserId", up."PackageID", up."TotalAmount", CASE WHEN count(DISTINCT tl."PackageID") IN (0,1) THEN false ELSE true END AS "HasChangedPackage" FROM public."Accounts" as a LEFT JOIN billing."TransactionHeaders" AS th ON th."AccountID" = a."ID" INNER JOIN security."Users" AS u ON u."AccountID"= a."ID" INNER JOIN billing."UserPackages" AS up ON up."UserID"=u."ID" AND COALESCE(th."Date", date) BETWEEN up."StartDate" AND COALESCE(up."EndDate", date) LEFT JOIN billing."TransactionLines" AS tl ON th."Id" = tl."TransactionHeaderID" WHERE th."AcceptanceActID" IS NULL AND a."StatusID"!=4 AND a."StatusID"!=3 AND up."StatusID"=1 GROUP BY a."ID", u."ID", up."PackageID", up."TotalAmount"; END; $$;推荐答案
您正在运行的查询是错误的。
The query you are running is wrong. It is a mix between the function creation statement and a call to get its result.
也只能在函数名称中加上双引号,而不是参数
Also only the function name should be doublequoted, not the parameter
将查询更改为
var s = "SELECT * FROM \"GetActiveUserPackagesForOpenBillingPeriod\"({0})";更多推荐
“输入结束时出现语法错误”
发布评论