| Subject | Re: [IBO] ParamEdit |
|---|---|
| Author | Gediminas |
| Post date | 2003-10-30T14:45:25Z |
Jason, have tried as you suggested - that isn't working. Have tried to use
OldParameterOrdering (as using FB 1.5RC6), but there is was no luck. What I
want is that initial params would have "" (empty string) params, not null
(but below in the log, there is an evidence that "" isn't helping much -
empty dataset, but query with an empty params in external SQL editor
fetches all rows)
Here monitor's log ( "Vardas" means Name and "Pavarde" means Surname in my
language) with some notes:
/*---
CONNECT DATABASE localhost:e:\sokiudb.gdb
DB_HANDLE = 15282840
SECONDS = 0,335
----*/
/*---
DATABASE INFO
DB_HANDLE = 15282840
----*/
/*---
START TRANSACTION
DB HANDLE COUNT 1
TR_HANDLE = 15282404
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 15282404
STMT_HANDLE = 15282740
select s."Sokejo_ID" , s."Vardas" , s."Pavarde"
from SOKEJAI s
where
s."Vardas" starting with ? /* Vardas */
and
s."Pavarde" starting with ? /* Pavarde */
;
PLAN (S INDEX (IDX_SOKEJAI1,IDX_SOKEJAI))
FIELDS = [ Version 1 SQLd 3 SQLn 30
SOKEJAI.SOKEJO_ID = <NIL>
SOKEJAI.VARDAS = <NIL>
SOKEJAI.PAVARDE = <NIL> ]
SECONDS = 0,056
----*/
/*---
START TRANSACTION
DB HANDLE COUNT 1
TR_HANDLE = 15282092
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 15282092
STMT_HANDLE = 15282188
SELECT RDB$PROCEDURE_NAME
FROM RDB$PROCEDURES
ORDER BY RDB$PROCEDURE_NAME ASC
PLAN (RDB$PROCEDURES ORDER RDB$INDEX_21)
FIELDS = [ Version 1 SQLd 1 SQLn 30
RDB$PROCEDURES.RDB$PROCEDURE_NAME = <NIL> ]
SECONDS = 0,015
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 15282092
STMT_HANDLE = 15282188
PARAMS = [ ]
----*/
/*---
COMMIT
TR_HANDLE = 15282092
----*/
/*---
START TRANSACTION
DB HANDLE COUNT 1
TR_HANDLE = 15271540
SECONDS = 0,010
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 15271540
STMT_HANDLE = 15282188
SELECT RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS R
WHERE ( RDB$SYSTEM_FLAG <> 1 OR RDB$SYSTEM_FLAG IS NULL )
AND NOT RDB$RELATION_NAME STARTING 'RDB$'
AND NOT RDB$FIELD_NAME STARTING 'RDB$'
AND NOT RDB$FIELD_SOURCE STARTING 'RDB$'
ORDER BY 1 ASC
, 2 ASC
PLAN SORT ((R NATURAL))
FIELDS = [ Version 1 SQLd 3 SQLn 30
RDB$RELATION_FIELDS.RDB$RELATION_NAME = <NIL>
RDB$RELATION_FIELDS.RDB$FIELD_NAME = <NIL>
RDB$RELATION_FIELDS.RDB$FIELD_SOURCE = <NIL> ]
SECONDS = 0,020
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 15271540
STMT_HANDLE = 15282188
PARAMS = [ ]
SECONDS = 0,013
----*/
/*---
COMMIT
TR_HANDLE = 15271540
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 15282404
STMT_HANDLE = 15282740
PARAMS = [ Version 1 SQLd 2 SQLn 2
[VARDAS] = ''
[PAVARDE] = <n> '' ]
/// Initial params - why VARDAS is '' and PAVARDE - NULL? in the form
settings for these ParamEdit are the same. Gives an empty dataset - right
SECONDS = 0,010
----*/
/*---
START TRANSACTION
DB HANDLE COUNT 1
TR_HANDLE = 15281648
SECONDS = 0,013
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 15281648
STMT_HANDLE = 15282188
SELECT R.RDB$FIELD_NAME
, R.RDB$RELATION_NAME
FROM RDB$RELATION_FIELDS R, RDB$FIELDS F
WHERE R.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME
AND F.RDB$COMPUTED_SOURCE IS NOT NULL
AND NOT R.RDB$RELATION_NAME STARTING WITH 'RDB$'
ORDER BY 1 ASC
PLAN SORT (JOIN (F NATURAL,R INDEX (RDB$INDEX_3)))
FIELDS = [ Version 1 SQLd 2 SQLn 30
RDB$RELATION_FIELDS.RDB$FIELD_NAME = <NIL>
RDB$RELATION_FIELDS.RDB$RELATION_NAME = <NIL> ]
SECONDS = 0,117
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 15281648
STMT_HANDLE = 15282188
PARAMS = [ ]
----*/
/*---
COMMIT
TR_HANDLE = 15281648
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 15282404
STMT_HANDLE = 15282740
PARAMS = [ Version 1 SQLd 2 SQLn 2
[VARDAS] = 'I'
[PAVARDE] = <n> '' ]
/// In Vardas ParamEdit was entered "I" - gives an empty dataset due to
PAVARDE - NULL
----*/
/*---
FETCH
STMT_HANDLE = 15282740
FIELDS = [ Version 1 SQLd 3 SQLn 3
SOKEJAI.SOKEJO_ID = 0
SOKEJAI.VARDAS = ''
SOKEJAI.PAVARDE = '' ]
ERRCODE = 100
/// Some kind of error - don't understand, what is the cause
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 15282404
STMT_HANDLE = 15282740
PARAMS = [ Version 1 SQLd 2 SQLn 2
[VARDAS] = 'I'
[PAVARDE] = 'K' ]
/// Found matching record - right
----*/
/*---
FETCH
STMT_HANDLE = 15282740
FIELDS = [ Version 1 SQLd 3 SQLn 3
SOKEJAI.SOKEJO_ID = 4
SOKEJAI.VARDAS = 'Indrë'
SOKEJAI.PAVARDE = 'Kazlauskaitë' ]
----*/
/*---
FETCH
STMT_HANDLE = 15282740
FIELDS = [ Version 1 SQLd 3 SQLn 3
SOKEJAI.SOKEJO_ID = 4
SOKEJAI.VARDAS = 'Indrë'
SOKEJAI.PAVARDE = 'Kazlauskaitë' ]
ERRCODE = 100
/// Again strange error code ....
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 15282404
STMT_HANDLE = 15282740
PARAMS = [ Version 1 SQLd 2 SQLn 2
[VARDAS] = 'I'
[PAVARDE] = '' ]
/// An empty string for the surname (pavarde), but IB_Grid shows an empty
dataset. Actually, there are 2 records in the table with the name starting
with "I"
----*/
/*---
FETCH
STMT_HANDLE = 15282740
FIELDS = [ Version 1 SQLd 3 SQLn 3
SOKEJAI.SOKEJO_ID = 4
SOKEJAI.VARDAS = 'Indrë'
SOKEJAI.PAVARDE = 'Kazlauskaitë' ]
ERRCODE = 100
/// again error code
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 15282404
STMT_HANDLE = 15282740
PARAMS = [ Version 1 SQLd 2 SQLn 2
[VARDAS] = ''
[PAVARDE] = '' ]
/// Should get all records from the table - however ib_grid shows an empty
dataset
----*/
/*---
FETCH
STMT_HANDLE = 15282740
FIELDS = [ Version 1 SQLd 3 SQLn 3
SOKEJAI.SOKEJO_ID = 4
SOKEJAI.VARDAS = 'Indrë'
SOKEJAI.PAVARDE = 'Kazlauskaitë' ]
ERRCODE = 100
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 15282404
STMT_HANDLE = 15282740
PARAMS = [ Version 1 SQLd 2 SQLn 2
[VARDAS] = ''
[PAVARDE] = 'K' ]
/// An empty dataset - should be 7 rows as there are such number records
where surname (pavarde) starts with the "K"
----*/
/*---
FETCH
STMT_HANDLE = 15282740
FIELDS = [ Version 1 SQLd 3 SQLn 3
SOKEJAI.SOKEJO_ID = 4
SOKEJAI.VARDAS = 'Indrë'
SOKEJAI.PAVARDE = 'Kazlauskaitë' ]
ERRCODE = 100
Why this mystery happens?
At 2003-10-28 21:21, you wrote:
The Truth Is Out There
OldParameterOrdering (as using FB 1.5RC6), but there is was no luck. What I
want is that initial params would have "" (empty string) params, not null
(but below in the log, there is an evidence that "" isn't helping much -
empty dataset, but query with an empty params in external SQL editor
fetches all rows)
Here monitor's log ( "Vardas" means Name and "Pavarde" means Surname in my
language) with some notes:
/*---
CONNECT DATABASE localhost:e:\sokiudb.gdb
DB_HANDLE = 15282840
SECONDS = 0,335
----*/
/*---
DATABASE INFO
DB_HANDLE = 15282840
----*/
/*---
START TRANSACTION
DB HANDLE COUNT 1
TR_HANDLE = 15282404
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 15282404
STMT_HANDLE = 15282740
select s."Sokejo_ID" , s."Vardas" , s."Pavarde"
from SOKEJAI s
where
s."Vardas" starting with ? /* Vardas */
and
s."Pavarde" starting with ? /* Pavarde */
;
PLAN (S INDEX (IDX_SOKEJAI1,IDX_SOKEJAI))
FIELDS = [ Version 1 SQLd 3 SQLn 30
SOKEJAI.SOKEJO_ID = <NIL>
SOKEJAI.VARDAS = <NIL>
SOKEJAI.PAVARDE = <NIL> ]
SECONDS = 0,056
----*/
/*---
START TRANSACTION
DB HANDLE COUNT 1
TR_HANDLE = 15282092
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 15282092
STMT_HANDLE = 15282188
SELECT RDB$PROCEDURE_NAME
FROM RDB$PROCEDURES
ORDER BY RDB$PROCEDURE_NAME ASC
PLAN (RDB$PROCEDURES ORDER RDB$INDEX_21)
FIELDS = [ Version 1 SQLd 1 SQLn 30
RDB$PROCEDURES.RDB$PROCEDURE_NAME = <NIL> ]
SECONDS = 0,015
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 15282092
STMT_HANDLE = 15282188
PARAMS = [ ]
----*/
/*---
COMMIT
TR_HANDLE = 15282092
----*/
/*---
START TRANSACTION
DB HANDLE COUNT 1
TR_HANDLE = 15271540
SECONDS = 0,010
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 15271540
STMT_HANDLE = 15282188
SELECT RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS R
WHERE ( RDB$SYSTEM_FLAG <> 1 OR RDB$SYSTEM_FLAG IS NULL )
AND NOT RDB$RELATION_NAME STARTING 'RDB$'
AND NOT RDB$FIELD_NAME STARTING 'RDB$'
AND NOT RDB$FIELD_SOURCE STARTING 'RDB$'
ORDER BY 1 ASC
, 2 ASC
PLAN SORT ((R NATURAL))
FIELDS = [ Version 1 SQLd 3 SQLn 30
RDB$RELATION_FIELDS.RDB$RELATION_NAME = <NIL>
RDB$RELATION_FIELDS.RDB$FIELD_NAME = <NIL>
RDB$RELATION_FIELDS.RDB$FIELD_SOURCE = <NIL> ]
SECONDS = 0,020
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 15271540
STMT_HANDLE = 15282188
PARAMS = [ ]
SECONDS = 0,013
----*/
/*---
COMMIT
TR_HANDLE = 15271540
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 15282404
STMT_HANDLE = 15282740
PARAMS = [ Version 1 SQLd 2 SQLn 2
[VARDAS] = ''
[PAVARDE] = <n> '' ]
/// Initial params - why VARDAS is '' and PAVARDE - NULL? in the form
settings for these ParamEdit are the same. Gives an empty dataset - right
SECONDS = 0,010
----*/
/*---
START TRANSACTION
DB HANDLE COUNT 1
TR_HANDLE = 15281648
SECONDS = 0,013
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 15281648
STMT_HANDLE = 15282188
SELECT R.RDB$FIELD_NAME
, R.RDB$RELATION_NAME
FROM RDB$RELATION_FIELDS R, RDB$FIELDS F
WHERE R.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME
AND F.RDB$COMPUTED_SOURCE IS NOT NULL
AND NOT R.RDB$RELATION_NAME STARTING WITH 'RDB$'
ORDER BY 1 ASC
PLAN SORT (JOIN (F NATURAL,R INDEX (RDB$INDEX_3)))
FIELDS = [ Version 1 SQLd 2 SQLn 30
RDB$RELATION_FIELDS.RDB$FIELD_NAME = <NIL>
RDB$RELATION_FIELDS.RDB$RELATION_NAME = <NIL> ]
SECONDS = 0,117
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 15281648
STMT_HANDLE = 15282188
PARAMS = [ ]
----*/
/*---
COMMIT
TR_HANDLE = 15281648
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 15282404
STMT_HANDLE = 15282740
PARAMS = [ Version 1 SQLd 2 SQLn 2
[VARDAS] = 'I'
[PAVARDE] = <n> '' ]
/// In Vardas ParamEdit was entered "I" - gives an empty dataset due to
PAVARDE - NULL
----*/
/*---
FETCH
STMT_HANDLE = 15282740
FIELDS = [ Version 1 SQLd 3 SQLn 3
SOKEJAI.SOKEJO_ID = 0
SOKEJAI.VARDAS = ''
SOKEJAI.PAVARDE = '' ]
ERRCODE = 100
/// Some kind of error - don't understand, what is the cause
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 15282404
STMT_HANDLE = 15282740
PARAMS = [ Version 1 SQLd 2 SQLn 2
[VARDAS] = 'I'
[PAVARDE] = 'K' ]
/// Found matching record - right
----*/
/*---
FETCH
STMT_HANDLE = 15282740
FIELDS = [ Version 1 SQLd 3 SQLn 3
SOKEJAI.SOKEJO_ID = 4
SOKEJAI.VARDAS = 'Indrë'
SOKEJAI.PAVARDE = 'Kazlauskaitë' ]
----*/
/*---
FETCH
STMT_HANDLE = 15282740
FIELDS = [ Version 1 SQLd 3 SQLn 3
SOKEJAI.SOKEJO_ID = 4
SOKEJAI.VARDAS = 'Indrë'
SOKEJAI.PAVARDE = 'Kazlauskaitë' ]
ERRCODE = 100
/// Again strange error code ....
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 15282404
STMT_HANDLE = 15282740
PARAMS = [ Version 1 SQLd 2 SQLn 2
[VARDAS] = 'I'
[PAVARDE] = '' ]
/// An empty string for the surname (pavarde), but IB_Grid shows an empty
dataset. Actually, there are 2 records in the table with the name starting
with "I"
----*/
/*---
FETCH
STMT_HANDLE = 15282740
FIELDS = [ Version 1 SQLd 3 SQLn 3
SOKEJAI.SOKEJO_ID = 4
SOKEJAI.VARDAS = 'Indrë'
SOKEJAI.PAVARDE = 'Kazlauskaitë' ]
ERRCODE = 100
/// again error code
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 15282404
STMT_HANDLE = 15282740
PARAMS = [ Version 1 SQLd 2 SQLn 2
[VARDAS] = ''
[PAVARDE] = '' ]
/// Should get all records from the table - however ib_grid shows an empty
dataset
----*/
/*---
FETCH
STMT_HANDLE = 15282740
FIELDS = [ Version 1 SQLd 3 SQLn 3
SOKEJAI.SOKEJO_ID = 4
SOKEJAI.VARDAS = 'Indrë'
SOKEJAI.PAVARDE = 'Kazlauskaitë' ]
ERRCODE = 100
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 15282404
STMT_HANDLE = 15282740
PARAMS = [ Version 1 SQLd 2 SQLn 2
[VARDAS] = ''
[PAVARDE] = 'K' ]
/// An empty dataset - should be 7 rows as there are such number records
where surname (pavarde) starts with the "K"
----*/
/*---
FETCH
STMT_HANDLE = 15282740
FIELDS = [ Version 1 SQLd 3 SQLn 3
SOKEJAI.SOKEJO_ID = 4
SOKEJAI.VARDAS = 'Indrë'
SOKEJAI.PAVARDE = 'Kazlauskaitë' ]
ERRCODE = 100
Why this mystery happens?
At 2003-10-28 21:21, you wrote:
> > How to force ParamEdit to accept an empty values? I need, that it have ''--/ Gediminas /--
> > value instead of NULL when there is no any text entered
> >
> > Now empty field sends NULL to SQL query:
> > select "Name" from Table where "Name" starting with :Name --> empty
> > dataset, because :Name => NULL, but in SQL
> > select "Name" from Table where "Name" starting with '' equals to select
> > "Name" from Table
> >
> > How to force empty field treat as '' ?
>
>Try going into the field and hit the delete key or somehow make it think it
>has been updated. Then it will assign the blank string, It should anyway...
>
>Let me know.
The Truth Is Out There