Passing Time-valued query parameters

General PGNP discussions.

Passing Time-valued query parameters

Postby DMH » Fri Apr 18, 2014 6:33 am

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.
DMH
 
Posts: 1
Joined: Fri Apr 18, 2014 6:00 am

Re: Passing Time-valued query parameters

Postby Moderator » Tue Apr 29, 2014 11:21 am

I tested build 1.4.0.3244 using test script below, and it worked as expected.
Code: Select all
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: Select all
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.
User avatar
Moderator
Site Admin
 
Posts: 303
Joined: Wed Oct 29, 2008 11:27 pm


Return to Miscellaneous

Who is online

Users browsing this forum: No registered users and 0 guests