Subject Re: [IB-Conversions] PDOXPUMP
Author Helen Borrie
At 03:07 PM 29/01/2003 +0000, you wrote:
>Helen, I've run into a different problem now. Here is whats happening
>now.
>
>1. Using IB_SQL I've created a database (dialect 3)
>2. Executed IB_Domain.SQL
>3. Browsed the database and the Country, IB_Domain, and IB_Domain_Map
>tables are created.
>4. Executed PDOXPUMP
>5. Logged into database
>6. On Metadata and Data Pump screen, checked Do Datapump and Dialect
>3 boxes.
>7. BDE login and loaded table to pump
>8. Added to scrip the single primary index (field of VARCHAR(3))
>9. Refreshed Domains
>10. Selected each field with domain types and selected the proper
>domain type in the combobox.
>11. Clicked add to script for table.
>
>At this point I get the error message 'Invalid Variant Type
>Conversion'.
>In the table script window is the following:
>
>Create Table Vehicle(
> Veh Varchar(3) Not Null
>
>Out of curiosity, I clicked the Add table script again and received
>the same error msg above. And now in the table script is:
>
>Create Tabel Vehicle(
> Veh Varchar(3) Not Null
> P_Date D_Date Not Null
>
>Clicking the add table script once more added to the table script the
>create table vehicle( line and all fields from the pdox table.
>
>However there was nothing in the Domains and Generators window.
>Naturally I can't pump the table without the Domains being created in
>the script and I forget the exact systax to use.
>Anyway, can you send us a fix?

There is no "fix" for this. The tool will create the statements for any
generators that you indicate are needed and it does offer some suggested
default domain definitions for some types. The window is there to provide
feedback on generators and domains that are created at the time you request
them and, at the right time, it will add those newly-created statements to
the script. If the default domains don't fit your data, you need to edit
them manually. Once they have been added to the script, any further
editing of them needs to be done in the main script. The program doesn't
go back and compare the statements in the main script with what's left
behind in the contributing windows. It also doesn't later parse through a
saved script and extract your CREATE DOMAIN and CREATE GENERATOR statements
back into the feedback window - otherwise, potentially, they would be added
again to the main script. If you are working on a saved script, look in
the window in the bottom right corner (I think) - where the latest version
of the script (whether previously saved or not) should appear.

I suspect the invalid variant type errors may be coming from my code
calling the AsDateTime method on a value that resolves to NULL . The
"As... methods work by casting variants to types. NULL isn't a variant,
and I know I handled this in the original code using NULLVAR, which worked
properly in IBO3.

The tool was built with IBO 3 in Delphi 5 and tested with IB 5.6, IB 6.0
and the first Firebird beta. When I recompiled it the other day, I used
the latest IBO4 and Delphi 6. A lot has happened to IBO, Delphi and
Firebird since then, so it would be worth reviewing the variant
handling. Perhaps IBO4 has changed the way NULLVAR is interpreted, because
Delphi 6 had some pretty scary bugs concerning variants and Jason generally
does try to make workarounds for Delphi bug...I can't promise to get to it
straight away, though, as I have urgent customer work to do right
now. And, dammit, it's 38 deg C in the shade here today! :-|

I'm a bit dubious about you apparently piggybacking re-runs of the app upon
one another. The ultimate script - the one that you decide to use for the
pump - has to be a complete script, not bits and pieces...it's not using
ALTER <object> on subsequent runs to override anything you have already
created in the main script or in the database.

You have to get all the metadata right before you start pumping
data. Trial runs to test stuff and find likely problem areas are fine, but
you need to be prepared to delete stuff and re-run it until you are
satisfied that your metadata will work with the incoming data.

As the notes say, this utility really does require that you understand what
will happen when the metadata script runs and be capable of editing it to
fit with what you know about the actual input and output data. The main
benefit of the tool (provided you understand what's going on) is that it
makes it possible for you to create the metadata and pump the data within
one UI. It doesn't pretend to be bomb-proof - it's a merger of several
little utility progs I'd developed for myself over the years for pdox to ib
conversions, using the BDE on both sides of the boat - hence the need to
keep the steps in strictly the right order.

When I started using IBO for the data-pumping, I was so astonished by its
speed that I suggested to Jason that it might be useful to work my utility
routines into some sort of integrated migration tool, to help out in cases
like yours. Pdox2IB was the result. It would have been nice to have time
to make it "newbie-proof" but there wasn't, and it's not.

If you need more info about the Dialect 3 types, read the IB 6.0 beta
Migration Guide, which you can download from the IBPhoenix site. It sounds
as if you could do with the Language Reference and the Data Definition
Guide as well. :-)

heLen