question about store procedure to return @@indentity

[mmEllen]

Our application just is upgrating to ColdFusion 2018. We have a store procedure

CREATE PROC sp_cs_set_emp_qual

@pbn_SRI varchar(15),

@EmP_ID decimal(18,0),

@availability_status varchar(15) = null,

@EmP_lname varchar(50),

@EmP_fname varchar(50),

@EmP_init varchar(50),

@academic_qual varchar(250),

@Dup_emp_flag int,

@added_emp_ID decimal(18,0) OUTPUT

AS

  BEGIN

        INSERT INTO employee

                                        (   pbn_SRI,

                                            availability_status,

                                                emp_lname,

                                                emp_fname,

                                                emp_init,

                                                academic_qual,

                                                duplicate_ind

                                        )

        VALUES

                       (    @pbn_sri,

                                        @availability_status,

                                                @EmP_lname,

                                                @EmP_fname,

                                                @EmP_init,

                                                @academic_qual,

                                                @Dup_emp_flag

                                        )

SELECT @added_emp_ID = @@IDENTITY

END

With the ColdFusion 2018 developing edition, after runing this store procedure, the record has been insert into the table, but the @added_emp_ID return empty. It was working well with the ColdFuion 9. Anyone has an idea about this?

[WolfShade]

Hello, mmEllen,

This should still work.  I'm not sure why it isn't.  But going from CF9 => CF2018 is a _HUGE_ jump, and you're bound to have issues.

If you can't fix this, then I would suggest having CF create the UUID (I assume you're using UUID/GUID) and pass that to the Stored Procedure.  That way, you'll have it before it gets inserted into the database, and can reference that for further processing.

HTH,

^ _ ^

[Charlie Arehart]

Or, taking a different tack: how are you calling the SP? with cfstoredproc? And with a cf procresult? And that's unchanged, I assume?

Note that CF DSN's now offer a way to control whether a cfquery or cfstoredproc call should cause CF to have the identity col value returned automatically, and you can even control that for a cfquery with its new disableAutoGenKeys attribute. I wonder if that setting (in the DSN) may be impacting the value you are (not) getting back from the SP call.

[mmEllen]

This is the way the store procedure is called. How to setup CF DNS to make identity col value returned automatically?

<CFSTOREDPROC PROCEDURE="sp_cs_set_emp_qual" DATASOURCE="#APPL_DATA#">

<CFPROCPARAM CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="15" TYPE="In" VALUE="#trim(request.session.firmInfo.pbn_SRI)#" DBVARNAME="@pbn_sri" NULL="No">

<CFPROCPARAM CFSQLTYPE="CF_SQL_DECIMAL" TYPE="In" VALUE="#trim(request.session.emp_ID)#" DBVARNAME="@emp_ID" NULL="No">

<CFPROCPARAM CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="15" TYPE="In" VALUE="#trim(request.session.emp_qual.availability_status)#" DBVARNAME="@availability_status" NULL="No">

<CFPROCPARAM CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50" TYPE="In" VALUE="#trim(request.session.emp_qual.emp_lname)#" DBVARNAME="@emp_lname" NULL="No">

<CFPROCPARAM CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50" TYPE="In" VALUE="#trim(request.session.emp_qual.emp_fname)#" DBVARNAME="@emp_fname" NULL="No">

<CFPROCPARAM CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="1" TYPE="In" VALUE="#trim(request.session.emp_qual.emp_init)#" DBVARNAME="@emp_init" NULL="No">

<CFPROCPARAM CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="255" TYPE="In" VALUE="#trim(request.session.emp_qual.academic_qual)#" DBVARNAME="@academic_qual" NULL="No">

<CFPROCPARAM CFSQLTYPE="CF_SQL_INTEGER" TYPE="In" VALUE="#trim(request.session.emp_qual.dup_emp_flag)#" DBVARNAME="@dup_emp_flag" NULL="No">

<CFPROCPARAM CFSQLTYPE="CF_SQL_DECIMAL" TYPE="Out" DBVARNAME="@added_emp_ID" VARIABLE="request.session.added_employee_ID">

</CFSTOREDPROC>

[mmEllen]

I just checked the CF DNS' setting. "Disable retrieval of autogenerated keys" is not Checked. Is there another way to control retrieval of autogenerated keys?

[Charlie Arehart]

Well, I had said above that there WAS a way to disable that, via cfquery. Sadly, for some reason, that is NOT offered for cfstoredproc. So the only way to control it (to test if changing it will help) would indeed be to change it in the CF Admin DSN setting.

But I am wondering now whether that is really the problem. Maybe it is, but maybe not. Now that you have shared your code that calls the SP, I see that you expect the output of the call to the SP to write this identit value into a variable called request.session.added_employee_ID. And so when you say, " the @added_emp_ID return empty", do you really mean that this request.session.added_employee_ID was empty?

And if so, can you do a cfdump var="#request.session#", to see what all the other values are, and is this the only one that's "empty"? I'm just curious to know, before we dig further.

[mmEllen]

Yes, I mean request.session.added_employee_ID empty.

I did cfdump var="#request.session#",  only request.session.added_employee_ID empty.

By the way, it's sybase store procedure.

[Charlie Arehart]

OK, so did you try the first suggestion I'd made there, to see what happens if you change it in the CF Admin DSN setting?

[mmEllen]

 CF admin DNS' setting, now the setting "Disable retrieval of autogenerated keys" is not Checked. D you want me to make it checked?

[Charlie Arehart]

yes

[mmEllen]

Still not working.

[Charlie Arehart]

OK, It may well be that your next stop is to look into things in the dbms itself, to see what it's doing when you run this SP from CF. You said you're running sybase. I have to admit that I've not worked with that in decades.

There are tools available to see such level of detail in sql server (which it split off from 27 years ago), and mysql. I suspect there is something like that for sybase. You just really need to see what is happening there in the sp call, to see whether it is returning the needed id.

And to be clear, I don't mean "run the sp in sybase". I suspect you already did that. I really am meaning to see some sort of profiling in sybase for WHEN it's called from CF, to see how things differ.  You're saying that the problem is in what you get back, right? That's why I now wonder what's going on in the dbms.

One last thing: are you using the sybase driver that comes with CF? or an "other" driver that points to some JDBC driver that someone implemented there?

[mmEllen]

The JDBC drive is used instead of sybase drive because we tried sybase drive, but always has another problem,

[WolfShade]

Have you reached out to Sybase to see if they have an updated driver that will work with CF?

HTH,

^ _ ^

[mmEllen]

No, I haven't. I will  contact Sybase. Thanks

[WolfShade]

Also, one more thing.  I just looked at the compatibility matrix, and according to it, only the Enterprise version of CF 2018 works with Sybase.  Are you using Enterprise, or Standard?

HTH,

^ _ ^

UPDATE:  Kind of a dumb question, now that I think about it.. the insert wouldn't work if you were using Standard.  n/m

[mmEllen]

Actually we plan to buy it, but before the bugets get approved, we want to working on it at the same time, so download the trail version. OK, we are going to wait. Thanks

[Charlie Arehart]

Two things, on all this, folks.

First, Wolf, I would read that issue about Sybase and CF Enterprise as not so much about whether CF supports it but rather, about how Adobe does not BUNDLE a Sybase driver in CF Standard. I realize that's not what it says. But CF has always supported the "other" driver option in the setup of a CF DSN, and you can point to a JDBC driver, and generally things should work.

And second, mmEllen, that is why I asked above about whether you were using the Sybase driver that came with CF or one that you or someone had downloaded. You answered: "The JDBC drive is used instead of sybase drive because we tried sybase drive, but always has another problem,"

So since you are using the Developer edition of CF2018, that DOES give you access to CF Enterprise features, like that sybase driver. More on that in a moment. But when you say "The JDBC drive is used instead of sybase drive", I assume you do mean that you or someone chose the "other" driver type for the DSN in question. If so, what is the JDBC driver you ARE using? You can see that in the classname field for such an "other driver" type in the CF Admin DSN page.  (And someone would have had to copy a sybase jdbc jar into CF2018 for that to work.  Are you really sure that's what you did? I am just going on what you said above.)

Further, when you say, "we tried sybase drive, but always has another problem", that "always" makes it seem you are referring to CF9. (So you did have CF Enterprise in 9?). And even if you had a problem with the built-in Sybase one back then, have you checked to see whether it DOES work for what you are trying?

And if it DOES work, then you should check to see if these "other problems" still happen. Maybe they do not, and so maybe you CAN  use the built-in Sybase driver--though again you would need to put in a CF2018 Enterprise license key when you want to go into production, as I'm sure you realize you can't use that Developer edition for production.

Finally, if the problem here DID go away with that built-in Sybase driver, but somehow the "other problem" you refer to did not, I'll point out that you can have TWO dsn's that point to the SAME database. One could be using the built-in sybase driver, and another could be using a JDBC "other" driver. They could have different DSN names, and you could use each DSN name in the code that wants to do what "works" with that driver.

I know that's all a lot to take in. Just trying to help you get to a clear understanding of things, and perhaps ao solution.

[mmEllen]

We chose the other "other" from the Driver Selection, and in the driver class we put "com.sybase.jdbc4.jdbc.SybDriver"

We do have CF9 Enterprise liscence. For this one, We don't  have the probem with the build-in sybase driver. Everything is working fine and our applications are still running on CF9. When we tried to promote our applications to CF2018 rencently, found out that our application can't even start running with the build-in sybase drive in CF2018 trail version, so we tried JDBC drive. we will buy CF2018 Enterprise license. Thank you!

[mmEllen]

When we use build-in sybase drive in CF2018 trail version, we get error

Error Executing Database Query.

[Macromedia][Sybase JDBC Driver]The stored procedure call could not be made as a language event, due to the presence of one or more output-only parameters. It is recommended that parameter markers be used for all stored procedure arguments.

[Charlie Arehart]

As for this issue with the sybase built-in driver, I assume that's about some other SP call that you are not showing here (not the CFPROCPARAMS nor the SP itself). But the error seems to be saying that there's some mismatch in the cfprocparams and the SP arguments. 

Someone in your workplace may have been frustrated that that failed, and dismissed it as a bug. But note that CF11 re-introduced CF's honoring what is in the dbvarname (it was ignored from CF6 to CF11, so was ignored in CF9 when you ran that).

It may just be a matter of cleaning things up between the cfprocparam and the sp args. Even if you may be saying that this other problem "doesn't happen" with the JDBC driver (you haven't said that), it could still be worth your time and effort to resolve whatever is amiss with this other issue. Hope the info here will help you do that.

[Charlie Arehart]

Finally (for tonight), if somehow none of the above works, then going back to the initial problem you've raised here (of it not returning the inserted identity key value), I will remind you that I had proposed several messages ago that the next step may be to do some analysis ON SYBASE to find out what is being generated when the SP runs there, as called from CF. For more, see the points I made back then:  https://community.adobe.com/t5/coldfusion/question-about-store-procedure-to-return-indentity/m-p/110...

[Charlie Arehart]

OK, so that's a sybase driver you are using. The next question would be what version is it?

Whoever implemented it into CF should be able to tell you. Since it has the problem you reported initially here, it may be that it's an old one (that they had used with CF9), which may no longer work. Or it may be a new one that's perhaps TOO new for CF.  

[BKBK]

Hi mmEllen

I suspect this is a driver issue. If my suspicions are right, then you are on the right track.

But then, you need the 100% Java driver for Sybase Enterprise version 16. Why version 16? That is the version that ColdFusion 2018 supports. 

1)  The driver file you actually need is jconn4.jar. Search on the web and find where to download it. It is usually located in a package called  jConnect-16_0.

Copy the JAR file to your lib directory (C:\ColdFusion2018\cfusion\lib), then restart ColdFusion.

Now, return the settings you have been using:

2) "other" as driver selection;

3) "com.sybase.jdbc4.jdbc.SybDriver" as diver class;

Does it now work as expected? My reasoning follows.

Probable cause of the issue: by default, ColdFusion 2018 uses its in-built driver 

class	macromedia.jdbc.MacromediaDriver
port	5000

 for Sybase. This driver might be incompatible.

Additional info: I used the following code to see which drivers ColdFusion 2018 uses:

<!--- Login into Coldfusion Administrator. --->
 <cfset  createObject("component","cfide.adminapi.administrator").login("my_CFAdmin_Password")>

<!--- Instantiate the data source object. --->
 <cfset  datasourceObject = createObject("component","cfide.adminapi.datasource")>
 
 <!--- Get a structure containing all the data sources --->
  <!--- <cfset datasources = datasourceObject.getDatasources()>--->
 
 <!--- Get a structure containing all the database drivers --->
 <cfset databaseDrivers = datasourceObject.getDriverDetails()>
 
<cfdump var="#databaseDrivers#" >