Post reply

Inserting a single quote using stored procedures

  • November 7, 2002 at 12:07 am

    #79254

    Hi All.

    I have a parameter as varchar coming into the procedure. This parameter can have single quotes to be inserted into the column of the table. I can do this by SET QUOTED_IDENTIFIER ON from the page and insert into the table, but i am not able to figure out how do i do it with a paramter which comes to the stored proc.

    Please i need some nice help from u gurus.

    Bye

    Jay

  • November 7, 2002 at 2:53 am

    #440597

    You should be able to insert a varchar containing a single quote into a table field without any problems. However, if you are concatenating the varchar param into a dynamic SQL statement you need to escape the single quote character.

    Try using the replace command as follows:

    set @param = replace(@param, char(39), char(39) + char(39))

  • November 7, 2002 at 4:39 am

    #440605

    If coming from a client app then have the app do a replace of the ' with '' (two single quotes).

    VB

    REPLACE(variablename,"'", "''")

    C++

    cStringVar.Replace("'","''")

    Another for SQL

    set @param = replace(@param, '''', '''''')

    (Note there you have to double the single in the string area then double the doubled version for it to replace right but I am assuming it is a client app since you mentioned using SET QUOTED_IDENTIFIER ON.)

    or what ever is specific to your programming language and your variable types

  • November 7, 2002 at 5:15 am

    #440614

    quote:

    If coming from a client app then have the app do a replace of the ' with '' (two single quotes).

    VB

    REPLACE(variablename,"'", "''")

    C++

    cStringVar.Replace("'","''")

    Another for SQL

    set @param = replace(@param, '''', '''''')

    (Note there you have to double the single in the string area then double the doubled version for it to replace right but I am assuming it is a client app since you mentioned using SET QUOTED_IDENTIFIER ON.)

    or what ever is specific to your programming language and your variable types

    Thnks , i think thios should help, but then it will req changes in many places, in the code of my developers team.

    I will let u know if this works out propperly

    Thnks

    Jay

  • November 7, 2002 at 5:29 am

    #440615

    If you are using parameters correctly from your client, i.e parameters of the command object.

    Then no replacing is needed as the text is handled correctly. it is only if you are using adhoc sql i.e connction.execute "insert into ..." then you will need to so the replacing.

    You quite often find that people replace when there is no need. this results in '' being stored or returned to the client.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • November 7, 2002 at 5:58 am

    #440623

    Is is the preferred method to use Command Object of adCmdStoredProc instead of a .Execute on the Connection object, the .Open of a Recordset Object or as adCmdText in a Command Object. This a much cleaner interface and Simon is right you will not have to perform the replace. The only issue with it though is that it returns the recordset as adOpenStatic and acts as a Forward-Only recordset, so you cannot move previous. See KB Article here http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306388 and here http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306385

  • November 7, 2002 at 8:46 am

    #440650

    You should never, in my experience, need to use server side cursors. This is reinforced with .net where everything is client side. With client side recordsets you can do what you want with them, forward, back, left, right, upside down

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • November 7, 2002 at 9:50 am

    #440668

    OK Simon, then how do you code things like loops of rows in a cursor where you need an action to occur on each row, rather than an aggregated result: for example, calling a COM object to send the row to an external system, or just simple things like coping with multi-row updates in update triggers. Perhaps I'm missing a point, and I know what the purists say, but there is a difference between writing totally correct SQL and writing code that other people are going to understand. I hope you can enlighten me on some of these gaps in my knowledge? In the case of .net, isn't that expecting to have a middleware layer to do this data manipulation, rather than downloading masses of data to each client?

  • November 7, 2002 at 10:55 am

    #440685

    And forceing a lot of rows down to the client can have side effects on memory resources. Not saying Client Side cursor are bad and again they are preferred, but preferred is not always practical. My other statment was a headsup.

  • November 8, 2002 at 9:07 am

    #440852

    When I refer to server side cursor I mean a client side piece of code that is using a server side cursor an looping though the data. i.e. a recordset in server side mode.

    This requires the setting up of a cursor on the server, requires a call to the server for each chunk of data, by default each row not many people change this.

    A server side cursor is only of benefit if you are not going to process each row in the resultset. However in that situation change your code to only return the rows you want and use a client side cursor.

    As for returning lots of data to the client, yeh I agree you, have to decide whether this is needed, whether you can do the work on the server. weigh up the pros and cons. This will get expenentionally harder to assess with Yukon

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • November 8, 2002 at 9:13 am

    #440853

    Then you are talkign abou doing the code lik so

    rs.CursorLocation = adUseServer

    but when using adCmdStoredProcedure as the CommandType for the command object there is a know bug that the cursor will only act as a forward only. Back to the two links I posted.

    It was mentioned by another person to cloan the recordset into one with a CursorLocation of adUseClient, but again you push all the data down, it may not fit in memory.

  • November 8, 2002 at 10:21 am

    #440860

    I read those articles and the say the problem is when you are using server side cursors but not that it forces server side cursors.

    My app only uses SPs and we have no server side cursors.

    My point is if you need the data on the middle tier then, use a client side cursor.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • November 8, 2002 at 10:57 am

    #440869

    So I know we are are the same page (or heck, even the same book at this point). Simon will you post an example of what you are doing so I can be sure that is not what I am talking about?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply