Re: INSERT Column Type Conversions

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