Offering the user the option of database browsing 1

[AndrewMozley]

An application offers a user with ‘Administrative rights’ the ability to browse any of the tables in the application.
The application lets the user enter the commands in an editbox. So, for example, the user could enter these commands :

[indent]SET DELETED OFF
USE XSDET (A table of sales order detail lines)
BROW[/indent]

The application then compiles and executes these commands, and (in this case) the standard BROWSE window is displayed, showing all the records down the screen and the columns for each of the fields across the screen, (In this case the application usually appends a NOCAPTION clause to the BROW command)

Fields which are numeric are shown in STR(9,2) format. It would be nice to offer an option whereby fields with more decimal places could be shown with their full precision. And fields of type ‘Currency’ could perhaps be shown with 4 decimal places. Could anyone suggest a way that I could offer this facility?

The standard BROWSE command lets the user modify the contents of a table at run-time. It would be nice to continue to offer the user that option as well.

Grateful for suggestions - Andrew

 

[Chris Miller]

The precision for numeric data is set by SET DECIMALS.

If you set this to max (18) then you see all decimal places of float/double/numeric fields, and 4 decimal places in currency fields and integers remain integers without decimal places:

Create Cursor crsTestData (iNum Integer, fNum1 Float(20,18), dNum2 Double, nNum Numeric(20,18), yCurrency Y)
Insert into crsTestData Values (1,0.123456789012345678,0.123456789012345678,0.123456789012345678,0.1234)
Insert into crsTestData Values (2,0.2,0.2,0.1,0.2)

Set Decimals To 18
Browse

So perhaps that's already a solution or close enough to ideal. Well, or teach them that SET comand and they can use it as needed.

Chriss

 

[Mike Lewis]

Andrew,

If your aim is only to let the admin user browse - and possibly edit - an arbitrary table, then I suggest that this is not a good way to go about it. It would be much better to give them an interactive tool, based on a VFP form. You could use ADIR() to get the names of the tables, then display the names in a listbox or combo box. Let the user choose from that list, and then display the contents of the relevant table in a grid.

The advantage of this approach is that it would be safer and more reliable. If, on the other hand, you gave them the opportunity to execute any command, you open the door to a careless user wreaking all kinds of havoc in your application.

That said, if you are sure you want to provide this level of freedom, there are existing tools that you can use. I don't have any up-to-date details, but many years ago I incorporated such a tool into an application - but only for developers to use.. I think it was called FoxBox. I have no idea if it is still available, but it might be worth you looking around for it.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

And to answer your other question, the BROWSE would indeed allow the user to alter the data in the table. That is its default behaviour. If you wanted to prevent them from altering the data, you would add NOEDIT (and probably NOAPPEND and NODELETE) to the BROWSE command. (Or, alternatively, add NOUPDATE to the USE command.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

I've found a reference to the tool I mentioned. It is indeed called FoxBox. See here for further information:

https://flylib.com/books/en/3.38.1.152/1/

I think it will do exactly what you want. But my above warning about careless (or malicious) users would still apply.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[AndrewMozley]

Thank you Chris and Mike.
The SET DECIMALS which you recommend, Chris, does the trick.

It seems to have a lasting effect on the database. When I close the application and go into it again, if I browse a table, the fields in the table which have more than two decimals are shown in their full detail.

Andrew M.

 

[Chris Miller]

SET DECIMALS only has an effect on the number of decimals displayed, the data stays as is. It's lasting effect only is, that it only changes, if you do another SET DECIMALS. Or restart VFP, for example.
The default setting comes from Options dialog, Regional tab, Currency and Numbers, Decimal Digits. And it's usually 2.

Chriss

 

[GriffMG]

Here is the complete 'browser' source including compiled exe

Be careful with it - a gifted user can ruin a lot of work with a tool like browser
Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Mike Lewis]

Andrew,

Keep in mind that SET DECIMALS doesn't apply to currency fields (which always display with four decimal places).

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Thanks for uploading your browser, Griff. I'll give it a try.

But can I just double-check: Is this your own work, or did you obtain it from somewhere else? I just want to check there are no copyright or licensing issues.

Thanks

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

It is one of my very own scruffy little bits of code Mike, knocked together a long time ago
to enable me to hack users databases when the need arises...

No copyrights asserted.

Now I think about it, I believe I have even seen variants of it on google...

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Mike Lewis]

Griff,

Just took your browser program for a quick test. It looks very similar to what I was suggesting to Andrew: a list of all the tables in a given directory; you then pick a table for browsing (with the added useful feature of being able to select an index).

A point to keep in mind is that it presumably requires the VFP runtimes to be installed. This is not likely to be a problem, given that you would normally run it on the same computer which hosts your main application.

It's quite different from the FoxBox program that I mentioned. That is more of a command processor. You can enter any legal command (with some exceptions) and it executes it straight away, much like in VFP command window.

So, thanks for sharing this.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

After selecting the table, and the index if needed, you can type any legal instruction in the lower text box, like ZAP or PACK or delete for widget="W"

If you are interested there are also some addition 'widgets' in the code that enable almost anything by calling a UDF, the examples are for specific requirements
but I'm sure you get the picture.

It's scrap code... not well done, not made for efficiency, might even have a for next loop or two - so don't look at the code if you are sensitive!

I leave a copy on most machines I work on, yes they would have the runtimes on them already, in the same place on each PC/server so can just navigate to
the folder of interest with a command prompt and type c:\whereiputit\browser and away I go.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[AndrewMozley]

I take your point, Mike, about not being too liberal with the facilities I offer to 'Administrative users'. At present I only have three operational systems (apart from my own), and perhaps have not let these users know about the option; so I am the only person who uses the facility.

I will give thought to further restricting the use of this facility. - Andrew

 

[mjcmkrsr]

x

 

[Gerrit Broekhuis]

If a user is ever allowed to browse / change, make sure the customer has formally agreed to use this function.
And check again they’re making proper daily external backups!

Regards, Gerrit

 

[mjcmkrsr]

Hi Andrew,

Andrew

---

An application offers a user with ‘Administrative rights’ the ability to browse any of the tables in the application.

Strange idea!

Why would you allow a user - were he/she an user with "administrative" rights - to do this. Several question arise

• Was this "super"-user part of the project team?
• Has he/she knowledge of the structure of the tables?
• Does he/she know how the tables are related?
• Does he/she know anything about the scope and the type of data?
• What does their IT department think about this?
• What does the employer of this "super"-user think about this "favor"?
• etc

I once was asked to allow a user to have access to the tables in a project. The result was absolute havoc - all data were mingled. I had warned the employer not to give permission but he ignored my warnings - fortunately for me they could not make me liable for what had happened. And "of course" they did not have backed up the data. Result: it took the team a week to put the data back on rail.

hth

MarK

 

[AndrewMozley]

Chriss, Griff, Mike

I thought that the SET DECIMALS TO 18 instruction would allow me to BROWSE a table which had a field with 4 or 5 decimal places to be displayed with full accuracy. I now find that this is not the case.
SET DECIMALS does indeed have an effect on expressions which do not have a specified number of decimal places. So the command '? 1/3' behaves differently according to SET DECIMALS.

I cannot work out how I managed (briefly) to get the data displayed in full detail.

Any further guidance would be much appreciated - Andrew

 

[mjcmkrsr]

Hi Andrew

From Hacker's Guide to Visual Foxpro

---

SET DECIMALS sort of determines the number of decimal places in the result. To be more specific, it determines the number of decimal places you'll see in the result, if the result has more places than the current setting. The correct calculation is performed and stored internally, but all subsequent displays of the result use the DECIMALS setting at the time the number was calculated. (If the result has fewer places than the DECIMALS setting and FIXED is OFF, the result is not padded with zeroes.

If that sounds confusing, it's because it is. Try this:

X1 = 10/3 && with default DECIMALS setting of 2
? X1 && 3.33, as expected
SET DECIMALS TO 5
X2 = 10/3
? X2 && 3.33333 - so far, so good
DISPLAY MEMORY LIKE X*
Interesting—X1 shows up as 3.33 while X2 is 3.33333. But it gets stranger.


? X1*2 && 6.67
? X2*2 && 6.66667
SET DECIMALS TO 18
? X1*2 && 6.67
? X2*2 && 6.66667
? X1*3 && 10.00
? X2*3 && 10.00000 - so no precision was lost in either case
The variables remember how many decimal places they were created with, even though you can see in the memory listing that the internal representations are the same.


What does all this mean for you? That you should choose a decimals setting for your application and use it throughout.

hth

MarK

 

[Chriss Miller]

Andrew,

the field type does not determine how many decimal places are displayed in BROWSE. You have that problem as developer, too. How do you solve it for yourself or do you even care? You know a N(x,y) field stores x places overall x-1 could be usable for decimals as the decimal dot itself is paart of X. The DBF actually stores a string of the value. Once you read a field with defined precision into a variable it becomes float. The number of decimal places associated with that float are an extra information that influences display and even calculations.

But I don't have a better advice as setting decimals to a higher value to see more than the default 2 places in a browse.

Could you point out a case where you don't see what you expect? If you want individual formatting with BROWSE you would need to use the NAME clause, which stores an object reference of the BROWSE - a grid object - into a variable of that name, and then you can act on that grids columns textbox format and or instead of displaying fields display expressions done with TRANSFORM and a specified number formatting. But the browse is the browse. And numbers (floats) are never exact anyway, despite you hit a value that is precisely that of a (binary) float number.

What's usually known about double flopats (that's the precision VFP works with, 64bit), you gett about 16 decimal places precision. So it's simple to store 4 or 5 decimal places exact, right? No, you can't even represent 0.1 exact in binary floats. The DBF of a N(2,1) field would store the strin ".1" for 0.1, so that's precise. But when you work with the data of that field, it's first converted to float, so you never have the exactness you think you have with numeric fields when it comes to calculating with them, or even just displaying them.

As I said in passing N fields are stored as strings of the numbers, you can see that with a hex editor, for example. But the browse does not simply show that string for N fields, it converts to double and displays that, taking SET DECIMALS into account.

To make it short: If you want to make science out of this, then, well, you have to, it gets complicated, not a simple set will give you exactly what you want. If you work with VFP and all its quirks, the admin you allow to execute any cde has to live with the same quirks. If you try to be better than the original, that won't work.

If the topic is really the precision of X decimal places, the only way to really offer that precision is with integers. So storing number with 4 decimal places exact is possible if you instead store 10000 times the value as an integer. The range of integers to about 4 billion won't allow very large numbers with this trick. The maximum stays 4 billion, of course, but divided by 10000 the max value becomes 400.000. You could work that way for a sufficient range, perhaps, depends on the case.