| View previous topic :: View next topic |
| Author |
Message |
GrawinOfTethir
Joined: 16 Oct 2005
Posts: 4
|
 Posted: Sun Oct 16, 2005 16:28 Post subject: Some SQL requests not working with SQLite |
 |
|
Hi,
I'm using NWNX 2.61 with ODBC 2 v0.9.24 and I'm having problems with some SQL requests not working.
| Code: |
strSQL = "SELECT * FROM "+strTableWeather+" WHERE (ParentName = '-');";
SQLExecDirect(strSQL);
while (SQLFetch() == SQL_SUCCESS)
{
intCounter++;
strAreaTag = SQLGetData(1);
SetLocalString(objMe,"SetWeatherAreaTag"+IntToString(intCounter),strAreaTag);
}
for (i=1;i<=intCounter;i++)
{
strAreaTag = GetLocalString(objMe,"SetWeatherAreaTag"+IntToString(i));
SetAreaWeather(GetObjectByTag(strAreaTag));
DeleteLocalString(objMe,"SetWeatherAreaTag"+IntToString(i));
}
strSQL = "SELECT AreaName, ParentName FROM "+strTableWeather+" WHERE NOT (ParentName = '-') AND NOT (ParentName = '*');";
SQLExecDirect(strSQL);
while (SQLFetch() == SQL_SUCCESS)
{
[...]
}
|
And in the function SetAreaWeather I have the following code:
| Code: |
strSQL = "SELECT * FROM modLoN_WeatherTable WHERE (AreaName = 'ARA_CalGraveyard');";
SQLExecDirect(strSQL);
|
Now the problem is, the SQL request in SetAreaWeather does not work, but the following SQL request in the function calling SetAreaWeather works fine.
The log output is:
| Code: |
o Got request: SELECT * FROM modLoN_WeatherTable WHERE (ParentName = '-');
o Sent response (39 bytes): ARA_CalGraveyard¬WEATHER_CLEAR¬85¬1¬1¬-
o Sent response (41 bytes): ARA_BrankenVillage¬WEATHER_CLEAR¬85¬1¬1¬-
o Sent response (0 bytes):
o Got request: SELECT * FROM modLoN_WeatherTable WHERE (AreaName = 'ARA_CalGraveyard');
o Sent response (39 bytes): ARA_CalGraveyard¬WEATHER_CLEAR¬85¬1¬1¬-
o Got request: SELECT * FROM modLoN_WeatherTable WHERE (AreaName = 'ARA_CalGraveyard');
o Sent response (39 bytes): ARA_CalGraveyard¬WEATHER_CLEAR¬85¬1¬1¬-
o Got request: SELECT AreaName, ParentName FROM modLoN_WeatherTable WHERE NOT (ParentName = '-') AND NOT (ParentName = '*');
o Sent response (37 bytes): ARA_CalBoundaries001¬ARA_CalGraveyard
o Sent response (37 bytes): ARA_CalBoundaries002¬ARA_CalGraveyard
o Sent response (32 bytes): ARA_KNIVRoad001¬ARA_CalGraveyard
o Sent response (32 bytes): ARA_KNIVRoad002¬ARA_CalGraveyard
o Sent response (32 bytes): ARA_KNIVRoad003¬ARA_CalGraveyard
o Sent response (29 bytes): ARA_Hills001¬ARA_CalGraveyard
o Sent response (34 bytes): ARA_KNIVRoad004¬ARA_BrankenVillage
o Sent response (34 bytes): ARA_KNIVRoad005¬ARA_BrankenVillage
o Sent response (0 bytes):
|
I would expect the SQL request to end with "Sent response (0 bytes):", but as you can see, this is not the case.
The table holds two entries for the area "ARA_CalGraveyard" which should both be returned.
I have even tried to delay the request in the function SetAreaWeather, but to no effect.
Placing the exact same query
| Code: |
strSQL = "SELECT * FROM modLoN_WeatherTable WHERE (AreaName = 'ARA_CalGraveyard');";
SQLExecDirect(strSQL);
|
after the second while-loop produces the same result.
Anyone has an idea there? |
|
| Back to top |
|
 |
Primogenitor
Joined: 08 Jan 2005
Posts: 88
|
| Posted: Mon Oct 17, 2005 20:24 Post subject: |
|
|
| Have you tried looking up the syntax at www.sqlite.org ? |
|
| Back to top |
|
|
GrawinOfTethir
Joined: 16 Oct 2005
Posts: 4
|
| Posted: Mon Oct 17, 2005 23:34 Post subject: |
|
|
Thanks for the reply.
The SELECT statement is following the exact syntax as described on www.sqlite.org.
Besides it works with all other queries, but not this one.
One thing to note though is, that sometimes the query returns not only one, but two rows from the table, but the log does not show the trailing
| Code: | | o Sent response (0 bytes): |
and the next time this query is executed, it returns just one row again.
The strangest thing is, that I have tested it via ODBC with an Access database with the same data and the behavior is the same.
Every other query works just fine, except for this one.
I have even dropped the table and re-created it.
No success there 
I'm out of ideas. |
|
| Back to top |
|
|
monezz
Joined: 03 Jan 2005
Posts: 52
Location: The Netherlands
|
| Posted: Tue Oct 18, 2005 11:37 Post subject: |
|
|
Have you tried to execute the sql query on the sqlite command prompt?
If the prompt also comes with 1 records, the problem has something to do with youre data or query.
If the prompt gives 2 records, there is something wrong with fetching the records from the database. Perhaps some character appears in the result indicating the end of the resultset? (long shot..)
ie. Try to shrink the problem area step by step.
Success.
Monezz |
|
| Back to top |
|
|
GrawinOfTethir
Joined: 16 Oct 2005
Posts: 4
|
| Posted: Tue Oct 18, 2005 18:21 Post subject: |
|
|
@monezz:
I have already tried your ideas.
Executing the query on the sqlite command prompt or with an external program like SQLite Spy produces the desired result.
And as you can see from my first post, the other queries run on the same database return all of the records, including the ones that should be returned with the SELECT query in question.
So why should one SELECT query return the records when anothe on the same table doesn't?
This doesn't make sense, does it? |
|
| Back to top |
|
|
GrawinOfTethir
Joined: 16 Oct 2005
Posts: 4
|
| Posted: Tue Oct 18, 2005 19:19 Post subject: |
|
|
Well, I guess I found the solution. Of course, it was my fault 
I had a break somewhere in the while loop cycling through the resultset and it left the loop before reaching the end of the resultset, so I guess everything works as it should.
Apparently the log does not show the whole tableset as returned by the query, but only the records returned when SQLFetch() is called.
Sorry about the confusion. |
|
| Back to top |
|
|
Papillon
x-man
Joined: 28 Dec 2004
Posts: 1060
Location: Germany
|
| Posted: Tue Oct 18, 2005 23:04 Post subject: |
|
|
@GrawinOfTethir: You are right on the assumption about SQLFetch and the log entries.
And there you had me sweat^d^d^d^d^d wondering what was going on 
_________________
Papillon |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
