Re: INSERT Column Type Conversions

The point of my question.... is does DB2 casting to be trusted....

A string is a string is a string.....

But a numeric value could look like a string.

So if I present a number 100 to be stored into a smallint field, and I place single quotes around it like '100'... is this a no brainer for DB2 to handle.

I am not trying to stuff strings into numerical fields. I know all strings must be encompassed in single quotes. And I certainly could type check each value and decide if the single quotes were necessary.

But is the time used to type check in PHP if DB2 stores a numeric value in a string faster than the if type check is numeric?

The particular table that made me think of this has 683 fields. (NOT MY DESIGN) But I have to use it to be compatible with the current procedures.

So I am not going to hand write a INSERT or UPDATE statement, especially not know which of the fields I am not even going to touch since there was no data to place in them.

As I have said before in my examples, as I validate the data, I know what is going in each field, as each key in the associated array is the name of the field. I will include the examples again:

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

foreach ( $aSet as $sK => $sV ) // $sK is the field name. $sV is the Value
{
$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 ) // $sK is the field name. $sV is the Value
{
$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 23:58, John Yeung wrote:

On Wed, Jan 20, 2016 at 12:41 AM, Rob <rob.couch@xxxxxxxxxxxxxx> wrote:

I know what values are going into the fields... my MakeSets and MakeInsert
functions have not a clue, so not matter what , all values are inside single
quotes....

But why can't MakeSets and MakeInsert use the PHP type-testing functions?

John Y.

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