Passing Time-valued query parameters

  • DMH
  • Topic Author
  • Visitor
  • Visitor
10 years 2 months ago #10956 by DMH
In Postgresql 9.3 I have defined this table-valued stored function:

FUNCTION "qryIncidentDetail_Date_Grade"(IN d1 date, IN d2 date, IN c1 time without time zone, IN c2 time without time zone, IN g1 character, IN g2 character)

The following SQL statement executes this stored function correctly through pgAdmin3.

SELECT * from "qryIncidentDetail_Date_Grade"('01-01-2014','02-01-2014','03:00:00','05:59:59','A1', 'A2');

However when attempting to execute the same stored function in a Classic ASP/VB Script program using ADODB connection string PROVIDER=PGNP.1, PGNP returns the following error

PostgreSQL Native Provider error '80040e14'
ERROR: time out of range
/actas_pg_server/actas_qry.asp, line 1091

In the ADODB.Command Parameters setup for the problem function, I have used the ADOVBS constant adDBTime for the two time parameters (c1 and c2) .

I find that other stored functions involving adDBDate but not adDBTIme are working fine through PGNP, but it seems only to accept adDBTime parameters with a value of "00:00:00".

Is this a bug, or am I doing something wrong? I'm testing with PGNP-Postgres-DE-Trial-1.4.0.3218.exe.

PS. Why do we need separate data and time parameters? To allow analysis of historical time-stamped data by both date range and time-of-day, for instance 14-18MAR2014 08:00:00-18:00:00.

Please Log in or Create an account to join the conversation.

More
10 years 1 month ago #10989 by Moderator
Replied by Moderator on topic Re: Passing Time-valued query parameters
I tested build 1.4.0.3244 using test script below, and it worked as expected.
Code:
option explicit '' connection, command and recordset variables dim oCon dim sCon, sSQL Dim cmdObj, rsObj '' create connection & command objects set oCon = CreateObject("ADODB.Connection") sCon = "Provider=PGNP.1;Persist Security Info=True;Data Source=localhost;" & _ "Initial Catalog=postgres;User ID=postgres;Password=12345;" & _ "Extended Properties=""PORT=5432;""" '' connect oCon.Open sCon MsgBox "Connected." Set cmdObj = CreateObject("ADODB.Command") Set cmdObj.ActiveConnection = oCon cmdObj.CommandText = "SELECT * FROM ""qryIncidentDetail_Date_Grade""('01-01-2014','02-01-2014','03:00:00','05:59:59','A1', 'A2');" Set rsObj = CreateObject("ADODB.Recordset") rsObj.Open cmdObj if not (rsObj.BOF and rsObj.EOF) then MsgBox rsObj(0).Value & " | " & rsObj(1).Value & " | " & rsObj(2).Value & " | " & rsObj(3).Value & " | " & rsObj(4).Value & " | " & rsObj(5).Value End if rsObj.Close MsgBox "Finish."

Then I changed the script to use stored procedure call with parameters:
Code:
option explicit '' connection, command and recordset variables dim oCon dim sCon, sSQL Dim cmdObj, rsObj '' create connection & command objects set oCon = CreateObject("ADODB.Connection") sCon = "Provider=PGNP.1;Persist Security Info=True;Data Source=localhost;" & _ "Initial Catalog=postgres;User ID=postgres;Password=12345;" & _ "Extended Properties=""PORT=5432;""" '' connect oCon.Open sCon MsgBox "Connected." Set cmdObj = CreateObject("ADODB.Command") Set cmdObj.ActiveConnection = oCon cmdObj.CommandText = """qryIncidentDetail_Date_Grade""" cmdObj.CommandType = 4 '' adCmdStoredProc '' adVarChar = 200 '' adParamInput = 1 cmdObj.Parameters.Append cmdObj.CreateParameter("@d1", 200, 1, 100, "01-01-2014") cmdObj.Parameters.Append cmdObj.CreateParameter("@d2", 200, 1, 100, "02-01-2014") cmdObj.Parameters.Append cmdObj.CreateParameter("@c1", 200, 1, 100, "03:00:00") cmdObj.Parameters.Append cmdObj.CreateParameter("@c2", 200, 1, 100, "05:59:59") cmdObj.Parameters.Append cmdObj.CreateParameter("@g1", 200, 1, 100, "A1") cmdObj.Parameters.Append cmdObj.CreateParameter("@g2", 200, 1, 100, "A2") MsgBox "Params set." Set rsObj = CreateObject("ADODB.Recordset") rsObj.Open cmdObj if not (rsObj.BOF and rsObj.EOF) then MsgBox rsObj(0).Value End if rsObj.Close MsgBox "Finish."

And got ERROR: function qryIncidentDetail_Date_Grade(character varying, character varying, character varying, character varying, character varying, character varying) does not exist
LINE 2: SELECT * FROM "qryIncidentDetail_Date_Grade"($1,$2,$3,$4,$5,...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

This is the application error, and it can be fixed by specifying the correct types for parameters.

Build 3244 (or higher) will be released shortly. Please contact Support if you want to obtain pre-release build.

Please Log in or Create an account to join the conversation.

Time to create page: 0.159 seconds
Powered by Kunena Forum