OK I've worked out the problem. Changing the where clause to
WHERE RTRIM(LOGON_ID) = :logonid
works. Presumably this is because LOGON_ID is defined as a CHAR and not a
VARCHAR2. That explains why the select from ALL_TABLES worked and this
didn't (ALL_TABLES.OWNER is defined as VARCHAR2).
What made this confusing is that the query worked in sqlplus without the
RTRIM. Will this impact on the speed of the query?. This table is small but
if i use this on a large table will this slow it down? Or am i better off
changing it to varchar?
Thanks
Lang
Lang Sharpe wrote:
> Hi all
>
> I've changed the query to this. But still no luck. I've also tried adding
> logon_id to the select list, and defining $sLogon before the OCIBindByName
> call.
>
> $custStmt = OCIParse($oradb,'SELECT co_code, cust_type, customer_flags, '
> .
> 'email_address, user_view FROM customer ' .
> 'WHERE logon_id = :logonid');
> OCIBindByName($custStmt,':logonid',&$sLogon,32);
>
> $sLogon = 'LANG';
>
> var_dump(OCIExecute($custStmt));
> var_dump(OCIFetchInto($custStmt,$aCustRec));
> var_dump($aCustRec);
> ?>
>
> After my first email i started writing a table viewer script. Fully
> expecting it not to work, i started using bind variables. But it worked
> perfectly. I'm connecting to the same database as the same user. The
> interesting bits are below.
>
> $tablesStmt = OCIParse($oradb,'SELECT * FROM ALL_TABLES WHERE OWNER =
> :owner');
> OCIBindByName($tablesStmt,':owner',$sOwner,32);
>
> $colsStmt = OCIParse($oradb,'SELECT * FROM ALL_TAB_COLUMNS ' .
> 'WHERE OWNER = :owner AND TABLE_NAME =
> :tab');
> OCIBindByName($colsStmt,':owner',$sOwner,32);
> OCIBindByName($colsStmt,':tab',$sTable,32);
>
> foreach($aSchemas as $sOwner) {
> echo "<H1>{$sOwner}</H1>\n";
> OCIExecute($tablesStmt);
> while(OCIFetchInto($tablesStmt,$aTable,OCI_ASSOC+OCI_RETURN_NULLS)) {
> $sTable = $aTable['TABLE_NAME'];
> echo "{$sTable}\n";
> OCIExecute($colsStmt);
> while(OCIFetchInto($colsStmt,$aCol,OCI_ASSOC+OCI_RETURN_NULLS)) {
> echo ' ' . str_pad($aCol['COLUMN_NAME'],34),
> data_type($aCol),
> $aCol['NULLABLE'] == 'N' ? ' NOT NULL' : '',
> "\n";
>
> }
> echo "\n";
> }
> }
>
> How come this script works, but the top one doesn't?
>
> Thanks
>
> Lang
>
>
> Jadiel Flores wrote:
>
>> Have u tried using:
>> OCIBindByName($custStmt,':logonid',$sLogon,4);
>> instead of:
>> OCIBindByName($custStmt,':logonid',&$sLogon,4);
>> ???
>> (the difference if in the & in the 3rd parameter)
>>
>>
>>
>> Jadiel Flores
>> -------------------------
>> http://www.abargon.com
>> jflores@abargon.com
>> (52-55) 52-29-80-34
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
