ODBC to MSSQL 2008 troubles

ODBC Connectivity, ELFs , Windows API etc.

Moderators: Phil Winkler, Graham Smith, Pete Tabord

ODBC to MSSQL 2008 troubles

Postby lumberjackshaw » Fri May 20, 2011 1:02 pm

Good Day,

I have been trying to use the ODBC connection to import data from our previous database into Ffenics.

1. I am using SQL views to generate the data I need. I have to restart Ff to update the list of tables/views available to the link as refresh doesn't seem to do the trick.

2. I am routinely have to delete the database link, restart ffenics, add the database link back in, and refresh to "re-connect" my Ffenics forms to the data tables. (a bit of a pain but may the price paid to get the data from a network odbc connection)

3. Now I have a form that does not see all of the records displayed in the view. i.e. select * from myViewName generates 205 rows and table view of the remoteDB table (myViewName) in Ff has 47 rows. I have other tables that work fine. I am not sure why this one is different.

Does anyone have any ideas or experiences with this kind of issue?

Given the release of 1.52 I plan to test the situation on the new version to see if by some miracle the later problem goes away.
--Josh

Associate with men of good quality if you esteem your own reputation; for it is better to be alone than in bad company.
-George Washington
lumberjackshaw
 
Posts: 207
Joined: Mon Jan 17, 2011 8:07 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: ODBC to MSSQL 2008 troubles

Postby lumberjackshaw » Fri May 20, 2011 1:29 pm

Alas 1.52 did not resolve the issue. There must be something about the table that it does not like.

Any Ideas?
--Josh

Associate with men of good quality if you esteem your own reputation; for it is better to be alone than in bad company.
-George Washington
lumberjackshaw
 
Posts: 207
Joined: Mon Jan 17, 2011 8:07 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: ODBC to MSSQL 2008 troubles

Postby lumberjackshaw » Fri May 20, 2011 3:26 pm

Good Day,

I have switched my connection over to a OLEDB provider and while it does seem to be more stable I am still only getting part of the data.

In fact, further research has revealed that is doing the same thing on all of the tables. I hadn't noticed before because I had been connecting to views that filter out the data from the demo data in our previous app. When I browse the data tables direct, however, it is quite obvious that I am only getting a small percentage of the rows. Some of the really small tables ( < 100 rows) I seem to be getting complete.

For Examples sake :
My Customer table is giving me 159 of 209
My Vendor table is giving me 263 of 468
My Address table is giving me 3687 of 37323

Any input would be much appreciated. This is going to put a serious damper on my data migration plans. :(
--Josh

Associate with men of good quality if you esteem your own reputation; for it is better to be alone than in bad company.
-George Washington
lumberjackshaw
 
Posts: 207
Joined: Mon Jan 17, 2011 8:07 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: ODBC to MSSQL 2008 troubles

Postby lumberjackshaw » Fri May 20, 2011 3:48 pm

Good Day,

This issue is apparently limited to Ff.

I have connected to the database using the same udl file (OLEDB) via excel and it yields all rows.

Has no one else ran into this issue?
--Josh

Associate with men of good quality if you esteem your own reputation; for it is better to be alone than in bad company.
-George Washington
lumberjackshaw
 
Posts: 207
Joined: Mon Jan 17, 2011 8:07 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: ODBC to MSSQL 2008 troubles

Postby Adrian Jones » Fri May 20, 2011 4:21 pm

Hi josh,

To some extent you've answered your own question when you said there must be something that Ff does not like about the table.

Tell us about the columns in ONE of the failing tables. What about the data types? Their lengths?

What about the missing rows. Null values? Blobs? Anything?

And what is happening when you trace the sql connections?
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: ODBC to MSSQL 2008 troubles

Postby lumberjackshaw » Fri May 20, 2011 4:40 pm

Adrian,

As for one of the table designs for a simple table with the issue (CustCntct):
CntctKey (int, not null)
CustKey (int, not null)
CustID (varchar(40), not null)
EmailAddr (varchar(40), null)
Fax (varchar(17), null)
FaxExt (varchar(4), null)
Name (varchar(40), not null)
Phone (varchar(17), null)
PhoneExt (varchar(4), null)
Title (varchar(40), null)


On the data side...of 205 rows shown on the SQL server Ff is displaying the first 15. There is nothing different with record 16 and the same applies for each of the other data tables. The null values that exist in row 15 or row 16 are also found before 15.

Let me know if there is anything else you want to know.
--Josh

Associate with men of good quality if you esteem your own reputation; for it is better to be alone than in bad company.
-George Washington
lumberjackshaw
 
Posts: 207
Joined: Mon Jan 17, 2011 8:07 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: ODBC to MSSQL 2008 troubles

Postby lumberjackshaw » Fri May 20, 2011 4:53 pm

AHA!

Well not the answer I am looking for but I modified the data in row 16 and now I see up to row 22...

Apparently, it does not like a blank value or a null in the Phone field...
Yet the Fax field of the same type is full of NULL values...

Any ideas....short of writing a update statement to put a fake value in that field. :-(
--Josh

Associate with men of good quality if you esteem your own reputation; for it is better to be alone than in bad company.
-George Washington
lumberjackshaw
 
Posts: 207
Joined: Mon Jan 17, 2011 8:07 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: ODBC to MSSQL 2008 troubles

Postby Adrian Jones » Fri May 20, 2011 5:23 pm

Well, a quick and dirty solution migth be to write a view that returns an empty string where the field is blank.



I must state that I'm not presenting myself as an expert in ff and sql. I'm pretty good at Ff, and I've done a lot of sql stuff, including mysql and ms sql, but not with Ff.

I'm just making suggestions to see if we can break the ice on this.

(Also, its the end of the day here, and I'm on site visits next week, so hopefully someone else can step in)
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: ODBC to MSSQL 2008 troubles

Postby lumberjackshaw » Fri May 20, 2011 6:54 pm

Adrian,

Good call!

The catch is that it will accept a NULL value but not a blank value.
*Some field types will need a ' ' instead of NULL. Also, make certain to order by the table key.
Here is the code to fix it should any other poor sole run into it.
Code: Select all
SELECT
    ...,
CASE WHEN
    Phone IS NULL or Phone is = ''
THEN
    NULL *( ' ' is the safer option)
ELSE
    Phone
END AS Phone,
    ...,


Thanks for point in the right direction.

*Correction Added
--Josh

Associate with men of good quality if you esteem your own reputation; for it is better to be alone than in bad company.
-George Washington
lumberjackshaw
 
Posts: 207
Joined: Mon Jan 17, 2011 8:07 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: ODBC to MSSQL 2008 troubles

Postby Pete Tabord » Sat May 21, 2011 9:14 am

Oh, the old NULL vs blank stuff... :-(

In theory, there shouldn't be blank fields on a SQL server, but quite a lot of the theory has gone out of the window over the years.

Ffenics doesn't have NULL in the SQL sense, so we have always translated NULL (which in SQL isn't - or shouldn't be - a field _value_ its a field property stating the field has no value) as blank - presumably we don't have code to translate blank as blank!

I suspect any data you save from Ffenics would have any fields you left blank converted to NULL also.

Of course if you put ' ' in the field it isn't blank, it has a value of 'space character' which I think is 0x30.
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 

Re: ODBC to MSSQL 2008 troubles

Postby lumberjackshaw » Mon May 23, 2011 2:07 pm

Good Day,

Just a little more information for you guys on the OleDB that I have determined worth mentioning.

The OLEDB driver seems to have trouble consistently accessing nvarchar fields. I'm not sure why but sometimes the fields are visible on the RemoteDB table list and sometimes they are not. Either way the following code will easily rectify the situation.

SELECT
...,
CONVERT(varchar(YourFieldLengthHere),YourFieldNameHere) AS YourColumnNameHere
...
FROM ...
--Josh

Associate with men of good quality if you esteem your own reputation; for it is better to be alone than in bad company.
-George Washington
lumberjackshaw
 
Posts: 207
Joined: Mon Jan 17, 2011 8:07 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: ODBC to MSSQL 2008 troubles

Postby lumberjackshaw » Fri May 27, 2011 4:08 pm

Good Day,

I have stumbled onto an easier way to remove the null/blank issue.

SELECT
...,
rtrim(dbo.MyTable.Phone) AS Phone,
...,

Just a bit less typing :-)
--Josh

Associate with men of good quality if you esteem your own reputation; for it is better to be alone than in bad company.
-George Washington
lumberjackshaw
 
Posts: 207
Joined: Mon Jan 17, 2011 8:07 pm
Has thanked: 0 time
Been thanked: 0 time
 
 

Return to Advanced

Who is online

Users browsing this forum: No registered users and 1 guest