[Thomas.]

Hi all,

Currently I'm having some issues with a really long MySQL query.

At first I tried to put each statement on it's own line, like this:

Код:

format(query, sizeof(query), "UPDATE `example` SET
example  = 'foo',
example2 = '',
...

And so on

This resulted in a "error 037: invalid string (possibly non-terminated string)".

Then I tried to just put the whole query on 1 line, which caused it to be a very long line, with result "error 075: input line too long (after substitutions)".

How should I solve this problem?

Thomas

[Vince]

Use a backslash (\) at the end of each line.

[Calgon]

When I deal with long queries I usually create a really large string (255+ cells) and use separate lines and use format() a couple of times, like so:

pawn Код:

new largestring[255];
format(largestring, sizeof(largestring), "UPDATE USERS SET ='' <something to make this continue go on forever>"), strings, etc);
format(largestring, sizeof(largestring), "%s, <and more>", largestring, otherstrings/variables);

[Thomas.]

Thank you for the replies, it seems to work.

However, I'm experiencing a new problem.

The variable in which the query is stored has to be very big, because it's a very long query.
But when I set the size of data storage of the variable higher than 1024 (not sure if it's exactly 1024), the whole server seems to behave unexpected; for instance, I compiled the script to see if it works, I logon to the server... and none of the commands were working...

When I changed the size of the var back to 1024 the server worked fine again...

Could this strange behaviour be caused by a too big(?) variable size, or is something else causing these problems?

Thomas

[Thomas.]

Quote:

Originally Posted by Thomas. Thank you for the replies, it seems to work. However, I'm experiencing a new problem. The variable in which the query is stored has to be very big, because it's a very long query. But when I set the size of data storage of the variable higher than 1024 (not sure if it's exactly 1024), the whole server seems to behave unexpected; for instance, I compiled the script to see if it works, I logon to the server... and none of the commands were working... When I changed the size of the var back to 1024 the server worked fine again... Could this strange behaviour be caused by a too big(?) variable size, or is something else causing these problems? Thomas

Just a Bump

[Thomas.]

The full code is going to be a bit of a problem (80000+ lines).

Here is the part where the query is set:

pawn Код:

...
                new query[10000];
                format(query, sizeof(query), "UPDATE `users` SET \
                `Key` = '%s', \
                `Level` = %d, \
                `Spawn` = %d, \
                `TesterLevel` = %d, \
                `HelperLevel` = %d, \
                `AdminLevel` = %d, \
                `RegularRank` = %d, \
                `UpgradePoints` = %d, \
                `ConnectedTime` = %d, \
                `Registered` = %d, \
                `Sex` = %d, \
                `Age` = %d, \
                `GPS` = %d, \
                `Origin` = %d, \
                `CK` = %d, \
                `Muted` = %d, \
                `Frozen` = %d, \
                `Blind` = %d, \
                `Respect` = %d, \
                `Money` = %d, \
                `Bank` = %d, \
                `Crimes` = %d, \
                `CrimeReason` = '%s', \
                `Wanted` = %d, \
                `WantedLevel` = %d,",
                newpass,
                PlayerInfo[playerid][pLevel],
                PlayerInfo[playerid][pSpawn],
                PlayerInfo[playerid][pTester],
                PlayerInfo[playerid][pHelper],
                PlayerInfo[playerid][pAdmin],
                PlayerInfo[playerid][pRegularRank],
                PlayerInfo[playerid][gPupgrade],
                PlayerInfo[playerid][pConnectSeconds],
                PlayerInfo[playerid][pReg],
                PlayerInfo[playerid][pSex],
                PlayerInfo[playerid][pAge],
                PlayerInfo[playerid][pGPS],
                PlayerInfo[playerid][pOrigin],
                PlayerInfo[playerid][pCK],
                PlayerInfo[playerid][pMuted],
                PlayerInfo[playerid][pFrozen],
                PlayerInfo[playerid][pBlind],
                PlayerInfo[playerid][pExp],
                PlayerInfo[playerid][pCash],
                PlayerInfo[playerid][pAccount],
                PlayerInfo[playerid][pCrimes],
                crimereason,
                PlayerInfo[playerid][pWanted],
                PlayerInfo[playerid][pWantedLevel]);

                format(query, sizeof(query), "%s \
                `Kills` = %d, \
                `Deaths` = %d, \
                `WhyLeft` = '%s', \
                `Arrested` = %d, \
                `WantedDeaths` = %d, \
                `Phonebook` = %d, \
                `LottoNr` = %d, \
                `Fishes` = %d, \
                `BiggestFish` = %d, \
                `Job` = %d, \
                `Paycheck` = %d, \
                `HeadValue` = %d, \
                `Jailed` = %d, \
                `JailTime` = %d, \
                `CanRobTime` = %d, \
                `Materials` = %d, \
                `Drugs` = %d, \
                `Leader` = %d, \
                `Member` = %d, \
                `FMember` = %d, \
                `Rank` = %d, \
                `Char` = %d, \
                `ContractTime` = %d, \
                `DetSkill` = %d, \
                `SexSkill` = %d, \
                `BoxSkill` = %d,",
                query,
                PlayerInfo[playerid][pKills],
                PlayerInfo[playerid][pDeaths],
                whyleft,
                PlayerInfo[playerid][pArrested],
                PlayerInfo[playerid][pWantedDeaths],
                PlayerInfo[playerid][pPhoneBook],
                PlayerInfo[playerid][pLottoNr],
                PlayerInfo[playerid][pFishes],
                PlayerInfo[playerid][pBiggestFish],
                PlayerInfo[playerid][pJob],
                PlayerInfo[playerid][pPayCheck],
                PlayerInfo[playerid][pHeadValue],
                PlayerInfo[playerid][pJailed],
                PlayerInfo[playerid][pJailTime],
                PlayerInfo[playerid][pRobTime],
                PlayerInfo[playerid][pMats],
                PlayerInfo[playerid][pDrugs],
                PlayerInfo[playerid][pLeader],
                PlayerInfo[playerid][pMember],
                PlayerInfo[playerid][pFMember],
                PlayerInfo[playerid][pRank],
                PlayerInfo[playerid][pChar],
                PlayerInfo[playerid][pContractTime],
                PlayerInfo[playerid][pDetSkill],
                PlayerInfo[playerid][pSexSkill],
                PlayerInfo[playerid][pBoxSkill]);

                format(query, sizeof(query), "%s \
                `LawSkill` = %d, \
                `MechSkill` = %d, \
...

And so on

I've even tried to split the whole query into 10 smaller query's (and thus having having to execute 10 different mysql_query's, which is everything but ideal, and even that resulted in unexpected behaviour. (A command not found error).

I think the reason why I get this command not found error is because the script produces an error when it tries to set and execute the query, which results in a disturbance in the whole command system. (Not sure about this though).

How could I solve this problems?

Thomas

[Thomas.]

Ok, I've tried many different things so far... nothing seems to work.

It seems that there is a limit on the cell size of the variable or something...

Does anybody know if there is such a limit?

[Vince]

There's is absolutely nothing wrong with multiple queries. For example: one simple click on this very forum takes an average of 13 queries. Then multiply that with the amount of users currently viewing this board ..

Maybe use 5 queries or so with 10 values in each query.

[Thomas.]

Yes, but still... those 13 queries all have their own purpose, they all do something different...

Executing 10 queries, to actually perform 1 query isn't very improving for the performance either.

But if there is no other option... I guess I should go with that...
Although, I did get the same error with the split queries, and I have no idea why.
The server does not log any error, nor is there anything useful in the mysql log.

Do you know about variable limits, (if there is such a thing)?

[Thomas.]

Quote:

Originally Posted by ****** This is incredibly important information you entirely failed to mention before. What is this error you speak of?

Quote:

Originally Posted by Thomas. The server does not log any error, nor is there anything useful in the mysql log.

I have absolutely no idea.

I've tried to execute the query manually (in phpmyadmin), to see if there is an error in the query itself, but the query get's executed without any error.