View only returning first field

questions
1

Hi Everybody
I have defined a view in Clarion looking at only one table and returning two fields.

Settings::View       VIEW(Settings)
						Project(SET:Detail)
						Project(SET:Item)
                     END

I am looping through the records. The code is below.
The messages are in there for me to try to debug the issue.
I only get the Item field returned.

    	CLEAR(Settings::View)	
    	OPEN(Settings::View)
    	IF ERRORCODE()
    		ViewOpenError('Settings')
    	ELSE
    		set(Settings::View)
    		LOOP
    			NEXT(Settings::View)
    			IF ERRORCODE()
    				MESSAGE('ERROR_' & ErrorCode() &', '& Error())
    				BREAK
    			END			
    			if clip(SET:Item[1:15]) <> '7DayImportName_'
    				cycle
    			end
    			if Access:Settings.Fetch(SET:kItem)
    				message('No Fetch')
    				cycle
    			else
    				Access:Settings.Fetch(SET:kItem)
    				message('Fetch_D_' & SET:Detail)
    				message('Fetch_I_' & SET:Item)
    			end
    			IQ:ImportCode			= SET:Detail
    			IQ:ImportItem			= clip(SET:Item[16:50])
    			ADD(IQ)
    			message('NonError_' &', '& SET:Item &' <10> FRED <10> '& left(SET:Detail[1:5]))
    			Cycle
    		END
    		CLOSE(Settings::View)
    	END

Any ideas why I only get the tables first field returned?

2

hi Marc,

First, you are doing a Fetch inside the view;

This code serves no purpose, and you should remove it. It’s not the root of your problem, but it’s just extra work that will make things worse, not better.

The root of your problem is that you are using MESSAGE for debugging. Specifically in this case, using <10> as a line separator. You probably want to drop that, or replace with <13,10>.

Also, you don’t actually say what you are seeing, so it’s tricky to see beyond that.

3

Thanks for that. The fetch went in as an attempt to sort the problem. The issue is that although it finds the relevant records the only fields that seems to be available to me is the Item field which is the first field. I need to assign the second field to a field in the queue but it always returns as blank.

Marc Brierley
Trimble Forestry
+64212855568

4

Set:Item would apparently be set to something, because it’s getting past the CYCLE statement.
if IQ:ItemQueue is always blank then it’s likely because Set:Item[16:50] is blank.

5

SET:Detail is 1st field, SET:Item is 2nd one (see VIEW decl).

So you get SET:Detail?

So you get SET:Item?

Really difficult to understand what you are trying to do.

6

Hi Bruce. Set:Item is being read from the table. Once the loop finds a record that matches the criteria, I should be able to read all fields for that record shouldn’t I? Such as SET:Detail from the example. Despite there being data in the table all fields other than SET:Item are returning as blank. The same basic code is being used in other areas of the application and function correctly. For since reason it is not working correctly for this table.

Marc Brierley
Trimble Forestry
+64212855568

7

Once the loop finds a record that matches the criteria, I should be able to read all fields for that record shouldn’t I?

no, only fields that are PROJECTed in the VIEW.

Despite there being data in the table all fields other than SET:Item are returning as blank.

In your example SET:ITEM and SET:DETAIL are in the View, so those are the only two you should access in your code. (If you want more fields, add more PROJECTs)

The same basic code is being used in other areas of the application and function correctly. For since reason it is not working correctly for this table.

The behaviour can be driver dependant. So, for example, the way a VIEW works on a TPS record is different to a MSSQL record. You should , in all cases, assume that fields not in the VIEW are not available. (If it works without that, against some driver now, it’s no guarantee it’ll work in the future.)

8

Right. I get that.
The only field available to me is the Item field which is the first field in the table. The Detail field returns as blank.
I have tried adding all fields to the view. All fields other than Item are still returning blank.
All tables in the dictionary use the same sql anywhere driver and connection string.
Is there perhaps something that needs to be set against the table in the dictionary?

9

So do you get many rows with just set:Item or just the first one.
Just wondering if your data contains row or rows with all blanks.

Also you don’t seem to be adding a sort order, that might help.

Also also, what file type is settings?

10

Have you tried using ODBC Admin to log a trace file and see what actual SQL is being passed? Does the same select statement give the expected results outside of Clarion?

11

The application is running on the same database as my test database. It’s not using an odbc connection.
Good suggestion though. I may use it if I have issues on our live servers.

12

Can you view the Settings table using the DCT database browser? Can you wizard a Browse for Settings and see the fields?

13

If this is a multi-DLL app, have you recompiled all of the apps?
Made any dictionary changes?

14

It is multi dll and I have tried compiling the whole solution however everything was already in there including the procedure I’m working on and the table called Settings.
I have only added the view on the settings table, the queue that I want to populate and the routine to populate the queue.

Cheers

Marc Brierley

Trimble Forestry Logistics

1372 Hinemoa St, Rotorua, 3010 | New Zealand
+64 73480308 Direct | +64 212855568 Mobile
+64 73480306 Fax | [email protected]

Please consider the environment before printing this email.
Notice: This e-mail together with any attachments is confidential, may be subject to legal privilege and may contain proprietary information, including information protected by copyright. If you are not the intended recipient, please do not copy, use or disclose this e-mail; please notify us immediately by return e-mail and then delete this e-mail.

15

OK. Your issue sure does sound like when I’ve had mismatched global DLL record structures. Such as when I changed the dictionary but didn’t compile all of the apps.