Fetch single record where unique key not know

nkuebelbeck

nkuebelbeck

VIP Member
I'm trying to query the F31122 by DOCO to check if any records exist via a C BSFN. If I were to use NER I would do a FetchSingle and check for SV File_IO_Status == SUCCESS.

If I knew the unique key I would use JDB_FetchKeyed and I see via NER that they use that api.

basically sql>SELECT * FROM LIBRARY.F31122 WHERE DOCO=12345678

Can I Use FetchKeyed or is there a different/better way?
 
You can use JDB_FetchKeyed with a partial key, just supply the number of key columns you are using. No gaps in the key though.

Otherwise you can fill in a SELECTSTRUCT or NEWSELECTSTRUCT and call JDB_SetSelection or JDB_SetSelectionX. Then your call to JDB_SelectKeyed will include your custom selection. Note these options can be combined so JDB_SelectKeyed will use the selection attached and the key datastructure if passed.

Plenty of examples in the existing code.

Craig
 
You can use JDB_FetchKeyed with a partial key, just supply the number of key columns you are using. No gaps in the key though.

Otherwise you can fill in a SELECTSTRUCT or NEWSELECTSTRUCT and call JDB_SetSelection or JDB_SetSelectionX. Then your call to JDB_SelectKeyed will include your custom selection. Note these options can be combined so JDB_SelectKeyed will use the selection attached and the key datastructure if passed.

Plenty of examples in the existing code.

Craig

thanks for this. The part i didn't understand was the "No gaps in the key though." I found a key that had doco as the first parm and used that. now its working as expected.
 
Sorry, what I mean is the key must be filled from the first element. If the key is KCOO, DCTO, DOCO, LNID and you just want DOCO you must fill KCOO, DCTO and DOCO and supply the key count of 3. If you don't know KCOO and DCTO, then find a key with just DOCO (as you did) or just use a SELECTSTRUCT and build your where clause. Obviously, using the key is better since the data is indexed that way. Also, the data will be sorted using the index chosen (ORDER BY clause)
 
With ER you could always use Select -> Fetch Next. That way you're not restricted to a Unique Key or indeed using a indexed field for your query.
 
Back
Top