Sybase stored procedure Error

Report · Mar 12, 2020

Just upgrade from ColdFusion 9 to ColdFusion 2018. A sybase stored procedure ran successfully in ColdFusion 9, but get error in ColdFusion 2018.

The code to call the stored procedure

</cfstoredproc>

The stored procedure :

CREATE PROC sp_cm_set_project_details @REF_proj_exist varchar(5)="no",
@REF_proj_id numeric(18,0)=0,
@REF_proj_name varchar(80),
@REF_proj_location varchar(50),
@REF_proj_city varchar(30),
@REF_proj_prov varchar(2),
@REF_proj_value numeric(10,2),
@REF_proj_dt_cmpltd datetime,
@REF_proj_client varchar(50),
@REF_proj_contact varchar(80),
@REF_proj_cntct_tel varchar(14),
@REF_proj_desc varchar(255),
@REF_proj_id_out numeric(18,0)=0 output

AS

IF @REF_proj_exist = "yes"
BEGIN

/* update reference project*/

      UPDATE reference_project SET
   ref_proj_name = @REF_proj_name,
   ref_proj_location = @REF_proj_location,
   ref_proj_city = @REF_proj_city,
   ref_proj_prov = @REF_proj_prov,
   ref_proj_value = @REF_proj_value,
   ref_proj_dt_cmpltd = @REF_proj_dt_cmpltd,
   ref_proj_client = @REF_proj_client,
   ref_proj_contact = @REF_proj_contact,
   ref_proj_cntct_tel = @REF_proj_cntct_tel,
     ref_proj_desc     = @REF_proj_desc
   WHERE ref_proj_id = @REF_proj_id

END

ELSE

BEGIN

      INSERT INTO reference_project
   (
   ref_proj_name,
   ref_proj_location,
   ref_proj_city,
   ref_proj_prov,
   ref_proj_value,
   ref_proj_dt_cmpltd,
   ref_proj_client,
   ref_proj_contact,
   ref_proj_cntct_tel,
   ref_proj_desc
   )
   VALUES
     (
  @REF_proj_name,
  @REF_proj_location,
  @REF_proj_city,
  @REF_proj_prov,
  @REF_proj_value,
  @REF_proj_dt_cmpltd,
  @REF_proj_client,
  @REF_proj_contact,
  @REF_proj_cntct_tel,
  @REF_proj_desc
  )

SELECT @REF_proj_id_out = @@identity

END

This storde precedure runs ok in Sybase, but not working in ColdFusion 2018

Anyone can help?

Report · Mar 12, 2020

The error code:102

The error type: database

The error message: Incorrect syntax near ')'.

Report · Mar 12, 2020

Ellen, this may be a tricky one. Someone may think they know the answer (I did at first), but as I will explain, it's not being caused by what I originally suspected. But I do offer several things for you to consider, either to better diagnose the problem, or to "try to get it to work" (always a bit risky, but I share concerns to consider).

On first reading, I would have put my bet on the "feature" that was "revived" in an update of CF11, whereby that version and all later ones now HONOR the dbvarname in the cfprocparam, and as such you must get it exactly right. Also, if the DB required a prefix for the proc variable now, it must be specified.

But I see you do. 🙂

For more background (in case somehow this is still a factor, or for others reading along), the issue is that in CF 8, 9, and 10 the dbvarname was ignored, though BEFORE that it HAD BEEN honored. And now as of an update in CF11, forward, it is AGAIN being honored, and must be correct.

But the point is that it would not be unusual for one to have had code in place for years (decades), and it was "right" when first written (perhaps written when the dbvarname was required in versions prior to CF7), but now it's not, if something changed in the SP or DB. (And while CF11 had added a JVM arg allowing that to be "ignored" again, that JVM arg was not carried forward into CF2016 and beyond.)

But again, I did a check for you, to make sure that all the dbvarnames DO match exactly the current SP var names, and they do. And I see that you had an @ prefix in both the SP definition and in the cfprocparam.

And I know you said that the SP had "worked" in Sybase, but of course you would have had to setup some other way of calling it, and there could have been some difference in how THAT called it and how your code above calls it. But we'll assume that is not the issue.

So I am a bit stumped. Maybe someone else will chime in with an idea or solution. If they have none, here's what I would do next if I were in your shoes. I'd create a VERY simple SP in sybase, first taking NO args, and make sure CF can call it ok. Then I'd add one arg to the SP, and have CF pass that in. If it worked when the SP did not require any but failed when it did, at least we know it's about that issue.

The next thing to try would be to see if you can remove the dbvarname from the procparam. My recollection was that as long as you provide the cfprocparam's in the order that the SP defines them, then you don't NEED the dbvarname. Of course, this makes the call to be tied to that SP definition, which is brittle. And you may not want to have to make such a change to tens or hundreds or more such calls. I'm just grasping at straws here.

If you really want to dig into what's going on, it would next be interesting to find out exactly what CF is passing to the DB (when you use the dbvarname, and it fails--even and especally on that simple test case I proposed). You can see that generated SQL various ways:

in the DB (with the right db tools)
with a tool like FusionReactor (which tracks all communications out of CF to the DB)
by enabling the "log" feature in the CF Admin DSN "advanced settings", which will create VERY verbose logging, but may show you the answer.

One more "hail mary": if you look at the CF Admin for the DSN, and those "advanced settings", is the value for "Disable retrieval of autogenerated keys" unchecked? It is by default. What if you checked it? I just wonder if it could be getting in the way of your SP call. (If this is prod, you would not want to make that change, if any of your CF code relies on that feature, which as added in like CF 8 to return the value of any auto-generated primary keys, for SQL an insert.) But if you either know you don't use that feature, or this is a test box, it should be a quick, simple test to see what happened if you turned it off, then you could turn it back on.

Similarly, at the bottom of the DSN "advanced settings" there is a "Enable connection validation" option. Is it enabled? I think it is disabled by default, but if someone did enable it, perhaps it's having an unexpected effect on the SP call. Again, I'm just wondering if the error would go away if you turned it off.

If the error DID go away with either change, note that you could create a NEW DSN in the CF Admin, and give it a different name, and use THAT DSN for the CFSTOREDPROC calls that are failing. At least then all other query processing from CF could use the original DSN, unchanged.

With that, I'm out of ideas for now. Or again, perhaps someone else will chime in with a better explanation or solution.

/Charlie (troubleshooter, carehart.org)

Adobe Community

Sybase stored procedure Error