Need sample code for using Sybase Dynamic SQL, esp. with multiple parameters

Posted on 2009-02-17
Last Modified: 2012-05-06
Where can I find sample code for preparing a statement, set the input parameter values, and execute the statement using ct_dynamic function? Especially when there are multiple input parameters to set (bind)? I have only found sample code for setting ONE parameter.
Question by:minjiezen
    LVL 19

    Expert Comment

    You are probably looking at the "dynamic2.cp" code.

    Just keep adding substitution question marks and comma separated bind variables in the using clause.


    		"select *
                       from publishers
                      where pub_id = ?
                        and pub_state = ?";
    EXEC SQL EXECUTE sel_stmt INTO :pub_id, :pub_name, :pub_city, :pub_state
                             USING :pub_id, :pub_state ;

    Open in new window


    Author Comment

    Hi Bill, thanks for the fast response. Sorry for not stating it clearly. I'm actually trying to do do dynamic SQL  in C++ with Sybase Client Library, with the library APIs like ct_dynamic(), ct_send(), ct_param(), ct_results().
    LVL 19

    Expert Comment

    O.K., I think this is the hardest way possible to do Client Lib programming.

    To do prepared statements, you have to:
      ct_dynamic : prepare
      ct_dynamic : describe input
      ct_dyndesc  (which will give you the description of the input parameters)
      Loop through the ct_dyndesc data structures can call ct_param for each one
      ct_dynamic : execute
      ct_dynamic : describe output
    and then you can process the results of the SQL statement.

    The deal on dynamic SQL is this:  If it is truly dynamic, it is a waste of time preparing statements.  Just build your complete SQL statement with the parameters already substituted in the string and do a ct_dynamic : execute_immediate.  When you prepare a "dynamic" SQL statement, you are actually creating a temporary stored procedure under the covers at run time that lives only for the life of the connection.  Embedded SQL does the same thing because it uses these calls under the covers.

    So you can see, if you are doing something like building a general purpose query interface in an application and you truly cannot predict what the user will ask for, you really have dynamic SQL and you might as well just build the entire statement once and be done.  All the overhead and complexity of preparing the statement is wasted since the SP will never get used again and you cannot assume what the input parameters and results set will look like so you have to code it all dynamically.

    If, on the other hand, you actually have fairly static SQL statements, your best bet is to code them into stored procedures yourself and then call them as RPCs (not exec immediate command strings).  This is by far the lowest overhead method and gives you other advantages such as keeping the SQL in the database, allowing you to tune the queries, provides better performance, gives you code reuse, simplifies the C++ code, etc.

    Stored procedures also gives you the option of assuming the parameter set so you don't have to deal with getting the description of the input parameters.
     In many cases, people resort to dynamic SQL not because the result set changes from call to call but because the WHERE clause changes.  Obviously, you can pass parameters to a bunch of fields appearing the WHERE clause but you might want to limit it to just one or two of several.  There are a couple of tricks for doing this.  See the code snippet below.

    There are other techniques for dealing with lists of values and IN clauses as well.  The bottom line is to avoid putting SQL in your C++ code at all costs.  It is much more difficult to create, debug, maintain, and impossible to tune.  Another advantage of dividing things up is that there is a natural division of labor as well.  One person can wrtie C++ while another works on stored procedures.

    I don't know which situation you are in but I am pretty sure that prepared statements are the last way you want to try approaching the problem.


    create procedure fubar @a int, @b int, @c int
      select *
        from barfu bf
       where (bf.a = @a or @a is null)
         and (bf.b = @b or @b is null)
         and (bf.c = @c or @c is null)

    Open in new window


    Author Comment

      Bill, thanks a lot for the detailed info. I really appreciate it.  
       The purpose of using dynamic SQL is to prevent SQL injection attacks -- to parameterize the SQL statements. In this case I'm only left the choices of prepare-and-execute method or stored procedure.
       According to what you stated, should I convert all the dynamic SQL statements into stored procedures (SPs),  use ct_param() to set the input parameters, and then use ct_command() calls with CS_RPC_CMD option to call those SPs, instead of using ct_dynamic calls with CS_PREPARE and CS_EXECUTE options? There are quite a few dynamic SQL statements in the application. Do you think it would affect the database performance by creating many stored procedures like this?
       Thanks again.
    LVL 19

    Accepted Solution

    Yes.  You should definitely convert all the dynamic SQL to stored procedures and use ct_param and ct_command with CS_RPC_CMD.

    Yes, database performance will be affected by converting to stored procedures; it will get better!  The number of procedures is irrelevant to database performance.  Each one represents pre-parsed, pre-optimized, and pre-compiled code; all steps that dynamic SQL has to do each time you run it.  Prepared statements are worse if you are throwing them away each time and not quite as bad as just passing SQL strings if you are reusing them quite a bit.

    Since you are worried about application security, the use of stored procedures tightens things up even further.  For example, you can grant the application user no rights at all except EXEC on the stored procedures you want him/her to use.  The stored procedure owner has to have rights to the tables when you compile them.  SQL Injection is not a problem at all because you are doing everything as RPC calls and nobody (except DBO) needs to have access to the actual data tables.

    If you need to grant table level access to certain users, say internal people running a reporting tool against the same database, you can still do so as long as you keep the user ids distinct.  You can limit that access to read-only unless there is a need for read-write access.  Some sites take all this a step further and build everything on top of views that have a layer of security built in them as well.

    Creating all those stored procedures is not a bad job if you have a database IDE to work with.  Sybase Workspace is free, either thru Sybase or via membership in the ISUG ($95 well spent.)  You can also use Embaracdero RapidSQL Pro or Aquafold Aqua Data Studio.  There are some others out there as well but those are the three biggies.  All three have interactive debuggers for T-SQL procedures and triggers as well so you should be in much better shape than you are now.

    You can set up an assembly line for the procs.  Just clip the SQL out of the C++ code and drop it into a CREATE PROCEDURE template.  Give the procedure and the arguments names, put the argument list at the top, and you are done.


    Author Comment

    Bill, thank you very much for all the insights.  

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Title # Comments Views Activity
    To form a query 10 377
    SQL Time Syntax 4 270
    SQL Query Syntax 11 140
    SQL Query Syntax 5 157
    The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
    DECT technology has become a popular standard for wireless voice communication. DECT devices are not likely to be affected by other electronic devices and signals because they operate in a separate frequency-band.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now