• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Using Parameter Markers with SQL Functions

    October 25, 2006 Michael Sansoterra

    SQL is a superb data access tool in terms of ease of use and versatility. Data extraction routines that used to take hours to write in RPG or COBOL often takes minutes to write in SQL. The one draw back to dynamic SQL (as compared to data access routines written in a high level language), is that the DB2 query optimizer often takes precious time to figure out how to best execute an SQL request. To take the edge off this performance hit, dynamic SQL statements can be made “reusable” by using parameter markers so that the query optimizer can reuse the previous execution’s access plan, and thus boost performance.

    For clarification, host variables should not be confused with parameter markers (although they have many similarities). Host variables are used in pre-compiled SQL statements within high level language programs such as RPG, C, or COBOL. Parameter markers (as indicated by question marks within a statement) are used in dynamically prepared SQL statements in environments such as ODBC, CLI, JDBC, .NET, etc.

    One issue I’ve encountered with parameter markers, though, is that they can be a problem when used within SQL functions. Consider this common scenario, a reusable INSERT statement with parameter markers. The following SQL inserts a social security number (DEC(9,0)), employee number (INTEGER) and resume link (DATALINK data type) into a table called EMPRESUME:

    Insert Into i3/EmpResume(SSN,EmpNo,ResumeLink) 
    Values(?,?,DLValue(?,'URL',?))
    

    Because column ResumeLink is a DataLink data type, the built-in DLValue function is used to convert character data to a DataLink. (In case you’re wondering, DLValue’s three parameters are: data location, link type and comment.) Executing this statement from a dynamically prepared SQL environment will generate error: “SQL0418 Use of parameter marker not valid.”

    The problem is that SQL needs help interpreting the data type of the parameter markers that are used as a function parameter. Since many SQL functions accept multiple data types, SQL can’t reliably infer a data type. Therefore, when passing parameters to a function, the developer needs to give DB2 a little more information on each parameter’s type. This can be done by using the CAST function:

    Insert Into i3/EmpResume(SSN,EmpNo,ResumeLink) 
    Values(?,?,DLValue(Cast(? As VarChar(1024)),'URL',Cast(? As VarChar(240))))
    

    Now this statement with a parameterized function can be dynamically prepared and subsequently re-used. Here is sample VB.NET 1.1 code to execute this SQL using the IBM DB2 for iSeries .NET managed provider:

    Dim connDB2 As iDB2Connection = New iDB2Connection( _
    "DataSource=AS400;UserId=x;Password=y;Naming=System")
    connDB2.Open()
    Dim cmdDB2 As iDB2Command = New iDB2Command("TABLE INSERT EXAMPLE")
    cmdDB2.Connection = connDB2
    cmdDB2.CommandText = "Insert Into i3/EmpResume(SSN,EmpNo,ResumeLink) 
       Values(?,?,DLValue(Cast(? As VarChar(1024)),
       'URL',Cast(? As VarChar(250))))"
    
    cmdDB2.CommandType = CommandType.Text
    cmdDB2.Parameters.Add("SSN", iDB2DbType.iDB2Decimal)
    cmdDB2.Parameters.Add("EmpNo", iDB2DbType.iDB2Integer)
    cmdDB2.Parameters.Add("ResumeLink", iDB2DbType.iDB2VarChar)
    cmdDB2.Parameters.Add("LinkComment", iDB2DbType.iDB2VarChar)
    
    cmdDB2.Parameters("SSN").Value = 324004210
    cmdDB2.Parameters("EmpNo").Value = 145
    cmdDB2.Parameters("ResumeLink").Value = _
    "http://localhost/resumes/Resume145.doc"
    cmdDB2.Parameters("LinkComment").Value = "Resume for Bart Quincy"
    
    cmdDB2.ExecuteNonQuery()
    cmdDB2.Dispose()
    connDB2.Close()
    connDB2.Dispose()
    

    In addition to scalar functions, table functions often require the use of parameter markers as well. For example, say table function MajorCities accepts a state code (VARCHAR), minimum population requirement (INTEGER) and returns a list of cities matching the input parameters. A Select statement using this function with parameter markers could be coded as:

    Select * 
      From Table(MajorCities(Cast(? As VarChar(256)),
                             Cast(? As Integer))) As CityList
    

    This statement is now ready to be executed within a dynamic SQL environment.

    This technique has been tested at V5R2 and will work on that release or any that follow.

    Don’t let the requirement for parameterized SQL functions stop you from using parameterized statements. With a little help from the CAST function to define the parameter type, parameterized SQL functions are allowed and should be used to make re-usable statements execute quicker.

    Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Click here to contact Michael Sansoterra by e-mail.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    ARCAD Software

    Embrace VS Code for IBM i Development

    The IBM i development landscape is evolving with modern tools that enhance efficiency and collaboration. Ready to make the move to VS Code for IBM i?

    Join us for this webinar where we’ll showcase how VS Code can serve as a powerful editor for native IBM i code and explore the essential extensions that make it possible.

    In this session, you’ll discover:

    • How ARCAD’s integration with VS Code provides deep metadata insights, allowing developers to assess the impact of their changes upfront.
    • The role of Git in enabling seamless collaboration between developers using tools like SEU, RDi, and VS Code.
    • Powerful extensions for code quality, security, impact analysis, smart build, and automated RPG conversion to Free Form.
    • How non-IBM i developers can now contribute to IBM i projects without prior knowledge of its specifics, while ensuring full control over their changes.

    The future of IBM i development is here. Let ARCAD be your guide!

    Watch Now

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    nuBridges:  Leading provider of secure FTP on the iSeries
    Magic Software Enterprises:  Design IT, Control IT, Monitor IT with iBOLT
    COMMON:  Join us at the Spring 2007 conference, April 29 – May 3, in Anaheim, California

    iTera Offers Big Discounts on Echo2 Software for New CBUs SoftLanding Systems Acquired By CICS Specialist Unicom Systems

    Leave a Reply Cancel reply

Volume 6, Number 39 -- October 25, 2006
THIS ISSUE SPONSORED BY:

Advanced Systems Concepts
Profound Logic Software
Guild Companies

Table of Contents

  • Using Parameter Markers with SQL Functions
  • Stuffing Five Digits Into Four, Take 2
  • Admin Alert: The Pitfalls of Duplicating Data to a Test Library

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle