?
Solved

Sql Server 2000 store procedure with a date parameter.

Posted on 2005-03-21
45
Medium Priority
?
496 Views
Last Modified: 2013-12-26
This must be easy.
For me this has always been difficult.
I have asked it before , I have got answer, the answer must have been ok,
but unfortunately I have yet not been able to do this. If the same
happens this time, please be patient with me.

I have a store procedure namely usp_LowFatSpread  sql server 2000 with
a date parameter.

When I write in QueryAnalyzer

exec usp_LowFatSpread '2005-03-17'

The top 5 rows I get are

loom_no a       b       c      d        e       f       g       h  
------- ----   -----   -----   -----   ------   -----   -----  ----
23      0      16      0      768      6416      260      1405      0
46      0      0      0      0      11170      0      3326      0
69      0      56      0      2725      22705      450      5283      0
77      0      42      0      2350      14002      38      5406      0
31      0      0      0      0      17466      379      8890      0


I want to call the above procedure from within powerbuilder to get
the above resultset.


The ['2005-03-17'] of ( exec usp_LowFatSpread '2005-03-17') will
be replaced by the date provided by the user from within powerbuilder.

Please help.


0
Comment
Question by:Mateen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 15
  • 14
  • +1
45 Comments
 
LVL 33

Expert Comment

by:sajuks
ID: 13589307
are u using the stored procedure as datasource ?when u create the datawindow you could just select the sp as ur datasource and in ur code u'd 've it as
dw_report.Retrieve(em_date.text)

or
http://www.experts-exchange.com/Programming/Programming_Languages/PowerBuilder/Q_21244750.html
0
 

Author Comment

by:Mateen
ID: 13589401
Here is my procedure in SqlServer 2000

CREATE procedure usp_LowFatSpread @input datetime
as

DECLARE @START DATETIME
dECLARE @END DATETIME

SELECT @START=CONVERT(CHAR(11),@INPUT)
      ,@END=cONVERT(CHAR(11),@iNPUT) + ' 23:59:59PM'

SELECT LOOM_NO,AT_DATE_TIME,CASE WHEN SUB_STATUS_CODE = 8 THEN -1 ELSE SUB_STATUS_CODE END
         AS sTATUS
      ,IDENTITY(INT,1,1) AS ROWNO
  INTO #TEMP
  FROM Loom_Pick_Count
 wHERE AT_DATE_TIME BETWEEN @START AND @END
   AND SUB_STATUS_CODE IN (1,2,4,8,16,64,127,128)
 ORDER BY LOOM_NO,at_DATE_TIME
 
sELECT X .LOOM_NO
,SUM(CASE STATUS WHEN 1 THEN LOST ELSE 0 END) AS  [ Knotting Stop]
,SUM(CASE STATUS WHEN 4 THEN 1 ELSE 0 END) AS  [ weft count]
,SUM(CASE STATUS WHEN 2 THEN LOST ELSE 0 END) AS  [ Article Stop]
,SUM(CASE STATUS WHEN 4 THEN LOST ELSE 0 END) AS  [ Weft Stop]
,SUM(CASE STATUS WHEN 16 THEN LOST ELSE 0 END) AS  [ Warp Stop]
,SUM(CASE STATUS WHEN 64 THEN LOST ELSE 0 END) AS  [ Leno Stop]
,SUM(CASE STATUS WHEN 127 THEN LOST ELSE 0 END) AS  [ Other Stop]
,SUM(CASE STATUS WHEN 128 THEN LOST ELSE 0 END) AS  [ Electro-Mechanical Stop]

  FROM (
sELECT a.LOOM_NO
      ,A.STATUS
      ,DATEDIFF(S,A.AT_DATE_TIME,COALESCE(B.AT_DATE_TIME,@end)) AS LOST

  fROM ( SELECT ROWNO,LOOM_NO,sTATUS ,AT_dATE_TIME  FROM #temp wHERE sTATUS > -1 ) AS a
 lEFT oUTER JOIN (
  SELECT ROWNO,LOOM_NO ,sTATUS ,AT_dATE_TIME   FROM #temp ) AS B   ON A.LOOM_NO=B.LOOM_NO  AND B.ROWNO=A.ROWNO + 1
    ) AS X

 GROUP BY LOOM_NO

-- exec usp_LowFatSpread '2005-03-17'
GO

I cannot use it as datasource because data will be determined after the user has inputted the
date from powerbuilder.
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13589404
hi,


try,

Datetime ldt_to

Declare ur_procedure Procedure for usp_LowFatSpread
                  @to_date = :ldt_to
                        Using SQLCA ;
            
            Execute ur_procedure ;
            
            If SQLCA.SQLCode < 0 then
                  MessageBox("", "Error executing db procedure usp_LowFatSpread - " + string(SQLCA.sqlcode) + " " + SQLCA.sqlerrtext)
                  Close ur_procedure ;
                  Return
            End If
            
            Close ur_procedure ;


let me know if u need more assistance...

Cheers,
Rosh
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Mateen
ID: 13589427
Hi Dia:
As u suggest, If I declare and execute , then how will get the data in datawindow to preview/report it.
0
 
LVL 18

Assisted Solution

by:diasroshan
diasroshan earned 1000 total points
ID: 13589435
hi,

<I cannot use it as datasource because data will be determined after the user has inputted the
date from powerbuilder.>
 as per ur above line i wud like to comment that the whole idea abt passin retrieval arguments in PB is that we get the data for a DW only after we pass arguments from powerbuilder...

also,
any SP which returns a select statment can be used as a datasource to make a DW... well i have not yet seen the complications involved in ur SP... but by the looks of it i feel u can go ahead and create a DW with ur above SP and call the dw as,
dw_report.Retrieve(ldt_date)

let me know if u need more assistance...

Cheers,
Rosh


0
 

Author Comment

by:Mateen
ID: 13589456
Hi dia:
<< but by the looks of it i feel u can go ahead and create a DW with ur above SP and call the dw as,
dw_report.Retrieve(ldt_date) >> 

I exactly want this but I will get somewhere struck.
Please instruct me step by step as
Create datawindow->Sql Select->Store Procedure and
then ??????????????

0
 
LVL 33

Expert Comment

by:sajuks
ID: 13589461
from the pb help file
CREATE PROCEDURE proc2 AS
SELECT emp_name FROM employee
      SELECT part_name FROM parts
PowerBuilder provides access to both result sets:
// Declare the procedure.
DECLARE emp_proc2 PROCEDURE FOR proc2;
// Declare some variables to hold results.
string      emp_name_var
string      part_name_var
// Execute the stored procedure.
EXECUTE emp_proc2;
// Fetch the first row from the first result

// set.
FETCH emp_proc2 INTO :emp_name_var;

// Loop through all rows in the first result

// set.
DO WHILE sqlca.sqlcode = 0

// Fetch the next row from the first result set.

FETCH emp_proc2 INTO :emp_name_var;
LOOP

// At this point we have exhausted the first

// result set. After this occurs,
// PowerBuilder notes that there is another
// result set and internally shifts result sets.
// The next FETCH executed will retrieve the
// first row from the second result set.
// Fetch the first row from the second result
// set.
      FETCH emp_proc2 INTO :part_name_var;

// Loop through all rows in the second result

// set.
DO WHILE sqlca.sqlcode = 0

// Fetch the next row from the second result

// set.
      FETCH emp_proc2 INTO :part_name_var;
LOOP


so once u execute the proc u need to've a loop as shown above and u could then set the valus in  a external dataobject since the fetch is givin u the values

0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13589463
hi,

if we have a real complicated SP and need the data in a DW and cannot do it by making the SP as a datasource we normally make a table and insert values of our final select statement of the SP to that table...
next its the simple task of making that table the datasource of our DW...

this case is normally used in scenerios where there is a batch of data... this data can
be inserted into the new table... then it gets easy to retrieve data with other retrieval
arguments... the call for what approach u take is all dependent on ur Business Functionality


Cheers,
Rosh
0
 
LVL 33

Expert Comment

by:sajuks
ID: 13589474
would using the loop to insert it into a datawindoiw be a simpler and effective solution ?
0
 

Author Comment

by:Mateen
ID: 13589487
Hi Rosh:

I don't want any select. Any pb scripting. The SP is giving me results with all the columns.
I want to call it into datawindow with store procedure option like
Create datawindow->Sql Select->Store Procedure and
then ??????????????
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13589493


well load on the backend is a better option then doing it from Powerbuilder using a loop...
theres no doubt that it wud give better performance than looping...
in mateens case i prefer inserting values thru the SP into a table and then creatin a DW of it...
what do u think sajuks...

Cheers,
Rosh
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13589502
Create datawindow->Sql Select->Store Procedure and
then ??????????????


then select ur SP...

thats it,,, rest is self explanatory... unless u get an error...

Cheers,
Rosh
0
 

Author Comment

by:Mateen
ID: 13589503
Hi Sajuks:

<<would using the loop to insert it into a datawindoiw be a simpler and effective solution ?>>
Why such things.

My SP give complete results with all the columns needed. (The result has been pasted in the question). Why one cannot call this result into datawindow.


0
 
LVL 33

Expert Comment

by:sajuks
ID: 13589507
rosh the problem that i've is in creating one more temporary table. now if there are more than one users whoc execute the same thing then that means we've to take that into consideration when retriveing from the temp table. so a insertinto is much faster and efficient

0
 

Author Comment

by:Mateen
ID: 13589521
Hi Rosh:

I selected my SP , there I see this line in bottom box
<<The database does not currently support retrieval of procedure text.>>
Further there are two check boxes. System Procedure and Manual Result Set.
How to go ahead.
0
 
LVL 33

Expert Comment

by:sajuks
ID: 13589522
"would using the loop to insert it into a datawindoiw be a simpler and effective solution ?"
shodul be read as
wouldnt using the loop to insert it into a datawindoiw be a simpler and effective solution ?

create new datawindow>sql select > procedure > ur procedure name shoudl work

in ur pb script as i mentioned earlier u just need to pass the date argument .
are u getting any errors ?
the sp will be executed with the date as an argument. thats it
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13589531
well.... the temp table is for that particular session only... so no issue abt other users i guess...
0
 
LVL 33

Expert Comment

by:sajuks
ID: 13589540
" well.... the temp table is for that particular session only... so no issue abt other users i guess..."
are you talking about hash tables or permanet temp tables ?
0
 
LVL 33

Expert Comment

by:sajuks
ID: 13589554
"Further there are two check boxes. System Procedure and Manual Result Set."
can u see the procedure list ?
ensure none of the checkboxes are seelcted
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13589560


im talkin abt the hash tables...


well from a little search i just came across this link.... surprisingly its namasi helping out 'Mateen' !

mateen... just check an older post of urs... i guess that helped u...

let me know if u need more assistance..

Cheers,
Rosh
0
 

Author Comment

by:Mateen
ID: 13589580
Dear experts:
My problem is that
I dont know how to create a datawindow with store procedure as its datasource.
I agree it must be easy. For me it is difficult because Whenever I have tried I have failed
and left the matter for some other time. This time I don't want so.
I have already specified my fear in my question.
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13589581
0
 
LVL 33

Expert Comment

by:sajuks
ID: 13589583
are u using odbc drivers ? u need to switch to native drivers to get full support for sp as datasource
0
 

Author Comment

by:Mateen
ID: 13589591
Hi Rosh:
<<surprisingly its namasi helping out 'Mateen' >>
I already cleared in my question
<<I have asked it before , I have got answer, the answer must have been ok,
but unfortunately I have yet not been able to do this. If the same
happens this time, please be patient with me.>>

0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13589596
ok...

this time we're gonna see to it that u create ur DW with the SP and no work around...

so ur gettin the error...
<<The database does not currently support retrieval of procedure text.>>

Also, make sure "manual result set" option is NOT CHECKED when associating your stored proc with the procedure.

Cheers,
Rosh
0
 

Author Comment

by:Mateen
ID: 13589606
Hi Sajuks:
<<are u using odbc drivers ? u need to switch to native drivers to get full support for sp as datasource >>
I am using odbc drivers currently. I am ready to switch to native driver. Then??

0
 
LVL 33

Expert Comment

by:sajuks
ID: 13589619
then u shouldnt be facing any problems.
once u r able to select the procedure , u will see the paarameters which u'd defined which u can format ( like u do with normal dw)
0
 

Author Comment

by:Mateen
ID: 13589627
Hi rosh:
the datawindow created!!!

When clicking retrieve button, retrieval box opened,
wrote '2005-03-17'  and clicked ok. Getting error

"Select errror 22005
Microsoft ODBC SQL SERVER DRiver Invalid character value for cast specification"
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13589628
also mateen hope u have the latest patch of the PB version that ur using... thats what i always recommend and it helps!!!
btw,
i just came across some writeups that talked abt using native drivers for full support... i guess u gotta take sajuks suggestion and give it a try...

Cheers,
Rosh
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13589637
well,

the datawindow created!!!

thats the first break thru...

now its just abt some small tuning... be right back...

Cheers,
Rosh
0
 

Author Comment

by:Mateen
ID: 13589638
Hi sajuks:
Do U want to say I wouldnt get this error
"Select errror 22005
Microsoft ODBC SQL SERVER DRiver Invalid character value for cast specification"
if i use native driver.
Meanwhile u answer, I am testing.....
0
 
LVL 33

Accepted Solution

by:
sajuks earned 1000 total points
ID: 13589649
Set the DBParm StripParmNames='Yes' and CallEscape='No' when connecting to the database.
0
 
LVL 33

Expert Comment

by:sajuks
ID: 13589664
ur connc profile would lok something like this
SQLCA.DBMS = "ODBC"
SQLCA.AutoCommit = False
SQLCA.DBParm = "ConnectString='DSN=My-SQL;UID=sa;PWD=abc',StripParmNames='Yes',CallEscape='No' "
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13589669
but... which version on PB do u have mateen...
0
 

Author Comment

by:Mateen
ID: 13589701
Hi Sajuks:
Set the Dbparms as u suggested.
Whe I create dw without checking manual resultset I get this error before the dw is created.
Invalid character value for cast specification.

1 execute dbo.usp_lowfatspread; 1 @input=:input

when I create dw with checking manual resultset the dw is created but the retrieve option
is dimmed or disabled.

So dw is halfly created.

0
 
LVL 33

Expert Comment

by:sajuks
ID: 13589720
" but the retrieve option is dimmed or disabled."
what retrieve option r u talking about ?
0
 
LVL 33

Expert Comment

by:sajuks
ID: 13589737
if u run this from ur application do u get any errors ?
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13589740
hi,

i feel its got something to do with ur identity column here...
SELECT LOOM_NO,AT_DATE_TIME,CASE WHEN SUB_STATUS_CODE = 8 THEN -1 ELSE SUB_STATUS_CODE END
         AS sTATUS
      ,IDENTITY(INT,1,1) AS ROWNO
  INTO #TEMP
  FROM Loom_Pick_Count
 wHERE AT_DATE_TIME BETWEEN @START AND @END
   AND SUB_STATUS_CODE IN (1,2,4,8,16,64,127,128)
 ORDER BY LOOM_NO,at_DATE_TIME

just for testing try without using the identity function...

Cheers,
Rosh
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13589863
hi mateen,

does ur SP compile properly...


Cheers,
Rosh
0
 

Author Comment

by:Mateen
ID: 13589896
Hi experts
As suggested by sajuks
I changed the dbparms but did not reconnect ODBC previously.
I restarted the machine to see everything working fine.

0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13589900
hi,

just went thru ur procedure again and felt something wrong with the first few lines...

DECLARE @START DATETIME
dECLARE @END DATETIME

SELECT @START=CONVERT(CHAR(11),@INPUT)
      ,@END=cONVERT(CHAR(11),@iNPUT) + ' 23:59:59PM'

u have decalred variables @start and @end as datetime but,
using convert u are settin char to them...

u might need to use ,
DECLARE @START DATETIME
dECLARE @END DATETIME
DECLARE @ST DATETIME
dECLARE @ED DATETIME


SELECT @ST=CONVERT(CHAR(11),@INPUT)
      ,@ED=cONVERT(CHAR(11),@iNPUT) + ' 23:59:59PM'

SELECT @START=CAST( @ST AS DATETIME )
      ,@END=CAST( ,@ED AS DATETIME )

instead of the above lines....
i guess this might be the issue but not sure...

Cheers,
Rosh
0
 
LVL 33

Expert Comment

by:sajuks
ID: 13589946
"I restarted the machine to see everything working fine."
seems to happen a lot with microsoft OS
0
 

Author Comment

by:Mateen
ID: 13589994
Thanks sajuks
0
 

Author Comment

by:Mateen
ID: 13590036
Hi Dia:

Its ok now.
Thanks 4 remaining so long.

<< BTW. the procedure should be ok as it is written by EE champion lowfatspread>>

0
 
LVL 1

Expert Comment

by:kathotia
ID: 14104001
Hi sanjuks,

I am also facing the same prole f error 22005. I am using PB 10.2 build 8011 and ADO .NET for connecting to theSQL Server.

He datawindow retrives properly when i am editing it or if it is in preview mode. However the error creeps up when it is retrieved throught powerscript dw_1.retieve(datetiem_variable_name) . I tried changing the parameter varable to string in place of datetime but still the same problem. However this same datawaindo runs flawlessly when using PB 6.51 and MSS driver to access the data.

Can you help.

Regards
kathotia
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question