Skip navigation
HiTopp
Currently Being Moderated

Using Client Variables When Calling Stored Procedure

Mar 21, 2012 8:55 AM

Tags: #coldfusion9 #stored_procedure #cfstoredproc #client_management

I have a ColdFusion function that calls two stored procedures.  The first stored procedure ends up inserting data into a remote database and the second stored procedure calls a remote procedure on a remote database.

 

Each are called using a block of code identical to the code below:

 

variables.storedProcService = new storedProc();

variables.storedProcService.clear();
variables.storedProcService.setAttributes(datasource="DATASOURCE", procedure="PROCEDURE");
variables.storedProcService.execute();

 

When I run this function, the calls fail.  The error I receive is [Macromedia][SQLServer JDBC Driver]The DBMS returned an unspecified error. The command code was 224. This isn't very helpful, so I had an associate of mine test the function with his ColdFusion environment and it worked just fine.  Comparing the two environments, I noticed that he was using the Windows Registry to store client variables, while I was using a database.  So I switched to the Windows Registry and the two calls worked just fine.  However, I don't want to use the Windows Registry because the current system I support uses a database.

 

Why would this work with the Windows Registry and not the database?  I was under the impression that the same information was stored regardless of the storage mechanism.

 
Replies
  • Currently Being Moderated
    Mar 23, 2012 1:45 AM   in reply to HiTopp

    Do you get the same error if you just use <cfstoredproc>?  Is the proc hitting the same DB / DSN as the client variables are stored in (/accessed via ~)?  Maybe set up a specific DSN for the client store, and see if that sorts it out?

     

    It's certainly a weird one.

     

    As for the "[Macromedia][SQLServer JDBC Driver]The DBMS returned an unspecified error. The command code was 224" being not very helpful: CF can only report back what the DB says.  If the DB doesn't return anything helpful then one cannot blame CF.

     

    Did you google up what a SQL Server error 224 is?  that might give a clue.

     

    Do you actually use client variables?  Most people don't, I find.  So you could always just set clietn storage to "none".

     

    --

    Adam

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 25, 2012 1:02 AM   in reply to HiTopp

    Two points. Firstly, since the issue shows up when you move from registry to database, it might have to do with authentication. What happens when you do something like this?

     

    variables.storedProcService.setAttributes(datasource="DATASOURCE", procedure="PROCEDURE", username="db_user", password="db_pword");

     

    Secondly, I see no need for variables.storedProcService.clear();

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 25, 2012 11:08 PM   in reply to HiTopp

    HiTopp wrote:

     

    When I try this:

    variables.storedProcService.setAttributes(datasource="DATASOURCE", procedure="PROCEDURE", username="db_user", password="db_pword");

     

    The two calls do work just fine, but I was hoping to not include the credentials in my source code.  If including the credentials with the call works, what does that mean to you?  Authentication still?  I am using the same credentials that are included as a part of the datasources in Jrun (and those same credentials work just fine calling the procedures from within SQL Server Management Studio).

    I wont pretend to know what's going on here. I arrived at username and password simply by elimination.

     

    Since the calls work when you include the credentials, and fail when you don't, I would say that that is the source of the problem. Is there perhaps a mismatch between the username/password pair for the datasource that you use for client variables and for the procedure's datasource? What about going to the administrator and making sure they are the same?

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 26, 2012 9:29 AM   in reply to HiTopp

    HiTopp wrote:

     

    Shouldn't ColdFusion use the credentials provided when the datasource was set up (in Jrun)?  I don't actually assign credentials within the code, if that's what you are referring to.

    I didn't suggest you assigned credentials by code. I am talking about the credentials you provided when you initially created the datasource. That's indeed what ColdFusion should use.

     

    However, each datasource you created in the administrator was assigned its own credentials. My point is that these could differ from one datasource to the next. Apparently, your procedure code internally invokes a process that involves client variables.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 26, 2012 11:50 AM   in reply to HiTopp

    To me, what needs investigating is where or why the procedure call would require client variables. The rest is straightforward. One thing I know for sure is that the accessing client variables will require the permissions of the local client datasource.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 26, 2012 11:38 PM   in reply to HiTopp

    HiTopp wrote:

     

    BKBK wrote:

     

    To me, what needs investigating is where or why the procedure call would require client variables.

     

    What do you mean by this?  Why the call works when credentials are included?

    Yes, and why it fails when they are not. The change in behaviour between registry(no problems) and database for client storage(problems) suggests that the procedure requires/uses client variables somewhere.

     

    BKBK wrote:

     

    One thing I know for sure is that the accessing client variables will require the permissions of for the local client datasource.

     

    And by this, do you mean that ColdFusion needs an account for the local database (where the datasource points)?

    I was only confirming a point. I think this question is irrelevant to your case. I am assuming that you have set up the client datasource locally in the ColdFusion adminstrator.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 30, 2012 1:10 PM   in reply to HiTopp

    Curious. What happens when you use username or password, but then with incorrect values?

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 24, 2012 1:29 AM   in reply to HiTopp

    HiTopp wrote:

     

    I get a "Login Failed for user _____" error if I attempt to use incorrect credentials.

    That message is very likely coming from the database. To be sure, turn debugging on in the ColdFusion Administrator. Also check out the logs. We have to narrow this down, otherwise we could end up running circles.

     

    If the attribute pairs [username="valid_name", password="valid_password"] and [username="", password=""] work, but [username="valid_name", password="invalid_password"] fails, then the cause of the problem is most probably your code, and how it connects to the database(s). Could you show us all (or at least more) of the code?

     

    My first suggestion to you was to omit the line variables.storedProcService.clear();. Invoking clear() only makes sense when it occurs after addProcResults() or addParam(). Did you omit the line? What was the effect, with and without credentials?

     

    Could you please describe your infrastructure a bit more. From what I understand, there are at least 2 servers, S1 and S2. Let's say, ColdFusion is installed on S1. I am assuming from what has gone before that SQL Server, which runs the stored procedure, is on the second server S2.

     

    Go to the 'Client Variables' page in the ColdFusion Administrator. Verify that ColdFusion is indeed configured to use a database for client storage. What is the name of the datasource used as client store? Now, proceed to the datasource page and check out the settings for the client datasource. To which machine/database server does this datasource point?

     

    Jot down the server settings and credentials of the other datasources configured in the Administrator. To which machine/database server do these datasources point?

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 24, 2012 1:47 AM   in reply to HiTopp

    Oh, and it helps to know your ColdFusion version.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points