logo logo

 Back to main page

The NWNX Community Forum

 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
 
Doing Math in SQL - what am I doing wrong?

 
Post new topic   Reply to topic    nwnx.org Forum Index -> Scripts and Modules
View previous topic :: View next topic  
Author Message
FunkySwerve



Joined: 02 Jun 2005
Posts: 377
PostPosted: Tue Aug 22, 2006 8:41    Post subject: Doing Math in SQL - what am I doing wrong? Reply with quote
Code:

#include "aps_include"
void main()
{
string sAmount = GetLocalString(OBJECT_SELF,"bank_amount");
int nAmount = StringToInt(sAmount);
object oPC = GetPCSpeaker();
if (GetGold(oPC) >= nAmount)
    {
    string sKey = "xferbank" + GetPCPublicCDKey(oPC);
    TakeGoldFromCreature(nAmount, oPC, TRUE);
    //int nBanked = GetPersistentInt(oPC, sKey);//GetCampaignInt("bank", sKey, oPC);
    string sBanked = GetPersistentString(OBJECT_SELF, sKey);//current balance
    //int nTotal = nAmount + nBanked;
    if (sBanked == "") sBanked = "0";
    string sAdd = IntToString(nAmount);
    string sSQL = "SELECT "+sBanked+"+"+sAdd;
    SQLExecDirect(sSQL);
    string sTotal = SQLGetData(1);
    //DeleteCampaignVariable("bank", sKey, oPC);
    //SetCampaignInt("bank", sKey, iTotal, oPC);
    SetPersistentString(OBJECT_SELF, sKey, sTotal);
    }
}


Im trying to convert my banking setup to do math in SQL, to avoid that pesky 2.1B limit (believe it or not, its an issue). No matter what I do it won't work, though it works flawlessly using normal persistence, so I'm guessing its the way I'm tring to add numbers. I altready dealt with the NULL+number = NULL problem via the zero string. Can I not use SQLGetData this way? Any help appreciated!

[Edit] More specifically, it is creating the entries with a blank value, or thats the way it appears from the SQL command prompt checking Im doing.

Funky
Back to top
View user's profile Send private message
odenien



Joined: 26 Sep 2005
Posts: 37
PostPosted: Tue Aug 22, 2006 14:58    Post subject: Re: Doing Math in SQL - what am I doing wrong? Reply with quote
[quote="FunkySwerve]
Im trying to convert my banking setup to do math in SQL, to avoid that pesky 2.1B limit (believe it or not, its an issue). [/quote]
2.1GB maybe? If so, converting StringToInt for nAmount will wrap. You will need your script to be able to handle a long long (int64), not just a long (int32).

from what I see, you are trying to perform the following SQL:

"SELECT 2147483647 + 12147483647"; fetch := "14294967294"

try

"SELECT '2147483647' + '12147483647'"; fetch := "14294967294"

or in your script

string sSQL = "SELECT '" + sBanked" + "' + '" + sAdd + "'";

Another thing:

GetGold and TakeGoldFromCreature use int which is a 32bit int. Those functions can not handle what you are trying to do.

If (GetGold(oPC) >=nAmount) needs to change to some kind of string compare. Create a function that will right fill a variable padded with zeros two number in the for of strings and compare.

The TakeGoldFromCreature line needs to be a function that loops to take out in MAXINT32 amounts until the string amount has been found.

Code:
#include "aps_include"
void main()
{
    string sAdd = GetLocalString(OBJECT_SELF,"bank_amount");
    object oPC = GetPCSpeaker();
    if (GetGold(oPC) >= nAmount) // Change to string compare code
    {
        string sKey = "xferbank" + GetPCPublicCDKey(oPC);
        TakeGoldFromCreature(nAmount, oPC, TRUE); // change to string delete function.
        string sBanked = GetPersistentString(OBJECT_SELF, sKey);//current balance
        if (sBanked == "") sBanked = "0";
        string sSQL = "SELECT '" + sBanked" + "' + '" + sAdd + "'";
        SQLExecDirect(sSQL);
        string sTotal = SQLGetData(1);
        SetPersistentString(OBJECT_SELF, sKey, sTotal);
    }
}
Back to top
View user's profile Send private message MSN Messenger
FunkySwerve



Joined: 02 Jun 2005
Posts: 377
PostPosted: Tue Aug 22, 2006 16:23    Post subject: Re: Doing Math in SQL - what am I doing wrong? Reply with quote
odenien wrote:
FunkySwerve wrote:

Im trying to convert my banking setup to do math in SQL, to avoid that pesky 2.1B limit (believe it or not, its an issue).

2.1GB maybe? If so, converting StringToInt for nAmount will wrap. You will need your script to be able to handle a long long (int64), not just a long (int32).

from what I see, you are trying to perform the following SQL:

"SELECT 2147483647 + 12147483647"; fetch := "14294967294"

try

"SELECT '2147483647' + '12147483647'"; fetch := "14294967294"

or in your script

string sSQL = "SELECT '" + sBanked" + "' + '" + sAdd + "'";

Thanks, will try.
Quote:

Another thing:

GetGold and TakeGoldFromCreature use int which is a 32bit int.

Yup, that's why I'm making this conversion.
Quote:

Those functions can not handle what you are trying to do.

Of course they can. The max gold a PC can carry is 999,999,999, and there are stariong conditionals in place to prevent accidental losses from overspill of that amount, as well. Razz The string compare is needed some places, but not there:
Code:

#include "aps_include"
int StartingConditional()
{
    string sAmount = GetLocalString(OBJECT_SELF,"bank_amount");
    int nAmount = StringToInt(sAmount);
    object oPC = GetPCSpeaker();
    string sKey = "xferbank" + GetPCPublicCDKey(oPC);
    string sBanked = GetPersistentString(OBJECT_SELF, sKey);
    if (sBanked == "") sBanked = "0";
    int nLength = GetStringLength(sBanked);
    if (nLength > 9)
    {
        string sCheckSign = GetStringLeft(sBanked, 1);//there should be no way for a pc to accumulate this big a negative, so this is really just added security
        if (sCheckSign == "-") return TRUE;
        else return FALSE;//keeping within int bounds for the comparison, and PCs may only carry 999,999,999.
    }                //if they have 10 digits they have enough for sure
    else
    {
        int nBanked = StringToInt(sBanked);
        int nRemaining = nBanked - nAmount;
        if (nRemaining < 0)
        {
            return TRUE;
        }
    }
    return FALSE;
}


Above is from a starting conditional. There are also checks on withdrawal, and a few other places. Alot simpler than doing the StringToInt conversion every time. I'll try the quotes and report back. Just seems odd, but select '7'+'8'; works in command line SQL, so it's worth a shot.

Funky
Back to top
View user's profile Send private message
FunkySwerve



Joined: 02 Jun 2005
Posts: 377
PostPosted: Tue Aug 22, 2006 16:54    Post subject: Reply with quote
Ok, adding single quotes didnt work. A devug string revealed that this:
string sSQL = "SELECT "+sBanked+"+"+sAdd;
SQLExecDirect(sSQL);
string sTotal = SQLGetData(1);

is returning a blank string. Anyone have any other ideas? Gonna start looking through aps include, I guess... Confused
Thanks,
Funky
Back to top
View user's profile Send private message
FunkySwerve



Joined: 02 Jun 2005
Posts: 377
PostPosted: Tue Aug 22, 2006 17:30    Post subject: Reply with quote
Mmm, a look at aps include seems to indicate I need to use SQLFetch first. Here goes another try... Smile
Funky
Back to top
View user's profile Send private message
FunkySwerve



Joined: 02 Jun 2005
Posts: 377
PostPosted: Tue Aug 22, 2006 17:59    Post subject: Reply with quote
Yup, adding a SQLFetch just before the SQLGetData did the trick! Obvious, now that I actually had a look at those functions... Embarassed
Funky
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    nwnx.org Forum Index -> Scripts and Modules All times are GMT + 2 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group