External Links

[wfl]

Has anyone tried an ODBC to an external table yet?

I have tried linking to our Visual Fox Xbase tables but get the same problem as with V7 and V7.5 despite having logged this one last year.

The linked field in Actinic pads out with white space to what looks like the declared width of the field in xbase. Is this a problem?

When a table is linked all components and attributes disapear. It looks as though I might be able to create new components and have them stick so it may just be a retrospecticve problem. No use though when adding this feature to a substantial pre existing catalogue.

I realy was hoping that this would have been fixed. Is there any chance of this before going live?

[Jan]

What data type have you used for your product reference field - there are several types of text field available, character, varchar and memo, if it is character, try using the others and see if it will link properly.

I am sure that I have sucessfully linked to foxpro in the past, without having this issue.

Regards,

[wfl]

We had the same discussion before Jan;

http://community.actinic.com/showthr...ghlight=foxpro

I am playing with it at the moment but it definitely seems as if it will let me ceate new components and atributes but when I import the existing db which is full of them, probably a couple of hundred) they just get blown away.

If you look at the previous post you will see that Actinic took it on board but that was the last I heard.

Also I am not sure at the moment about the padded fields. I need to get an exoprt and see what it does, if anything, to my imports into the back office.

[Jan]

So we did - I think that dydacomp have a interface from Mail Order Manager to Actinic these days - one of my customers used it for a while. I don't have the details so maybe they were doing the same as you and using external linking - seems unlikely though.

Regards,

[wfl]

Mailsolve's "link" is a conversion of the Actinic csv export files into a dbf for import into MOM. It works fine in as much as it is a reasonably direct output / input process but it is not a bidirecrtional link.

All I really need is to use the MOM data to populate Actinic. To be honest there is enough available betwen the two to ensure that Actinic stays up to date with pricing, qty, weight, shipping and even some of the descriptive stuff.

It looks as if the link works if I start afresh and create the components etc again but that is a BIG task.

The padding could be a problem. It is an Actinic problem because it creates spaces where there is no data (any other extract of the data using the same ODBC in Excel or Crystal only gets data and no spaces) to pad the field to the full data length. If it comes across in the export, as I believe it does, it could be a nuisance. I might see if Mailsolve will adapt their utility to overcome the problem.

The other thing that I am puzzled about is how linking will work in an MU environment. Either every instance will ODBC to the date or only the single admin instance, would I be right in presuming the latter?

[Jan]

The reason that the components etc dissapear is because the product reference is used as the foreign key in the product properties table - once the main product reference is padded the keys don't match

eg: ref : "XXX" in product, so product properties has "XXX" as well

after linking you have "XXX " in product and "XXX" in product properties so no match.

A hacky way of restoring your components would be to pad out the product reference field in the productproperties table, you could use SQL something like this in access

I can send you some SQL to do this if you wish - email me.

Regards,

[wfl]

Aha the insomniac coder at 4 a.m.!

That sounds very much like it to me. Email on its way.

However the padding should not be there so no one should be having to "work around" it. The fields aren't padded in Fox and if I create any kind fof data query in Excel or report in Crystal (I will try it again in Access to be sure) using the same flat table ODBC they don't pad the field. Ergo it is Actinic causing the problem.

At the moment creating comonents in my linked version of V8 (with all local tables let alone with both MU and the Fox table across the network) is very slow. Perhaps that is because it is trawling all the now dead links in the catalog?

Perhaps somone can answer my query about linking to MU? I can test all of this in stand alone V8 but I until we try the ultimate I can't apply it to MU.

[Jan]

> Ergo it is Actinic causing the problem.
Actinic uses standard ODBC linking - I think that you have this problem because of the field types used by the MOM database. To check how the field is linking look at the table called ProductLinked in Actinic. I do agree though that it would be nice if this was resolved within Actinic.

SQL is emailed BTW.

Regards,

[Jan]

I have just tried external linking in V7 MU and it is available for both administrators and order processors - I checked offline ordering, the linked product details were shown and the external stock levels were updated when I placed an order. I am fairly sure that V8 linking is the same.

Regards,

[wfl]

> Ergo it is Actinic causing the problem.

I am having difficulty seeing where it can be the external table fields. I work with them all the time for reporting and don't have a problem and I have worked similarly with Fox dbf tables in the past without a problem. As I see it;

MOM uses Visual Fox / xbase flat tables - nothing special there.
The fields I am linking are all "Character" - again nothing special there.
ODBC the same fields from Excel and Crystal - works fine no padding.
I use the self same ODBC for these as I do for Actinic - so it should work the same.

The problem is that the field as imported by Actinic pads the data to the xbase length using physical space characters even when these spaces don't exist in the original table.

Will have a play with the SQL as soon as I get a chance today and let you know.

[Jan]

Check the ProductLinked table in ActinicCatalog.mdb access - how long is the product reference field in there? That is a standard access linked table, if it is not padded in that table, then it is Actinic's fault, if is is padded, blame Microsoft.

Regards,

[wfl]

if is is padded, blame Microsoft.

Microsoft it is then. I am sat in front of it now and if I import the table into Access it comes across the with the Character and Value fields padded. I have checked again in Excel, Crystal and the DBF editor I use and it is not padded.

I have googled this and can find some references so I will go off and have a look see.

Still leaves me looking for a solution

[wfl]

Actually perhaps that is not the case.

In Access they look padded, i.e. the cursor will go to the field legnth or drop anywhere in that lenght. However you can't edit the field because it is realy a link to the external data base.

Looking on around I have found this pertaining to visual basic;

"When you use ODBC to insert or update values in a CHAR field, ANSI conventions dictate that the number of characters written to the field equal the total field length. Because of this ANSI requirement, the ODBC driver is designed to pad all fixed-length fields with spaces (Chr$(32)). Therefore, when you insert or update CHAR fields with Jet DAO, the Data control, RDO, the RemoteData control, or the ODBC API directly to, the data length returned by a subsequent query might be longer than the inserted or updated value's data length. To remove trailing spaces, use the RTrim or Trim functions. Note that if your data value is supposed to contain trailing spaces, it might be difficult to determine the correct number of trailing spaces."

That is what I need now how to do it?

[wfl]

Thanks Jan

Your SQL worked fine so now I have a QAD fix. It means that I could link the tables and retain all the components and from then all everything would be padded.

Going forward that would mean that the ProductRef displayed on the site would have a bunch of non breaking spaces on the end and order exports would have spaces on the end and need triming before being imported. It would also leave the issue in Actinic to come back in the future.

Perhaps someone can be persuaded to add the Trim command in Actinic so that it will import correctly.

[Jan]

If you don't need to update MOM from Actinic you could use a dynamically linked excel spreadsheet to linkto MOM and then link Actinic to that - you can set the excel sheet up to automatically updates itself, if this works, you will find that you components disappear again and you would have to trim the spaces off the refs in the property table to get them to come back again.

I've used the access - excel - actinic method before to link to a view in actinic, it works but you can't update from Actinic to access because although Actinic appears to update the external source, it updates the excel spreadsheet rather than the underlying access tables.

Regards,