11.5.1 Win: Moving a table from MS SQL to OpenEdge and mysti

Posted by Stefan Marquardt on 29-Sep-2016 04:36

I like to share my experience and I expect that somebody will tell me in this group: You are very stupid you could use this way ...

The task was to move tables from SQL to OE.

Steps:
- used CREATE table menu in MS SSMS
- run this with a SQL client (Squirrel) in OE (R)DMBS after only modifying timestamp/datetime type.
- in PDS I could see every new table -> done!

But I was wrong.
As I tried to use the second table of these new ones I got a ABL compile error, field or table does not exists.
That was strange, it was shown on the left table view and auto-complete proposal worked fine.
After 5 minutes testing I was just before calling PSC but then I remembered that Squirrel shows a DBO schema which I ignored as a load failure from my previous tests.
Checked again, this table was not in pub, it was in DBO.
O.k. - my fault but why PDS shows it??

Let's fix it, create table script by tool, drop table and delete DBO and run again to re-apply it to the OE (R)DMBS, now Squirrel shows it in pub, like others.
I am done! 

No, what's this? PDS does not show the table like before, it's gone.
Restart DB, PDS -> The same effect.
Used DB Admintool, select this table and dump -> no df, it's empty like it has no fields, a zombie table, something new.
Why this table does not work as the other before? Something is different.
Compared create table script in Squirrel and identified a tinybit field definition (for logical)
After changing this to bit, drop and reload it seems to work as expected.

End of story

All Replies

Posted by Brian K. Maher on 29-Sep-2016 06:34
Stefan,
 
When using SQL-92 via either ODBC or JDBC you have the option of creating tables with “PUB” as the owner or with another owner.  For example, if you login using Squirrel using XZY as your userid and you do a simple “CREATE TABLE FRED” the table will actually be XZY.FRED and will only be seen via the SQL-92 side.  ABL based applications can only see tables that have an owner name of “PUB”.
 
This is standard behavior and has been this way ever since SQL-92 was introduced.
 
If you want the tables you create to be accessible by the ABL then your create statement must use the PUB.<TableName> in the CREATE statement.
 
PDS shows the tables because PDS uses a SQL-92 connection to the database.
 
Brian
Posted by Stefan Marquardt on 30-Sep-2016 01:11

Brian,

I am aware of the different schema but why the table was visible in PDS as it was with tinybit field type in DBO and then invisible after I created it in the pub (default) schema?

After created in pub the table was visible in the admintool but I could not dump the structure.

That's mystic - I think.

In Squirrel I activated for my local db set schema 'pub' now as default with the session plugin.

It's working now, the developer was very nice and fixed it.

Stefan

Posted by Brian K. Maher on 30-Sep-2016 04:28
Stefan,
 
Not sure but I expect it didn’t show up because of the data type.
 
Brian
This thread is closed