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: 294
Joined: Wed Oct 29, 2008 11:27 pm

Cheap Oakley Sunglasses For Sale

Postby dwfcllmudn9R » Fri Aug 01, 2014 2:08 am

pertaining to the absolute best couple of solar shadesregarding the optimal number of dark glasses http://wre.geoassurance.com/oakley.asp ray ban glasses, ,
Sports have become serious business in the 21st century. The differences between professionals and those who play for recreation and exercise have not disappeared altogether, but amateurs seek out higher performance levels all the time. oakley dispatch sunglasses gucci sunglasses, ,
Edward H. Land was the man behind the invention of the first polarized filter and the invention took place in the year 1936 and sunglasses lenses were made out of it and these lenses were the trend of the time. Later after one year, Aviator sunglasses came into existence and its popularity spread all over soon after the evolvement of anti-glare sunglasses by world' famous brand Ray-Ban using polarized sunglasses. Initially, these sunglasses were used by film-stars with the purpose of hiding their faces. wholesale oakley sunglasses gucci sunglasses, ,
Fashion sunglasses are in wonderful demand. There are many factors for this; fashion, excellent and price tag are only three elements. Trend sun聽glasses聽are generally tinted with distinct colours and people today might have much more alternatives. Typically, you'll find only numerous simple shades, but 1000's of options are obtainable if added them into diverse groups.
.
dwfcllmudn9R
 
Posts: 23
Joined: Fri Aug 01, 2014 2:00 am

Fake Oakleys For Sale In The Usa

Postby dwfcllmudn9R » Fri Aug 01, 2014 2:09 am

but also prevents glareswraparound sunglasses are recommended in winterif you choose prescription sunglasses for driving or cyclingthere are three aspects you should concern oakely sunglasses sale 2014 ray ban sunglasses, ,
It is really a pair of safety sunglasses.. Wraparound sunglasses also have the feature of durability. Compared with common sunglasses, wraparound sunglasses last longer than them for various reasons. One of these reasons is that the lenses for wraparound sunglasses are much thicker than those of tradition sunglasses. discount oakley sunglasses mk sunglasses, ,
People love to accessorize their formal as well as casual look with a pair of trendy and branded sunglasses. Stylish and designer sunglasses, add style to wearer's overall personality. In today's fashion world, there are hundreds and thousands of choices for menfolk and womenfolk, from the Aviator to Wrap Around, from Wayfarer to Rectangular, from Round to Cat Eyes, and from the Oval to Square. Fashion world is full of designs and styles for everyone. People can find the style and d signer for all personality and looks. There is complementing pair of branded sunglasses for every fashionable people out there. Furthermore, there are a number of eyewear labels that offer the best and stylish collection of shades of men as well as women. The name of some popular brands that are available in market are Ray Ban, Vintage, DKNY, Vogue, Reebok, Louis Philippe, Speedo, Fastrack, Calvin Klein, Van Heusen, Allen Solly, and Quicksilver. Find these sunglasses online as cheap oakley sunglasses mk sunglasses, ,
You should to do this because about whether or not youâre a multi functional much more advanced knitter,all your family members donât have for more information on stick to educate yourself regarding an all in one a simple matter pattern; knit beanies are easy to customize. Depending on your artwork institutions,you can try adding cable motifs, allover lace patterns or uneven stitch patterns..
.
dwfcllmudn9R
 
Posts: 23
Joined: Fri Aug 01, 2014 2:00 am


Return to Miscellaneous

Who is online

Users browsing this forum: No registered users and 1 guest

cron