D3 ODBC Problem
SteveS
get the following error.
Error: Query aborted - non-numeric value encountered when numeric
required (State:S1000, Native Code: 0)
I've looked at the record that it stops at and the following record
and the field appears to be fine. It is a Quantity field that is
numeric but I can not see any bad data.
Can anyone offer a suggestion to get around this problem?
Thanks,
Steve
x
Often you have blanks or a nonprintable characters in the midst.
Just to make sure do a print such as:
FLD = ...
PRINT @(-13):OCONV(FLD,'MCP'):@(-14)
Check also for the number of periods not to exceed 1.
Lucian
SteveS
I've written a program to check for non-numeric or non-printable
characters and there is no bad data in the field. It must be a bug.
x
> characters and there is no bad data in the field.
Check also the dictionary definition of the item.
It might have some conversion or calculation you don't want.
For example if your statement is ....TOTAL QTY... check dict QTY
fields 7 & 8 for any surprises or instead of QTY use A1 A2 etc for raw
data.
Lucian
Mark Brown
news:cfecbccb-6271-4b9e...@b30g2000prf.googlegroups.com...
Is the field multivalued? If so, even though each value is numeric, the
value mark will make the query choke.
If the field IS multivalue, you'll have to account for that when you do the
SQL-CREATE-TABLE in Pick.
--
Mark Brown
Sr. Software Engineer
Cancer Survivor
Drexel Management Svc Inc.
Frank Winans
> D3 ODBC Problem:
> Having a problem with a file in D3. While use Winsql to troubleshoot I
> get the following error.
>
> Error: Query aborted - non-numeric value encountered when numeric
> required (State:S1000, Native Code: 0)
>
> I've looked at the record that it stops at and the following record
> and the field appears to be fine. It is a Quantity field that is
> numeric but I can not see any bad data.
>
> Can anyone offer a suggestion to get around this problem?
>
> Thanks,
>
> Steve
instead of, say, text?
For instance, I had a file in d3/linux with dict entry for Name
and one for Zipcode, both had very similar dict contents, but
with most of my zipcodes being nnnnn-nnnn or just nnnnn
that field sucks into an Excel spreadsheet {via Microsoft Query,
as usual} as type Numeric, and gave a barf message quite like
you got when it found an item with just a "-"
[I wonder if you've got a space or some control characters in your d3
items that is tripping the 'non-numeric' condition in the odbcsrv
d3 odbc driver...]
That is, does the 'numeric' data type get determined each time you
run a query, or is it guesstimated from a short survey of data present
on the d3 file when you do your sql-create-table command? If the
latter, then your workaround would be to temporarily replace your
file data with a few sample items but stick lotsof text in this problem
field, then create the table again with the update flag, and it will
consider the field text, or at least not straight numeric, even after you
put back in place your true desired data on the d3 side.
A better solution would be to write a basic program to comb your data
for non-digits and report back any odd data found. Make sure your
data entry staff knows not to use their arrow keys when correcting typos,
as the escape sequences they create only mask the typo, not truly fix it,
and those escapes will upset d3 and odbc mildly.
SteveS
>
> - Show quoted text -
Thanks for all the replys:
The dictionary entry:
::CT DICT INVL QOH
QOH
001 A
002 6
003 Quantity]On Hand
004
005
006
007 MR0
008
009 R
010 8
The data actually sub-valued and the SQL-CREATE-VIEW statement is
correct also. The table displays properly for some 12000 records
before barfing.
And I did write a program to comb the data and the data is clean.
D3 ODBC sucks
Tony Gravagno
>And I did write a program to comb the data and the data is clean.
removepleaseNebula-RnD.com/freeware/FIND.BAD.CHAR.TXT
>D3 ODBC sucks
If I had said that, all hell would break loose. Suffice to say, you
know the solution I would propose.
T
Mark Brown
message news:fcck84hesns7f8ge1...@4ax.com...
Error messages from WinSQL are usually fairly accurate. If it says invalid
data, it's probably invalid.
Poster said they scanned the data and there's no non-numeric data and that
the field is normalized correctly in the SQL-CREATE-TABLE.
Next question is, what IS the data, exactly? Just because it's numeric,
doesn't mean it's valid. If it's > 32K, then depending on which version of
Windows O/S and which version of the SQL query, it may think an integer is
16bits and the data is invalid because of overflow.
Peter McMurray
"Frank Winans" <fwi...@sbcglobal.net> wrote in message
news:oIqdnRBvWL5ZmRfV...@posted.internetamerica...
<snip>>
Make sure your data entry staff knows not to use their arrow keys when
correcting typos,
> as the escape sequences they create only mask the typo, not truly fix it,
> and those escapes will upset d3 and odbc mildly.
>
would write a program that does not validate the data entered before filing
it. Ah! just answered my own question some people may allow the users
access to editor in which case Tony's bullet is an appropriate response.
For the system guy that is not the user.
Peter McMurray
Peter McMurray
And I did write a program to comb the data and the data is clean.
D3 ODBC sucks
Hi
I think that you will find that D3ODBC is actually a quite good third party
product that they bought because the in house version was not so hot. I am
afraid that after 40 years programming I am inclined to believe the error
message. Did your data vet program check for a null for example? Have you
actually dumped the data items in question with the GX option ?
Peter McMurray
Frank Winans
> "Frank Winans" wrote
> > Make sure your data entry staff knows not to use their arrow
> > keys when correcting typos,
> > as the escape sequences they create only mask the typo
> >
> would write a program that does not validate the data entered before filing
> access to editor in which case Tony's bullet is an appropriate response
> Peter McMurray
Nah, your first guess was right, I'm talking unverified data entry applications.
A lot of sites we encounter really don't understand the tools they've inherited,
and have no access to the firms that developed them. And database layout
is driven by what looks nicest to the owner, who likes to browse data in ED.
So I find a files with same zipcode repeated as 12th value of each attribute of
all items, and those items grow {at the TOP} by one line each calendar day.
Or a file with nnnn.nn format data on on file instead of nnnnnn with mr2 in the
dict. Makes me cringe. The R83 shops were the worst, but over time they've
largely gone defunct.
Come to think, we use a screen generator package ourselves, and the user
input module didn't check for control characters for the first ten years or so
despite a really impressive stream of other code enhancements. It seems
nobody really cared about that missing feature very much.
I've seen managers avoid general solutions like getting the code to use terminal
type instead of hardcoded escape sequences, going so far as to buy the exact
same model of green screen for all the desks, ages after that model was out of
production. Software changes worry them, you seee.
SteveS
> "Tony Gravagno" <address.is.in.po...@removethis.com.invalid> wrote in
> messagenews:fcck84hesns7f8ge1...@4ax.com...
Created a support call with TL. They could not find a problem with the
data either. They now have my data and are trying to reproduce at
their facility.
Will let u know the outcome.
Steve
GlenB
only have occasional data-oriented hiccups. For the most part it works fine
for me.
Glen
"SteveS" <s...@perfectionsoftware.com> wrote in message
news:bd831f3c-ad0e-4a77...@m44g2000hsc.googlegroups.com...
Peter McMurray
The very first program we ever wrote so that we could write our 4gl was
ScreenGen that did all the data display and edit. I can honestly say that I
have never given a user a production program that did not validate the data
since I started commercial programming in 1973 with NEAT3 and Pick in 1977.
The examples you give are horrific. I have seen some phenomenally bad file
designs but even then the data was normally correct.
The only time I have seen editor used was the raw beginnings of Peter
Harvey's very successful hospital package. In the beginning he had a
gentleman from the hospital come in on Thursday afternoons and spend 3 hours
keying in his data, then sort and list it, going home very happy. But that
was 1977 and Peter was flat out often sleeping at the office, he soon wrote
a proper program.
Peter McMurraynews:S6OdnbZqutWOuRbV...@posted.internetamerica...
Tony Gravagno
that data validation beyond what's required for specific context is
not the norm. Pick guys mostly code like this:
CRT @(x,y):"ID please":
INPUT ID
READ REC FROM FV,ID ELSE...
CRT @(x,z):"Info please:"
INPUT INFO
REC<Q> = INFO
WRITE REC ON FV,ID...
The people who have been doing this a while (the entirety of present
company, I'm sure) have subroutines (internal or external) for screen
output, user input, reads, and writes. Many people claim this is
their M.O. but have a look at code coming in from non-telnet sources
and everything breaks down. Examples include data input from FTP
transfers, data received from web services, data entered in browser
forms, data imported from CSV or other formats... Somehow there is the
assumption that if the means of acquisition is not an INPUT statement,
at least when it's not hooked to a user via telnet, that the data must
be good. The idea doesn't really occur to people that they should be
running all of this data through the same cleansing functions - since
the MV DBMS model doesn't do this natively, as DBMS administrators we
need to make sure the application provides the services. Some sites
do this with triggers. I dunno about other platforms but I prefer to
avoid triggers, particularly with D3.
Lest the grass look greener on the other side, one of the things
provided by the relational model is referential integrity enforced at
the DBMS level. While RDBMS guys use this to beat us over the head,
many of them also leave this as the last thing they do, right down
there with comments, error handling, and optimizations.
T
Tony Gravagno
>I think that you will find that D3ODBC is actually a quite good third party
>product that they bought because the in house version was not so hot.
I'll preface this all with "to my knowledge", and I welcome
correction. The software which you describe was licensed at an
overall cost in the high 5 figures and I wouldn't be surprised if
someone confirmed the number was well over 6 figures. After all that
I don't believe it was ever used. The ODBC connectivity has always
been the home-grown stuff, so the problems and lack of functionality
present today are of the same category that people were struggling
with almost 10 years ago.
The way the history goes, D3 v8 was supposed to have all sorts of
goodies, including FSI for *nix and the new ODBC client/server. After
going so long with v8 in the pipe but nowhere near close to being beta
quality, RD decided to roll back a lot of the functionality into v7.5.
Not only did this potentially give them the brownie points for
releasing real enhancements in a point-release, but by keeping this a
minor release it allows anyone on a support agreement to get the new
features for free. If they had committed to v8 then people would need
to pay for the update, and considering what's been going on with v7.5
that would have been very bad all the way around. Unfortunately the
new ODBC connectivity was bound to v8, so it was never rolled back to
v7.
The "new" ODBC code is now so old and moldy that I personally wouldn't
have much faith in it. Consider that with all the time that's gone
by, TL would need to get it updated for Vista and W2008 as well as
re-fitted into the latest D3. Given what's going on with D3 7.5 for
current platforms and Vista and W2008, the chance of them
accomplishing all of these feats anytime in this decade are, IMO, just
not worth a bet.
At this point for anyone looking for serious ODBC, I think migration
to a new DBMS would be less expensive and could be accomplished in
much less time. From what I've seen, both Caché and Reality have
exceptional relational handling as well as a clear migration path from
D3. Calling it as I see it, sorry.
T
Peter McMurray
Rick Davies told us that this was the case when he came to Australia to
introduce the new D3 from Raining Data along with the new management team
about 6 years ago. So heaven knows which issue it is now.
I must admit that I am very concerned about their failure to address Vista
as this is the basis of Server 2008 and like it or not this is where the
world is going.
Peter McMurray
"Tony Gravagno" <address.i...@removethis.com.invalid> wrote in
Peter McMurray
I must be weird. I actually think that a program with more than one INPUT
and associated edit routine is seriously weird. Sorry I should say two as I
have a separate routine for my control line (Save, Exit, Change Field) to my
field data entry routine. In NEAT3 I only had one but moved to two when I
switched to Reality and VTE6 screens in 1977.
I do remember one British expert going a little slack-jawed when I showed
him a screen with 42 fields (as requested by the user) and happily hopped
about changing fields at random. He did ask if I realised that the accepted
wisdom was to start the entire screen again and prompt for each field in
turn. I think I looked a little bemused and asked if he hadn't heard of
event driven programming (this would have been around 1979-80).
Ahah! That phrase "accepted wisdom" reminds me of another party deriding
complex keys and saying did I not realise that the accepted wisdom was to
use random numeric keys for all Pick data. Of course I was not impressed
and wondered why one would do such a thing. Now I realise that this was the
"accepted" way of getting around the foolish concept of case sensitivity so
that one did not wind up with stock items of "nails" and "Nails" and
"NAILS". My approach to case-sensitivity is to ban it for any key. I
simply set alpha keys to being Upper case on entry and yes I always validate
that it is A-Z and if I ever have to use Cyrillic I am sure I can do a
similar trick there. We did some work recently on a banking system that did
not have decent standard edit facilities so the first thing I did before we
cut a line of operational code was to write an edit routine that we used at
all times. I do remember, with some amusement, a tester sitting with her
finger on the keyboard saying with a smile "I'll break this yet". She
didn't break it but she did get an awful lot of bells back.
As for external sources, I actually have a wide variety of sources and I
assume that all are suspect. My standard approach is to set up a standard
interface to the master system say for card purchases through tills or card
acceptors and then pass each suppliers info through a conversion program
that validates every attribute. Errors in the data passed down are frequent
from some suppliers and they are definitely rejected before they hit my
database. This makes it extremely simple to incorporate a new supplier such
as happens with business take-overs.
Also the approach makes changes, debugging and training much simpler as
every screen is identical in operation. In fact only last week I had an
issue that took ten minutes to fix. A bank in its wisdom decided to change
a six numeric key to six numeric with leading zeroes. I regard leading
zeroes on numbers as even more stupid than case sensitivity and edit them
out, anyone who has seen the chaos caused by false emergency calls in
Australia would understand - the emergency number is triple zero and the
area codes start with zero, so what happens is people in an office dial zero
to get a line, followed by another zero for the area code, glance at the
number to double check and promptly enter another zero. Sounds OK but many
new office systems do not need a zero to get a line so up pops emergency.
In my programming approach I simply changed my global dictionary for the
bank number to alpha numeric and added a string check of (6N) to it.
Problem solved, edit all OK and error message OK as well.
Peter McMurray
"Tony Gravagno" <address.i...@removethis.com.invalid> wrote in
message news:nv8q84h40sl7du8b0...@4ax.com...