Re: INSERT Column Type Conversions

[Rob <rob.couch@xxxxxxxxxxxxxx>]

Charles,

    I am writing in PHP, on i against DB2.

Example Code:

function MakeSets( $aSet )  //  Convert Associative Array Into UPDATE 
SET Field = Value Pairs
{
  $sSets = $sSep = '' ;

  foreach ( $aSet as $sK => $sV )
  {
    $sSets .= $sSep . '"' . $sK . '"' . "='" . $sV . "'" ;
    $sSep = ', ' ;
  }

  return $sSets ;
}
function MakeInserts( $aInsert )  //  Convert Associative Array Into 
INSERT Fields & Values
{
  $sFields = $sValues = $sSep = '' ;

  foreach ( $aInsert as $sK => $sV )
  {
    $sFields .= $sSep . '"' . $sK . '"' ;
    $sValues .= $sSep . "'" . $sV . "'" ;
    $sSep = ', ' ;
  }

  return array( 'Fields' => $sFields, 'Values' => $sValues ) ;
}

        $aApp[ 'Joint' ]           = $aData[ 'individualJointIndicator' ] ;
        $aApp[ 'Insurance' ]       = $aData[ 'optionalInsurance' ] ;
        $aApp[ 'Price' ]           = $aData[ 'purchasePrice ' ] ;
        $aApp[ 'PrevEmployer' ]    = $aData[ 'mainPrevEmployer' ] ;
        $aApp[ 'PrevTimeOnJob' ]   = $aData[ 'mainPrevTimeAtEmployer' ] ;

        $sSets = MakeSets( $aApp ) ;

        $rResult = RunQuery( 'UPDATE MyTable SET ' . $sSets . ' WHERE 
ID = ' . $nApp . ' ;' ) ;

        $aPeople[ 'Occupation' ]      = $aData[ 'mainOccupation' ] ;
        $aPeople[ 'TimeOnJob' ]       = $aData[ 'mainTimeAtEmployer' ] ;
        $aPeople[ 'Income' ]          = $aData[ 'mainMonthlyIncome' ] ;
        $aPeople[ 'Signed' ]          = $aData[ 'mainApplicationSigned' ] ;
        $aPeople[ 'Email' ]           = $aData[ 'emailAddress' ] ;

        $aPpl = MakeInsert( $aPeople ) ;

        $rResult = RunQuery( 'INSERT INTO AnotherTable ( ' . $aPpl[ 
'Fields' ] . ' ) VALUES ( ' . $aPpl[ 'Values' ] . ' ) ;' ) ;



On 2016-01-19 12:17, Charles Wilt wrote:
> Rob,
>
> Newer version of DB2 do have some auto-casting functionality.
>
> But you shouldn't be counting on it in production code.
>
> What language are you writing in?  Can you show some code examples?
>
> I don't understand how you'd be creating SQL functions that didn't know
> what they were dealing with.
>
> Charles
>
> On Tue, Jan 19, 2016 at 12:26 PM, Rob <rob.couch@xxxxxxxxxxxxxx> wrote:
>
> > Hey guys,
> >
> >      As I am transitioning from MySQL, I want to run an issue by you.
> >
> >      In my integer/float/ decimal fields I use functions that create SET
> > strings or INSERT FIELD and VALUES strings which place single quotes around
> > the values.  So far I have not seen any ill effects from this practice.  If
> > I create an INSERT manually and know I am dealing with a numeric value
> > going into a numeric field i do not use the single quotes.
> >
> >      But in my functions I do not test or other wise know that the value of
> > a name => value pair in an associative array is necessarily numeric.  As I
> > said so far, DB2 seems to know that the string I am inserting is a number
> > and stores it correctly.  So in my testing and what I have read, I have not
> > been very concerned.
> >
> >      But I am creating code that run a financial entity.  The more I code
> > on the i, with PCI compliance in mind, the more I worry about getting
> > things 100%.
> >
> >      Please let me know I have nothing to worry about...
> >
> > Thanks
> > Rob
> >
> > ---
> > This email has been checked for viruses by Avast antivirus software.
> > https://www.avast.com/antivirus
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> >
> > Please contact support@xxxxxxxxxxxx for any subscription related
> > questions.



---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus