Translate error: Value out of bounds

[RobPouwelse]

Hi,

I've been trying to read records from an omnis database (from raining data) into delphi for editing and inserting into an access database. The problem that I've run into is that as soon as I try to retrieve the value of a date type field, the program stops with the error "Translate error: Value out of bounds"

From what I understand delphi has its own take on what the date field is like. For instance delphi might interpret 12/30/2000 as day:12 month:30 year:2000 which would be impossible (out of bounds). What I can't seem to figure out is how to make a workaround to the problem for instance reading the data in a raw form so I can specify it as a string and string-format my way through it myself.

for specifics:
- empty field is either "null" or "0/0/0" (not sure about that)
- omnis date notation is d-m-yyyy (day and month without a leading 0)

Thanks in advance,
Rob Pouwelse

 

[Zathras]

I assume you are using .AsDateTime

Try using .AsString and look at what you are getting.

If you post a little of your code to show what you are actually doing, you could get a more specific response.

 

[RobPouwelse]

I have tried the following solutions:

If the fields DataType is a date and isn't null then store the fields (raw) value in a temporary variable. srcQuery.Fields.Value returns a Variant.

    for i := 0 to srcQuery.FieldList.Count-1 do
    begin
      if srcQuery.Fields[i].DataType = ftDate then
      begin
        if not srcQuery.Fields[i].IsNull then
        begin
          tmpVariant := srcQuery.Fields[i].Value;
        end
        else
        begin
          SQLResultMemo.Lines.Add(srcQuery.FieldDefs[i].Name + chr(VK_TAB) +
                                  'Null');
        end;
      end
      else
      begin
        SQLResultMemo.Lines.Add(srcQuery.FieldDefs[i].Name + chr(VK_TAB) +
                                srcQuery.Fields[i].AsString);
      end;
    end;

If the fields DataType is a date and isn't null then display the fields DateTime in a memo using a specific format.

    for i := 0 to srcQuery.FieldList.Count-1 do
    begin
      if srcQuery.Fields[i].DataType = ftDate then
      begin
        if not srcQuery.Fields[i].IsNull then
        begin
          SQLResultMemo.Lines.Add(srcQuery.FieldDefs[i].Name + chr(VK_TAB) +
                                  FormatDateTime('dd/mm/yyyy',srcQuery.Fields[i].AsDateTime));
        end
        else
        begin
          SQLResultMemo.Lines.Add(srcQuery.FieldDefs[i].Name + chr(VK_TAB) +
                                  'Null');
        end;
      end
      else
      begin
        SQLResultMemo.Lines.Add(srcQuery.FieldDefs[i].Name + chr(VK_TAB) +
                                srcQuery.Fields[i].AsString);
      end;
    end;

Finally, f the fields DataType is a date and is null then display the fields definition name and 'null' else display the fields definition name and the fields value as a string type.

    for i := 0 to srcQuery.FieldList.Count-1 do
    begin
      if srcQuery.Fields[i].DataType = ftDate then
      begin
        if srcQuery.Fields[i].IsNull then
        begin
          SQLResultMemo.Lines.Add(srcQuery.FieldDefs[i].Name + chr(VK_TAB) +
                                  'Null');
        end;
      end
      else
      begin
        SQLResultMemo.Lines.Add(srcQuery.FieldDefs[i].Name + chr(VK_TAB) +
                                srcQuery.Fields[i].AsString);
      end;
    end;

All three solutions give the same "Translate error: Value out of bounds" error and in all three solutions the value of the field in the debugger is "Delphi exception EDBEngineError at $4B8F985

 

[Zathras]

If you step thru the code, I think you will find that Delphi is reporting the DataType as ftDate, so you never get to the point where you retrieve .AsString

Don't be so fancy while debugging. Just try
[tt]
for i := 0 to srcQuery.FieldList.Count-1 do
SQLResultMemo.Lines.Add(srcQuery.FieldDefs.Name
+ chr(VK_TAB) + srcQuery.Fields.AsString);
[/tt]
and see what happens.

 

[RobPouwelse]

It still gives the same error and states "Delphi exception EDBEngineError at $4B8F985" after evaluating the value of srcQuery.Fields.AsString.

Note: The first and only value of the Query is a Date (for testing purposes)

 

[Zathras]

I'm not familiar with omnis database, but is there any way you can apply a database function to the column as part of the query to convert it to a friendlier (i.e. string) format?

For example, in Microsoft SQL Server you could use [tt] Convert() [/tt] or in Gupta SQLBase you could use [tt] @DateToChar() [/tt] -- Hopefully, there is something available for that purpose in omnis.

 

[RobPouwelse]

I'm not sure since I don't have any documentation about the database but I'll see what I can find. If it's any help I'm using an ODBC driver to connect to the database, maybe ODBC has a default DateToChar() function?