| View previous topic :: View next topic |
| Author |
Message |
Fiz
Joined: 13 Nov 2010
Posts: 5
|
 Posted: Sat Nov 13, 2010 19:39 Post subject: LIKE operator and '%' |
 |
|
Hi, I'm quite new to NWNX2 and I just begun to build my own SQLite DB.
Everything works fine, except for one thing: I can't make 'LIKE' statements because for some reason if I write '%' in the SQL string to be executed, I see in odbc log that it is converted into '~'.
Here's an example:
SCRIPT COMMAND:
sSql = "SELECT quantita FROM ed_disp WHERE tag = '" + sTag + "' AND resref LIKE '" + sRes + "%'";
LOG MESSAGE:
o Got request: SELECT quantita FROM ed_disp WHERE tag = 'DagAlc' AND resref LIKE 'ristsup~'
Could you explain what I'm doing wrong?  |
|
| Back to top |
|
 |
Fiz
Joined: 13 Nov 2010
Posts: 5
|
| Posted: Tue Nov 16, 2010 20:50 Post subject: |
|
|
| Anyone knows how to help me, please? |
|
| Back to top |
|
|
Squatting Monk
Joined: 28 Jun 2007
Posts: 76
|
| Posted: Sat Nov 20, 2010 6:25 Post subject: |
|
|
| I can't see what you're doing wrong. I've used the wildcard with LIKE before with no problems, and it looks like your quotation marks work out fine. What sort of datatype are you getting? I know % is used by NWNX when retrieving object data from a text or BLOB field, so that could cause conflicts. Is that what you're doing? |
|
| Back to top |
|
|
Fiz
Joined: 13 Nov 2010
Posts: 5
|
| Posted: Sat Nov 20, 2010 14:28 Post subject: |
|
|
Thank you for the answer, here is the code where I use the LIKE statement:
| Code: |
int iQty;
int iResult;
int iPrice
string sTag = GetTag(oMerc);
string sRes = GetResRef(oProd);
string sSql;
sSql = "SELECT price FROM ed_main WHERE resref = '" + sRes + "'";
SQLExecDirect(sSql);
iResult = SQLFetch();
if (iResult == SQL_SUCCESS)
{
iPrice = StringToInt(SQLGetData(1));
sSql = "SELECT quantity FROM ed_disp WHERE tag = '" + sTag + "' AND resref LIKE '" + sRes + "%'";
SQLExecDirect(sSql);
iResult = SQLFetch();
if (iResult == SQL_SUCCESS)
{
iQty = StringToInt(SQLGetData(1));
}
else
{
iQty = 0;
}
}
|
|
|
| Back to top |
|
|
Fireboar
Joined: 17 Feb 2008
Posts: 323
|
| Posted: Sun Nov 21, 2010 16:59 Post subject: |
|
|
| What does your SQLExecDirect look like? |
|
| Back to top |
|
|
Fiz
Joined: 13 Nov 2010
Posts: 5
|
| Posted: Mon Nov 22, 2010 1:53 Post subject: |
|
|
Is the 'standard' one, directly from aps_include:
| Code: |
void SQLExecDirect(string sSQL)
{
SetLocalString(GetModule(), "NWNX!ODBC!EXEC", sSQL);
}
|
It doesn't seem to convert the string in any way at this point... But I don't know how it works after setting the local string.
Maybe you could give me some example that works, so I could try it in my system? I really don't know what to do, except maybe thinking about some strange arrangement using something like FindSubString outside the SQL code...  |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Thu Nov 25, 2010 9:40 Post subject: |
|
|
It's a result of a Token replacement, that is part of the SCO/RCO (object) functionality, and 'safe' string encoding.
'%s' is used as C string placeholder. Any % that is not part of the SCO/RCO code will be replaced with a ~ character.
Basically, the LIKE syntax has been kiboshed.
Cheers
Gryphyn |
|
| Back to top |
|
|
Fiz
Joined: 13 Nov 2010
Posts: 5
|
| Posted: Fri Nov 26, 2010 2:20 Post subject: |
|
|
Ok, thank you for the answer. I'll try something else  |
|
| Back to top |
|
|
eeriegeek
Joined: 07 Jan 2008
Posts: 59
|
| Posted: Thu Dec 16, 2010 3:15 Post subject: |
|
|
| I just tried several simple varieties of like with % using both the windows stable (0.9.2.4) release and a Nov 1 svn trunc build for linux (0.3.2) and both seemed to work fine using SQLExecDirect and both return the expected matching rows. I wonder what the difference is? |
|
| Back to top |
|
|
JustVoodoo
Joined: 17 Jan 2012
Posts: 4
|
| Posted: Tue Jan 17, 2012 15:07 Post subject: |
|
|
| Gryphyn wrote: | It's a result of a Token replacement, that is part of the SCO/RCO (object) functionality, and 'safe' string encoding.
'%s' is used as C string placeholder. Any % that is not part of the SCO/RCO code will be replaced with a ~ character.
|
Is there a way to avoid or "fix" it?
My problem is, that i want to compare two datetimes. Its not enough to know if time1 is greater then time2, I need the exact value of seconds(or minutes at least) between them. The only way I know is to use "strftime('%s','xxxx') - strftime('%s','now')". But if i cant use %s, i dont know what a can do.
Any function that will convert a datetime to int would be fine as well.
Thx for help |
|
| Back to top |
|
|
eeriegeek
Joined: 07 Jan 2008
Posts: 59
|
| Posted: Tue Jan 17, 2012 16:50 Post subject: |
|
|
| JustVoodoo, time functions are very database dependent, what DB are you using? Under MySQL I can use something like timestampdiff(SECOND,last_login,now()) to get seconds since last login. |
|
| Back to top |
|
|
JustVoodoo
Joined: 17 Jan 2012
Posts: 4
|
| Posted: Tue Jan 17, 2012 17:09 Post subject: |
|
|
Ok,
i've made my own conversation from date to int.
| Code: |
int DBTimestamp(string sTime="now"){
if(sTime == "now")
sTime = Datetime();
return (GetDateTimeByPosition(1, sTime)-2012)* 33177600 //-2012 to keep number small(er)
+(GetDateTimeByPosition(1, sTime)- 1)* 2.678.400
+(GetDateTimeByPosition(3, sTime)- 1)* 86.400
+ GetDateTimeByPosition(4, sTime) * 3.600
+ GetDateTimeByPosition(5, sTime) * 60
+ GetDateTimeByPosition(6, sTime) * 1;
}
|
Basically its simple. I convert everything is seconds, but I don't know if there will be some problems with months, since the seconds a month has depends on the month. I took for a every month 31 Days (30 Days + 23h + 59 min + 59 sec). and a year = 12*32 Days. So I secured that the 31.xx. 23:59:59 can't have a greater value then the 01.xx+1. 00:00:00 and the same with the year.
This idea is based on another method to convert a date to int:
2012-06-06 20:00:00 to
20120606200000, basically deleting all spaces and symbols other then digits. unfortunately that number has 14 digits and i doesn't fit into an integer.
The method to convert the date is
year * 10.000.000.000
+ month* 100.000.000
+ day * 1.000.000
...
But it is not necessary that the numbers are so great. The greatest Value for the maximum number of days, hours,minutes and seconds (31d + 23h + 60m + 60m) has to be smaller then one month. So increasing the month will result in a greater value then all other dates before that date.
The only issue may be that i can't convert the int back to a date, but I don't need that (by now ) |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
