AbleCommerce Shopping Cart Software

Is it possible to change a field type in a table without making a mess of the DB? Specifically, due to problems exporting from UPS Worldship, we would like to change the OrderShipmentID in the TrackingNumbers table from int to another format, such as text or nvchar. Can this be done? And, more importantly, will it produce any undesirable results? Or, does the format have to match the format for the same field in the OrderShipments table, where the ID is pulled from?

No not possible, changing field type will create problems for sure because application will expect it to be int hence exceptions will be there after this change.

Is there any way to enlarge the field, then? When we try to export the tracking number from UPS Worldship the OrderShipmentID is being cut off at 4-digits. For example, if the OrderShipmentID is 12345, then after exporting to the TrackingNumbers table, the number shows up in the table as 1234. The tracking number shows full length, but is a different field type.

When we check UPS, it is importing the full 5 digits (it is brought in and used as a reference number, so we are able to tell that it is importing from our db to Worldship correctly).

From what I have been able to dig up, it seems that the problem is that the ODBC driver reads the field length as the number of characters, and does not allow Worldship to send the full number of characters as a result. Is there a way to increase the field length for an integer field?

I have really no idea what you are using to export data. Is this something custom or you are using DataPort for exporting orders.

The information is being exported by UPS Worldship using an ODBC connection. It seems that the ODBC driver interprets the field length for the OrderShipmentsId field as the maximum number of characters that it can export. If it is possible to change the field length for that field then it should solve the problem.

Does the field type for the OrderShipmentsId field in the ac_TrackingNumbers table need to be the same as OrderShipmentsId in the ac_OrderShipments table? Or is it possible to have the field stay as an int field in ac_OrderShipments, and change it to another field type in ac_TrackingNumbers? We will be importing the information for the field in the ac_TrackingNumbers table, not having AC generate it or transfer it from one table to another.

I did some messing around using a new table and found that if we used the bigint setting instead of int, that we were able to export the 5 digits to the table. Can we change the field from int to bigint without any effect? I believe that the only difference is that bigint allows for 8-bit instead of 4-bit, but it is still the same basic format. If we change the OrderShipmentsId field in the TrackingNumbers table, should we make sure that we also change the format in the OrderShipments table, to be sure that they match?

Well i think it should work fine in current scenario but in future it can create any other exception for you.

I am not sure, you can give it a try. I suspect that may be application can throw exception on type mismatch but I haven't tried anything like that before. You are going to change type from small to large type so there will be no data lost. Give it a go and test it by placing some order with data that can populate the field in question and see results.

Thanks. I will give it a try and report back. I suspect that, because we are staying with the same basic field type (int)- just a version that allows for larger numbers- that we should be fine.

UPDATE: We changed the field to bigint, and for some reason it causes a problem at the checkout. Basically, you cannot pay- it throws an error. Any thoughts?

Would we be able to add a new field to the TrackingNumbers table, and use that field for pulling the tracking numbers instead of using the OrderShipmentID? If so, which file would we have to change, and where in the file is the tracking number actually pulled from the db? I have spent a couple of hours going through files with no luck.

Well I don't think so that it will workout. Reason is that code that performs load/save is in back end. So if you add new field, back end code will not be aware about it hence it will not save new information or load data from that custom field.

The only table that we need to work with is ac_TrackingNumbers. Our export from UPS Worldship is able to write to the fields in that table. But, because the field length is listed as 4, only 4 characters are sent by UPS to the OrderShipmentId field.

Because there are only 4 fields in the TrackingNumbers table, and I beleive that the only one of those fields that is linked to any other tables is the OrderShipmentId (which we can write to manually and still succesfully pull the tracking information), could we not simply create a new table to replace the TrackingNumbers table? I believe that this table is used only for holding the tracking numbers. I am just not sure which file(s) specify where to pull the tracking information from.

Are you sure there is no setting you can change when you map your data from UPS? With an Able 5 store, we had orderIds that were 5 digits. Try calling UPS support and see if they can walk you thru the configuration- in the past they have been very helpful.

So far their tech has been useless on this. They insist that we need to expand the SQL field, which is not an option. The number will IMPORT into UPS from the db correctly, but it will not EXPORT correctly, as Worldship apparently is ignoring the field type and paying attention only to the length, so it interprets the length of 4 as a max 4 characters. I think that the tech people that I have spoken to are just not understanding the problem, and I have spoken to more than one.

Is there a way that we can add a field to the table and then change the relationship of OrderShipmentsId to the new field, and expand the existing OrderShipmentsId field to allow the longer information to be exported from Worldship? I tried this, but we get an error when bringing up the shipping information for an order, so I had to change it back.

One solution can be possible to resolve your problem like this

(1) Add a Sql Table which contains two fields one will be type int and other will be of type long int in your store db.Type int value will be used for your cut off value and long int value will be used for your original value.

(2)Write two code script one for inserting cut off value and original value in your added table during process and other for displaying actual value where it is needed but in back end cutoff value will be used.

e-g your actual value is '1234567' and cut off value was '1234' first script will insert '1234' in type int field and '1234567' in type long int field.
When you need to display actual value there you can query to get actual value like this
'Select actualValue from table1 where cutOffValue=1234 '.It will return you '1234567' which will be used for display.

I am not sure if this will work. The value that is being cut off is only needed so that the tracking number will disply correctly. In the ac_Tracking Numbers table there are the following fields:

TrackingNumberId
OrderShipmentId
ShipmentGatewayId
TrackingNumberData

The TrackingNumberId is auto-generated, so this is not a problem
OrderShipmentId is being cut off at 4 digits when we export from UPS Worldship into the table. With this number being cut off at 4 digits, the website will not show the tracking number for the order.
ShipmentGatewayId is fine, as the gateways are all 2 digits
TrackingNumberData is fine, as it allows up to 100 characters, and the tracking numbers are much less than that

So, we have to be able to find a way to get the 5 digits into the OrderShipmentId field in the ac_TrackingNumbers table. Maybe I am misunderstanding your solution, but I do not think that it will put the 5 digit number into the OrderShipmentId field.

Can we get AC to pull the tracking information from a different table? Then we can just create a new table that will allow us to use a long integer for the OrderShipmentId field, and we would export all of the information into it.