Direct mode and stored procs with prepare

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
feamster
Posts: 24
Joined: Thu 11 Nov 2004 12:59

Direct mode and stored procs with prepare

Post by feamster » Thu 19 Feb 2015 17:53

An easy way to recreate this is place a storedproc component pointing to a know working storedproc on a sql server on a dataset and try to add field defs.

I'm using sql 2008 and will shortly go to 2014

DBMONITOR results

When doing a sqlprepare in direct mode on a stored procedure I get an exception from the server.
Incorrect syntax near '{'

The call is below.
{:RETURN_VALUE = CALL LoadCont14;1 (:MONTH, :DAY, :BPNO, :BPNAME, :CONTRACT, :PTYPE, :STATE1, :COUNTY1, :FARM1, :NDX1, :LBS1, :IACRES1, :NIACRES1, :ACRES1, :AVG1, :STATE2, :COUNTY2, :FARM2, :NDX2, :LBS2, :IACRES2, :NIACRES2, :ACRES2, :AVG2, :STATE3, :COUNTY3, :FARM3, :NDX3, :LBS3, :IACRES3, :NIACRES3, :ACRES3, :AVG3, :STATE4, :COUNTY4, :FARM4, :NDX4, :LBS4, :IACRES4, :NIACRES4, :ACRES4, :AVG4, :STATE5, :COUNTY5, :FARM5, :NDX5, :LBS5, :IACRES5, :NIACRES5, :ACRES5, :AVG5, :STATE6, :COUNTY6, :FARM6, :NDX6, :LBS6, :IACRES6, :NIACRES6, :ACRES6, :AVG6, :STATE7, :COUNTY7, :FARM7, :NDX7, :LBS7, :IACRES7, :NIACRES7, :ACRES7, :AVG7, :STATE8, :COUNTY8, :FARM8, :NDX8, :LBS8, :IACRES8, :NIACRES8, :ACRES8, :AVG8, :P1ID, :P1SHARE, :P1FNAME, :P1LNAME, :P1ADDR1, :P1CITY, :P1STATE, :P1ZIP, :P2ID, :P2SHARE, :P2FNAME, :P2LNAME, :P2ADDR1, :P2CITY, :P2STATE, :P2ZIP, :P3ID, :P3SHARE, :P3FNAME, :P3LNAME, :P3ADDR1, :P3CITY, :P3STATE, :P3ZIP, :P4ID, :P4SHARE, :P4FNAME, :P4LNAME, :P4ADDR1, :P4CITY, :P4STATE, :P4ZIP, :REM1, :REM2, :CTYPE, :SEED, :SIGNED, :VOID, :L1NAME, :L1ADDRESS, :L1NATURE, :L1CHECK, :L2NAME, :L2ADDRESS, :L2NATURE, :L2CHECK, :STATUS, :TYPE400, :XCOUNTY, :XSTATE, :DATE, :CROPYR, :CONTDATE, :OPT, :PRICE, :CORP, :CHECK1, :AMT1, :CHECK2, :AMT2, :CHECK3, :AMT3, :CHECK4, :AMT4, :CORRECTED, :CORRECTDT, :XMITTED, :XMITDATE, :REDEMPTION, :CODE1, :CODE2, :CODE3, :SEEDPREM, :MINSMK, :SEEDCLASS, :OPTADVPAID, :OPTADVREPAID, :SEG, :DEFERRED, :DEFER1, :DEFER2, :DEFER3, :DEFER4, :DEFER5, :DEFER6, :DEFER7, :DEFER8, :DMA1, :DMA2, :DMA3, :DMA4, :DMA5, :DMA6, :DMA7, :DMA8, :conversion)}

When in prAuto mode it works fine then:

{:RETURN_VALUE = CALL LoadCont14;1 (:MONTH, :DAY, :BPNO, :BPNAME, :CONTRACT, :PTYPE, :STATE1, :COUNTY1, :FARM1, :NDX1, :LBS1, :IACRES1, :NIACRES1, :ACRES1, :AVG1, :STATE2, :COUNTY2, :FARM2, :NDX2, :LBS2, :IACRES2, :NIACRES2, :ACRES2, :AVG2, :STATE3, :COUNTY3, :FARM3, :NDX3, :LBS3, :IACRES3, :NIACRES3, :ACRES3, :AVG3, :STATE4, :COUNTY4, :FARM4, :NDX4, :LBS4, :IACRES4, :NIACRES4, :ACRES4, :AVG4, :STATE5, :COUNTY5, :FARM5, :NDX5, :LBS5, :IACRES5, :NIACRES5, :ACRES5, :AVG5, :STATE6, :COUNTY6, :FARM6, :NDX6, :LBS6, :IACRES6, :NIACRES6, :ACRES6, :AVG6, :STATE7, :COUNTY7, :FARM7, :NDX7, :LBS7, :IACRES7, :NIACRES7, :ACRES7, :AVG7, :STATE8, :COUNTY8, :FARM8, :NDX8, :LBS8, :IACRES8, :NIACRES8, :ACRES8, :AVG8, :P1ID, :P1SHARE, :P1FNAME, :P1LNAME, :P1ADDR1, :P1CITY, :P1STATE, :P1ZIP, :P2ID, :P2SHARE, :P2FNAME, :P2LNAME, :P2ADDR1, :P2CITY, :P2STATE, :P2ZIP, :P3ID, :P3SHARE, :P3FNAME, :P3LNAME, :P3ADDR1, :P3CITY, :P3STATE, :P3ZIP, :P4ID, :P4SHARE, :P4FNAME, :P4LNAME, :P4ADDR1, :P4CITY, :P4STATE, :P4ZIP, :REM1, :REM2, :CTYPE, :SEED, :SIGNED, :VOID, :L1NAME, :L1ADDRESS, :L1NATURE, :L1CHECK, :L2NAME, :L2ADDRESS, :L2NATURE, :L2CHECK, :STATUS, :TYPE400, :XCOUNTY, :XSTATE, :DATE, :CROPYR, :CONTDATE, :OPT, :PRICE, :CORP, :CHECK1, :AMT1, :CHECK2, :AMT2, :CHECK3, :AMT3, :CHECK4, :AMT4, :CORRECTED, :CORRECTDT, :XMITTED, :XMITDATE, :REDEMPTION, :CODE1, :CODE2, :CODE3, :SEEDPREM, :MINSMK, :SEEDCLASS, :OPTADVPAID, :OPTADVREPAID, :SEG, :DEFERRED, :DEFER1, :DEFER2, :DEFER3, :DEFER4, :DEFER5, :DEFER6, :DEFER7, :DEFER8, :DMA1, :DMA2, :DMA3, :DMA4, :DMA5, :DMA6, :DMA7, :DMA8, :conversion)}

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Direct mode and stored procs with prepare

Post by azyk » Mon 23 Feb 2015 09:43

Thank you for the information. We have already fixed this problem. This fix will be included in the next SDAC build.

feamster
Posts: 24
Joined: Thu 11 Nov 2004 12:59

Re: Direct mode and stored procs with prepare

Post by feamster » Mon 23 Feb 2015 14:29

That's super! So when will the next build be released? It's been more than a month.

Thanks

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Direct mode and stored procs with prepare

Post by azyk » Tue 24 Feb 2015 11:27

The next SDAC build is expected in the next month.

feamster
Posts: 24
Joined: Thu 11 Nov 2004 12:59

Re: Direct mode and stored procs with prepare

Post by feamster » Mon 20 Apr 2015 21:06

So I have the new version, after I've done the prepare, when I do a storedprocedurecomponentname.fielddefs.update I get the following error:

SQL statement doesn't return rows.

Any ideas. I'm matching up field names in the stored proc with field names in my local table

When I comment out that line I get Incorrect syntax near '{'

I thought this was fixed in the new version

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Direct mode and stored procs with prepare

Post by azyk » Thu 23 Apr 2015 09:12

Unfortunately, we couldn't reproduce the "SQL statement doesn't return rows" error message. Please try to compose a small sample to demonstrate the problem. Include the database objects creating scripts and send it to andreyz*devart*com .

Post Reply