LinkPoint and Error SGS-005000

We have had a number of customers receive the following error:

SGS-005000: The server encountered a database error.
[COLOR=Black]
There seems to be no rhyme or reason behind the receiving of this error.

A google search for the Error Number turns up the following:

[/COLOR]

SGS-005000: The server encountered a database error.

Make sure you have not exceeded the character limit for any of the fields. The <items><description> field has a limit of 120 characters.

[COLOR=Black]
None of those who receive it are from the same location or ordering the same products. We have had larger orders, and we have had smaller orders. We have had orders with the same products go through.

We have had folks receive this error on Linkpoint for CC, and then use their CC through Paypal and have it work properly.

I have a number of debug files for these folks, if somebody would be willing to take a look to see if there is anything that might be causing this error.

None of our product names (what shows up in the description field) is anywhere near 120 characters long.

We are at a complete loss to explain this problem...

Can anybody please shed some light on it...
[/COLOR]

I had this error today as well. I called linkpoint and they told me one of the fields contained more characters than allowed, though they didn't tell me which one. We are an engraving company so we use a lot of attributes including two text fields for instructions and engraving text. ALL of this information is passed to linkpoint (not sure why). Is there anyone that can help in this regard?

Does anybody know how to stop it from passing the Options?

We had a fellow try his order 1 item at a time and it seems to be multiple options causing it to blow up

What version of Zen Cart?
v1.3.8 already limits the 'description' parameter to 100 characters.

You may or may not find this patch useful: http://www.zen-cart.com/forum/showthread.php?p=520462#post520462

DrByte:

What version of Zen Cart?
v1.3.8 already limits the 'description' parameter to 100 characters.

You may or may not find this patch useful: http://www.zen-cart.com/forum/showthread.php?p=520462#post520462

I am running 1.3.8a. Below is the info from the cc review (x's added for personal info):
DECLINED
5466XXXXXXXX2809
Expires: 12/10
10-folO3c

SGS-005000: The server encountered a database error.

result=LIVE; oid=10-folO3c; ip=xx.xxx.xxx.xx; ponumber=; subtotal=21.95; tax=0; shipping=6.9; chargetotal=28.85; cardnumber=5466XXXXXXXX2809; cardexpmonth=12; cardexpyear=10; cvmindicator=provided; cvmvalue=xxx; userid=15; customerid=15; name=xxxxxx xxxxx; company=; address1=xxx xth Avenue; address2=; city=xxxxx xxxxxxxxx; state=Wisconsin; country=US; phone=xxx-xxx-xxxx; [email protected]; addrnum=xxxx xth Avenue; zip=53172; sname=xxxxxxxx xxxxx; saddress1=xxxx xth Avenue; saddress2=; scity=xxxxx xxxxxxxxx; sstate=Wisconsin; szip=53172; scountry=US; comments=Website Order; items=Array ( [0] => Array ( [id] => 5:2f803c6aa0a51baf41daf82c9d646829 [description] => Personalized Black Anodized License Plate [quantity] => 1 [price] => 21.95 [options0] => Array ( [name] => Do you want this item engraved? [value] => Yes ) [options1] => Array ( [name] => Font [value] => 27. Script MT Bold ) [options2] => Array ( [name] => Engraved Text [value] => THE RAVEN ) [options3] => Array ( [name] => Layout and Special Instructions [value] => I would like to have both words centered on the liscense, THE above RAVEN and also have the word RAVEN larger then THE. Email me with any questions [email protected] ) [options4] => Array ( [name] => Emailing add'l images or text? [value] => No ) ) ) ; ordertype=SALE;

I am wondering if it is because of all the attributes being sent, is that possible? And if so, can they be "turned off" as I don't think it's pertinent. Any help would be appreciated.

tstamplis:

I am running 1.3.8a. Below is the info from the cc review (x's added for personal info):
DECLINED
5466XXXXXXXX2809
Expires: 12/10
10-folO3c

SGS-005000: The server encountered a database error.

result=LIVE; oid=10-folO3c; ip=xx.xxx.xxx.xx; ponumber=; subtotal=21.95; tax=0; shipping=6.9; chargetotal=28.85; cardnumber=5466XXXXXXXX2809; cardexpmonth=12; cardexpyear=10; cvmindicator=provided; cvmvalue=xxx; userid=15; customerid=15; name=xxxxxx xxxxx; company=; address1=xxx xth Avenue; address2=; city=xxxxx xxxxxxxxx; state=Wisconsin; country=US; phone=xxx-xxx-xxxx; [email protected]; addrnum=xxxx xth Avenue; zip=53172; sname=xxxxxxxx xxxxx; saddress1=xxxx xth Avenue; saddress2=; scity=xxxxx xxxxxxxxx; sstate=Wisconsin; szip=53172; scountry=US; comments=Website Order; items=Array ( [0] => Array ( [id] => 5:2f803c6aa0a51baf41daf82c9d646829 [description] => Personalized Black Anodized License Plate [quantity] => 1 [price] => 21.95 [options0] => Array ( [name] => Do you want this item engraved? [value] => Yes ) [options1] => Array ( [name] => Font [value] => 27. Script MT Bold ) [options2] => Array ( [name] => Engraved Text [value] => THE RAVEN ) [options3] => Array ( [name] => Layout and Special Instructions [value] => I would like to have both words centered on the liscense, THE above RAVEN and also have the word RAVEN larger then THE. Email me with any questions [email protected] ) [options4] => Array ( [name] => Emailing add'l images or text? [value] => No ) ) ) ; ordertype=SALE;

I am wondering if it is because of all the attributes being sent, is that possible? And if so, can they be "turned off" as I don't think it's pertinent. Any help would be appreciated.

Notice the 2nd text array value for name "Layout and Special Instructions", it is more than 120 characters long. This is the only field I notice that is over 120 characters.

DrByte:

What version of Zen Cart?
v1.3.8 already limits the 'description' parameter to 100 characters.

You may or may not find this patch useful: http://www.zen-cart.com/forum/showthread.php?p=520462#post520462

I am using v. 1.3.8a

It is appearing as if Linkpoint is adding some text from the various options into the <item><description> fields causing the error.

We tested with a customer, he can successfully order with 1 attribute, but when he placed an order for one with 4 options, it threw the error.

Here is the debug file from one of the failed orders

Apr-12-2008 03:51:19
=================================

Response Code: DECLINED

Sending to Gateway: 
result = LIVE
oid = 960-q12dKi
ip = 71.182.231.207
ponumber = 
subtotal = 108.99
tax = 0
shipping = 9.19
chargetotal = 118.18
cardnumber = ******
cardexpmonth = 05
cardexpyear = 09
cvmindicator = provided
cvmvalue = ******
userid = 264
customerid = 264
name = William Hxxxxxxx
company = 
address1 = 39 Txxxxxxxx Ln
address2 = 
city = Spxxxxxxxxxx
state = New York
country = US
phone = xxx-xxx-xxxx
email = [email protected]
addrnum = 39 Txxxxxx Ln
zip = 14559
sname = William Hxxxxxxx
saddress1 = 39 Txxxxxxxx Ln
saddress2 = 
scity = Sxxxxxxxxx
sstate = New York
szip = 1xxxx
scountry = US
comments = Website Order
items = Array
(
    [1] => Array
        (
            [id] => 184:722d000d4aa3b69cc6a01b83ec227853
            [description] => HARP Combat Cards PDF
            [quantity] => 1
            [price] => 5.00
            [options0] => Array
                (
                    [name] => Download 1
                    [value] => PDF
                )

        )

    [2] => Array
        (
            [id] => 384:dca3197a19d64c2e93d1467573da9e4c
            [description] => HARP VP 2
            [quantity] => 1
            [price] => 86.10
            [options0] => Array
                (
                    [name] => With Bundled
                    [value] => PDF
                )

            [options1] => Array
                (
                    [name] => With Bundled
                    [value] => PDF2
                )

            [options2] => Array
                (
                    [name] => With Bundled
                    [value] => PDF3
                )

            [options3] => Array
                (
                    [name] => With Bundled
                    [value] => PDF4
                )

        )

    [3] => Array
        (
            [id] => 182:f5b9cdbd8bb9608c6dad80b55bbf2987
            [description] => HARP Gamemaster Screen
            [quantity] => 1
            [price] => 22.00
            [options0] => Array
                (
                    [name] => With Bundled
                    [value] => PDF
                )

        )

    [4] => Array
        (
            [id] => 181:a6d84afbf7f94c38446c7fc11eae6de3
            [description] => The Codex PDF
            [quantity] => 1
            [price] => 8.00
            [options0] => Array
                (
                    [name] => Download 1
                    [value] => PDF
                )

        )

)

ordertype = SALE
host = secure.linkpt.net
port = 1129
keyfile = xxxxxxxxxxxxxxxxxx/xxxxxx.pem
configfile = 560492
transactionorigin = ECI
terminaltype = UNSPECIFIED


Result: 
(
    [r_csp] => 
    [r_time] => Sat Apr 12 13:49:19 2008
    [r_ref] => 
    [r_error] => SGS-005000: The server encountered a database error.
    [r_ordernum] => 960-q12dKi
    [r_message] => 
    [r_code] => 
    [r_tdate] => 
    [r_score] => 
    [r_authresponse] => 
    [r_approved] => DECLINED
    [r_avs] => 
)

We had him purchase each item separately, and everything went through find, except for item #2.

And when we looked back through the other debug files, we found the same pattern -- all failing on items with more than 1 optional item listed.

Rasyr,

Have you been able to find a fix for this yet?

Sort of....

Where we had Value Packs (i.e. multiple books), and we wanted to be able to bundle PDFs with them, we dropped the individual listings for the PDFs, and instead did it as a single zip file containing all of the PDFs (so, instead of picking and choosing, you now have the option of purchasing none or all (though the PDFs are also sold separately, but the bundled versions were 50% normal price).

What appears to be happening is that Linkpoint is including at least SOME of the Option information in the description field (possibly all of it), so for each product, not only was it including the product name, but also "With Bundled PDF" for each PDF that was bundled, and 16 characters multiplied by 3 or 4 times per Value Pack really added up.

The ideal solution would be to find a way to make it so that the Options Information is not sent to Linkpoite (thus not overloading their "description" field).

BUt my attempts at doing that only ended up with getting a blank screen during checkout process....

Rasyr:

Sort of....

Where we had Value Packs (i.e. multiple books), and we wanted to be able to bundle PDFs with them, we dropped the individual listings for the PDFs, and instead did it as a single zip file containing all of the PDFs (so, instead of picking and choosing, you now have the option of purchasing none or all (though the PDFs are also sold separately, but the bundled versions were 50% normal price).

What appears to be happening is that Linkpoint is including at least SOME of the Option information in the description field (possibly all of it), so for each product, not only was it including the product name, but also "With Bundled PDF" for each PDF that was bundled, and 16 characters multiplied by 3 or 4 times per Value Pack really added up.

The ideal solution would be to find a way to make it so that the Options Information is not sent to Linkpoite (thus not overloading their "description" field).

BUt my attempts at doing that only ended up with getting a blank screen during checkout process....

Keep me posted here if you should find a solution. I am very suseptable <sp> to this as an engraver as people tend to leave long-winded instructions for layout.

I talked with linkpoint API today as I had another issue with an order. They confirmed it was the attributes that caused the error. Specifically, a text input field that had 240 characters in it (this is a text field used to provide layout instructions for the engraving). I THINK the code responsible for adding the attributes to the gateway submission from the linkpoint_api.php is:

// itemized contents
$num_line_items = 0;
for ($i=0, $n=sizeof($order->products); $i<$n; $i++) {
$num_line_items++;
$myorder["items"][$num_line_items]['id'] = $order->products[$i]['id'];
$myorder["items"][$num_line_items]['description'] = substr(htmlentities($order->products[$i]['name'], ENT_QUOTES, 'UTF-8'), 0, 100);
$myorder["items"][$num_line_items]['quantity'] = $order->products[$i]['qty'];
$myorder["items"][$num_line_items]['price'] = number_format($order->products[$i]['final_price'], 2, '.', '');
if (isset($order->products[$i]['attributes'])) {
for ($j=0, $m=sizeof($order->products[$i]['attributes']); $j<$m; $j++) {
$myorder["items"][$num_line_items]['options' . $j]['name'] = $order->products[$i]['attributes'][$j]['option'];
$myorder["items"][$num_line_items]['options' . $j]['value'] = $order->products[$i]['attributes'][$j]['value'];
}

Those last six lines seem to fit what I see in the linkpoint cc review screen with attribute "Name" then attribute "Value".

Now I just need to know how to remove it without damaging the rest of the code:)

if (isset($order->products[$i]['attributes'])) {
for ($j=0, $m=sizeof($order->products[$i]['attributes']); $j<$m; $j++) {
$myorder["items"][$num_line_items]['options' . $j]['name'] = $order->products[$i]['attributes'][$j]['option'];
$myorder["items"][$num_line_items]['options' . $j]['value'] = $order->products[$i]['attributes'][$j]['value'];
}

is what I tried commenting out and it gave me a blank white page. Apparently it looks for it elsewhere as well.

tstamplis:

I talked with linkpoint API today as I had another issue with an order. They confirmed it was the attributes that caused the error. Specifically, a text input field that had 240 characters in it (this is a text field used to provide layout instructions for the engraving).

It would be more helpful if they could tell you which specific fields are allowed to have which lengths, as it relates to products and options. Their API documentation does not specify *any* restrictions whatsoever. Having the details will help determine the most correct solution to the problem.

DrByte:

It would be more helpful if they could tell you which specific fields are allowed to have which lengths, as it relates to products and options. Their API documentation does not specify *any* restrictions whatsoever. Having the details will help determine the most correct solution to the problem.

Yeah they could be more helpful...

But, in the first post of this thread, I posted a definition of the error message which said that <item><description. field wasn't supposed to be more than 120 characters long.

And it has been verified that the Attributes are being counted towards that field. It would appear that the solution would be to NOT send Linkpoint the Attribute Information. We only send Name and Value anyways, not any addition information such as its individual costs, that is totaled into the full price.

What we are looking for is a way to NOT send those attributes to Linkpoint. We already know that if we can remove those Attributes from what is sent. Even if it is just replacing the list of Attributes with something that says Attributes Present instead of listing all of them (so that we know that they purchased something with Attributes when looking at the CC log for Linkpoint.

I just want to be able to offer multiple attributes without it causing every transaction containing them to fail.

This is the part that's building attributes:

      if (isset($order->products[$i]['attributes'])) {
        for ($j=0, $m=sizeof($order->products[$i]['attributes']); $j<$m; $j++) {
          $myorder["items"][$num_line_items]['options' . $j]['name'] = $order->products[$i]['attributes'][$j]['option'];
          $myorder["items"][$num_line_items]['options' . $j]['value'] = $order->products[$i]['attributes'][$j]['value'];
        }
      }

If you put /* and */ around that block of code, it should stop sending any attrib details.

You mentioned that you tried that and got a blank page ... which suggests that you caused some sort of other problem in the file by doing so. Are you sure you didn't leave any blank lines at the end of the file?
Try step 2a from this article to help find the bug causing your blank page: https://www.zen-cart.com/tutorials/index.php?article=82

DrByte:

It would be more helpful if they could tell you which specific fields are allowed to have which lengths, as it relates to products and options. Their API documentation does not specify *any* restrictions whatsoever. Having the details will help determine the most correct solution to the problem.

They told me the limit was 128 characters. The specific order I called about had 240 characters in one of the attributes values and that was what caused the error. This was from a text input box attribute for describing layout instructions for engraving.

I have a feeling this is a new limit from them as I have never had this problem before and I have used 255 character text input boxes. I am going to try to use your fix noted in this thread and will report back how it goes.

Thank you for your help, it is greatly appreciated!

You said:

Rasyr:

items = Array
(
[1] => Array
(
[id] => 184:722d000d4aa3b69cc6a01b83ec227853
[description] => HARP Combat Cards PDF
[quantity] => 1
[price] => 5.00
[options0] => Array
(
[name] => Download 1
[value] => PDF
)

)

[2] => Array
(
[id] => 384:dca3197a19d64c2e93d1467573da9e4c
[description] => HARP VP 2
[quantity] => 1
[price] => 86.10
[options0] => Array
(
[name] => With Bundled
[value] => PDF
)

[options1] => Array
(
[name] => With Bundled
[value] => PDF2
)

[options2] => Array
(
[name] => With Bundled
[value] => PDF3
)

[options3] => Array
(
[name] => With Bundled
[value] => PDF4
)

)

[3] => Array
(
[id] => 182:f5b9cdbd8bb9608c6dad80b55bbf2987
[description] => HARP Gamemaster Screen
[quantity] => 1
[price] => 22.00
[options0] => Array
(
[name] => With Bundled
[value] => PDF
)

)

[4] => Array
(
[id] => 181:a6d84afbf7f94c38446c7fc11eae6de3
[description] => The Codex PDF
[quantity] => 1
[price] => 8.00
[options0] => Array
(
[name] => Download 1
[value] => PDF
)

)

)

But, none of any of those fields is greater than 128 characters.
So, I still don't have a "correct" solution for you.

You can try the option I posted earlier to avoid sending any attribute info at all.

DrByte:

You said:

But, none of any of those fields is greater than 128 characters.
So, I still don't have a "correct" solution for you.

You can try the option I posted earlier to avoid sending any attribute info at all.

I wondered about that too, as I didn't notice anything long in that string. Is the text in your linkpoint cc review (admin/customers/linkpont cc review) for that order the same as well?

"the text in your linkpoint cc review" ??? I'm not sure I understand what you're asking about?

DrByte:

"the text in your linkpoint cc review" ??? I'm not sure I understand what you're asking about?

I was asking Rasyr that question. The text he posted that you quoted saying there are no fields greater than 120 characters came from his debug log. The information I used when calling Linkpoint was not from the log but from the text in the linkpoint cc review section in admin.