Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    20

    Unanswered: Apostrophes problem using ASP and Oracle

    Hi everyone,

    I'm having extreme difficulties in getting a stored procedure to work. I am passing string values from a web page to the following procedure which updates a table in an Oracle db.


    var cmd = Server.CreateObject("ADODB.Command");
    cmd.ActiveConnection = Application("bmcmaindb");

    var param1 = cmd.CreateParameter("text1", adChar, adParamInput, 4000);

    var param2 = cmd.CreateParameter("text2", adChar, adParamInput, 4000);

    cmd.Parameters.Append(param1);
    cmd.Parameters.Append(param2);

    cmd.Parameters("text1") = param1;
    cmd.Parameters("text2") = param2;

    cmd.CommandText = "{CALL my_pkg.my_proc(?,?) }";

    cmd.CommandType = adCmdText;
    cmd.Execute();

    cmd = null;


    The trouble here is that it is knocking off text after any apostrophe within both of the parameters. Does anyone know why?


    Regards,

    John

  2. #2
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    The reason for this that Oracle sees the apostrophe as the end of the field. To enter apostrophe's into the Oracle database you need to double up the apostrophe in the string, so that Oracle processes see it as an apostrophe rather than the end of the field.

  3. #3
    Join Date
    Apr 2003
    Posts
    1
    Just Escape the variables when you're putting them into the database. UnEscape them when you're reading them out of your database.


    OK, it's pretty irritating, but it works.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •