Passing Time-valued query parameters

DMH
Topic Author
Visitor

9 years 5 months ago #10956 by DMH

Passing Time-valued query parameters was created 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.

Moderator
Offline
New Member

More

9 years 4 months 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.142 seconds

Powered by Kunena Forum

Passing Time-valued query parameters

Login Form

VM - Shopping cart