Results 1 to 3 of 3

Thread: Udt

  1. #1
    Join Date
    Oct 2011
    Posts
    11

    Unanswered: UDT insert question

    I have a question about user defined types. I'm following the tutorial here:

    DB2 Basics: An introduction to structured data types and typed tables

    In the first insert example, the type attribute values are set as:

    address_t() ..street('20 Indian Rd') ..city('Toronto') ..province('Ontario')
    ..postal_code('M6T2R1'))

    Is anything with '()' seen as a user defined type and then corresponding values are allocated to attributes? I'm trying to do an insert and DB2 doesn't like when its passed the complete value as a string in a prepared statement (using PDO).

    I posted in the PHP section (http://www.dbforums.com/php/1671705-...ned-types.html) but I don't think this type of usage (PDO + UDT) is prevalent since there are virtually no examples I can find.

    Thanks in advance!
    Last edited by oshman; 11-10-11 at 23:13. Reason: more specific title

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    In the other post, you wrote this:
    ------------------------------
    $f_name = 'Tom';
    $l_name = 'Jones';
    $street = "111 Way St";
    $city = 'Houston';
    $state = 'TX';
    $zip = '77007';
    $type = "address_t() ..street($street) ..city($city) ..province($state) ..postal_code($zip)";

    $stmt = $db->prepare("insert into clients (client_lname, client_fname, address) values (:db_insert_placeholder_1,:db_insert_placeholder_2 ,address_t() ..street(:db_insert_placeholder_3) ..city(:db_insert_placeholder_4) ..province(db_insert_placeholder_5) ..postal_code(db_insert_placeholder_6))");
    $stmt->bindParam(':db_insert_placeholder_1',$f_name);
    $stmt->bindParam(':db_insert_placeholder_2',$l_name);
    $stmt->bindParam(':db_insert_placeholder_3',$street);
    $stmt->bindParam(':db_insert_placeholder_4',$city);
    $stmt->bindParam(':db_insert_placeholder_5',$state);
    $stmt->bindParam(':db_insert_placeholder_6',$zip);
    $stmt->execute();
    ------------------------------
    This is basically the correct approach. But it doesn't work because you don't have a ':' in front of db_insert_placeholder_5 and db_insert_placeholder_6.

    ------------------------------
    $f_name = 'Tom';
    $l_name = 'Jones';
    $street = "111 Way St";
    $city = 'Houston';
    $state = 'TX';
    $zip = '77007';
    $type = "address_t() ..street($street) ..city($city) ..province($state) ..postal_code($zip)";

    $stmt = $db->prepare("insert into clients (client_lname, client_fname, address) values (:db_insert_placeholder_1,:db_insert_placeholder_2 ,:db_insert_placeholder_3)");
    $stmt->bindParam(':db_insert_placeholder_1',$f_name);
    $stmt->bindParam(':db_insert_placeholder_2',$l_name);
    $stmt->bindParam(':db_insert_placeholder_3',$type);
    $stmt->execute();
    ------------------------------
    That cannot work because you try to bind the string "address_t() ..street($street) ..city($city) ..province($state) ..postal_code($zip)" to a value of type "address_t". But a structured type is not a string.

    What is often done to avoid those problems is to define a UDF shadowing the constructor. Then use this UDF in the INSERT statement:
    Code:
    CREATE FUNCTION address_t(street VARCHAR(100), city VARCHAR(100), state VARCHAR(100), zip VARCHAR(10))
       RETURNS address_t
       RETURN address_t()..street(street)..city(city)..province(state)..postal_code(zip);
    
    INSERT INTO clients (client_lname, client_fname, address)
    VALUES (:db_insert_placeholder_1, :db_insert_placeholder_2, address_t(:db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6))
    Now you don't have to remember the correct syntax to invoke the setter methods on the type in the INSERT statement - just use the function, which does the right thing.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Oct 2011
    Posts
    11

    Excellent!

    Just what I needed. Many thanks Knut!

    Cheers

Posting Permissions

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