Page 1 of 1 1
Topic Options
#201803 - 2011-03-28 11:19 PM DBGetRecordSet inconsistent with same query elsewhere...
Georges_K Offline
Getting the hang of it

Registered: 2005-02-17
Posts: 83
Loc: Chino, CA
Hello guys,
I'm using the DBGetRecordSet UDF to get some Mysql query results. I have the following code:
 Code:
 Dim $cnstring, $recordset, $retcode, $Workstation, $MAC, $DOMAIN, $row, $column, $COUNT, $MachineString, $LabMachine, $Query
Dim $ArrayItem
$cnstring = DBConnOpen('Driver={MySQL ODBC 3.51 Driver};Server=kbox;Database=ORG1;User=R1;Password=******;Option=3;')
$Query = "Select NAME, MAC, DOMAIN FROM MACHINE WHERE NAME LIKE '" + $Prefix + "%' ORDER BY NAME LIMIT 1000;)" 

$recordset = DBGetRecordset($cnstring,$Query)
$retcode = DBConnClose($cnstring)
Dim $LabMachines[999]
?? UBound($recordset) ??
For $row = 0 to UBound($recordset,1)
		$Workstation = $recordset[$row,0]
		$MAC = $recordset[$row,1]
		$Domain = $recordset[$row,2]
		$MachineString = $Workstation + "," + $MAC + "," + $Domain
	$LabMachines[$row] = $MachineString
Next
ReDim Preserve $LabMachines[$row - 1]
$CV_GetReservationInfo = $LabMachines
EndFunction


This code works a treat. However, whenever I add one additional criteria to the query, the function fails and returns -1.
An example of a similar query which is not working is:
 Code:
Select NAME, MAC, DOMAIN FROM MACHINE WHERE NAME LIKE '" + $Prefix + "%' AND NAME NOT LIKE '%Teach%' ORDER BY NAME LIMIT 1000;)" 
 


Using that same exact query in MySql workbench, i get the desired results. However, running it through the DBGetRecordSet function, doesn't seem to work correctly.
I was hoping someone could shed some light as to what may be going on. I could technically manipulate the string contents from the "unfiltered" query results, but that's a very cloodgy way of doing it, and I'd rather have the query itself return the desired results.

Any help would be greatly appreciated !!

quick side question:
A little while I go, I stumbled on a post which had a really neat trick to shorten the
 Code:
If InStr($string,abc) AND InStr($string,def) AND InStr($string,ghi) , etc.

into something with the split() function with delimiters, kind of like this:
 Code:
$StringsToSearch="abc~def~ghi"
If InStr($string,split($StringsToSearch,"~") = 1 


(I know that this is wrong code, but I'm trying to find that thread where that was explained... can someone link me that if possible?

Thanks!
_________________________
Network Specialist
Chino Unified School District

Top
#201804 - 2011-03-29 02:22 AM Re: DBGetRecordSet inconsistent with same query elsewhere... [Re: Georges_K]
Allen Administrator Online   shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4482
Loc: USA
Try doubling up on your %'s.

 Quote:

The characters @, %, or $ are normally used to indicate macros, environment strings, or variables. If you want to use these characters in a string, use @@, %%, or $$.

Top
#201805 - 2011-03-29 04:53 AM Re: DBGetRecordSet inconsistent with same query elsewhere... [Re: Allen]
Georges_K Offline
Getting the hang of it

Registered: 2005-02-17
Posts: 83
Loc: Chino, CA
I cannot believe I didn't even think of that. That was so much simpler than I had thought. Thanks so much Allen!

By the way, would you happen to know the location of the thread regarding the "side question?"
_________________________
Network Specialist
Chino Unified School District

Top
#201806 - 2011-03-29 09:24 AM Re: DBGetRecordSet inconsistent with same query elsewhere... [Re: Georges_K]
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4945
Loc: Leatherhead, Surrey, UK
I suspect that the code you are thinking of is more likely to be for
 Code:
If $STRING="abc" OR $STRING="def" OR STRING="ghij"...

Which could be replaced with
 Code:
In InStr("~abc~def~ghij~","~"+$STRING+"~")...


I can't think of a short-cut for multiple AND-ed InStr() in the form that you've asked.

Top
#201807 - 2011-03-29 09:31 AM Re: DBGetRecordSet inconsistent with same query elsewhere... [Re: Georges_K]
Mart Moderator Offline
KiX Supporter
*****

Registered: 2002-03-27
Posts: 4648
Loc: The Netherlands
 Originally Posted By: Georges_K
....
By the way, would you happen to know the location of the thread regarding the "side question?"


I do not know the thread but I think you are looking for something tlike this.

 Code:
;What to search for.
;Converted into an array by splitting it on ~.
$StringsToSearch = Split("abc~def~ghi", "~")

;Loop through the array item by item.
For Each $item in $StringsToSearch
	;Check if the items can be found.
	If InStr($string, $item)
		;Found it.
		? "Found it!"
	EndIf
Next
_________________________
Mart

- Chuck Norris once sold ebay to ebay on ebay.

Top
#201808 - 2011-03-29 09:56 AM Re: DBGetRecordSet inconsistent with same query elsewhere... [Re: Mart]
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4945
Loc: Leatherhead, Surrey, UK
It can be done, but I'm not sure that this is "simplifying" the code!
 Code:
$sStringsToSearch="ab,bc,bcd,d"
$s="abcd"

"Checking if string '"+$s+"' contains all the sub strings from '"+$sStringsToSearch+"'"+@CRLF
"'"+$s+"' result: "+Execute("Exit InStr('"+$s+"','"+Join(Split($sStringsToSearch,","),"') AND InStr('"+$s+"','")+"')")+@CRLF


I'd probably just use a loop for readability.

Top
#201811 - 2011-03-29 05:10 PM Re: DBGetRecordSet inconsistent with same query elsewhere... [Re: Richard H.]
Georges_K Offline
Getting the hang of it

Registered: 2005-02-17
Posts: 83
Loc: Chino, CA
Ahh yes! I think it's that first one that you suggested Richard. the second works as well. I actually ended up with the second suggestion.
What made it so memorable for me, was the fact that it was so simple, and seemed much more practical when having a slightly longer list of "ORs"

Richard, though I love reading your code \:\) , I know for a fact that wasn't it, but I'll keep that one as a reference anyway ! \:\)
Thanks again guys!

_________________________
Network Specialist
Chino Unified School District

Top
#201813 - 2011-03-29 09:52 PM Re: DBGetRecordSet inconsistent with same query elsewhere... [Re: Georges_K]
Georges_K Offline
Getting the hang of it

Registered: 2005-02-17
Posts: 83
Loc: Chino, CA
Ok , I can't stop looking at that line you wrote Richard. I can't seem to understand it. Would you mind explaining it to me?
Specifically, this line, obviously \:\)
 Code:
"'"+$s+"' result: "+Execute("Exit InStr('"+$s+"','"+Join(Split($sStringsToSearch,","),"') AND InStr('"+$s+"','")+"')")+@CRLF 
_________________________
Network Specialist
Chino Unified School District

Top
#201819 - 2011-03-30 09:33 AM Re: DBGetRecordSet inconsistent with same query elsewhere... [Re: Georges_K]
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4945
Loc: Leatherhead, Surrey, UK
 Originally Posted By: Georges_K
Would you mind explaining it to me?


Sure.

It's actually a very simple process but reducing it to a one-liner makes the code very dense and hard to read, which is the main reason that I'd recommend going for a simple loop. Making your own code difficult to maintain is never a good idea \:\)

The code is creating a new line of KiXtart code as a string and then EXECUTE-ing it, returning a TRUE/FALSE value via the EXIT built-in.

We split $sStringsToSearch into an array, then JOIN it back together using KiXtart InStr() code as the glue instead of the default space character. Finally we tag a little bit of code to the front "Exit Instr(" and the final closing parenthesis ")" on the end and we have an executable bit of code.

It all becomes clear if you add a line to print the generated code:
 Code:
"Exit InStr('"+$s+"','"+Join(Split($sStringsToSearch,","),"') AND InStr('"+$s+"','")+"')"+@CRLF


Gives you:
 Quote:
Exit InStr('abcd','ab') AND InStr('abcd','bc') AND InStr('abcd','bcd') AND InStr('abcd','d')


In this example the generated code is about the same number of characters as the code that creates it so there is not much gain, but if you had many more strings in $sStringsToSearch or if $sStringsToSearch was coming from a variable source then the code would be worthy.

Top
#201821 - 2011-03-30 05:35 PM Re: DBGetRecordSet inconsistent with same query elsewhere... [Re: Richard H.]
Georges_K Offline
Getting the hang of it

Registered: 2005-02-17
Posts: 83
Loc: Chino, CA
Ah ... I see... That is a clever way to do it. Albeit, as you mentioned, overkill for this scenario; though I could see its usefulness in a longer string to search.
To be honest, though I understand the line now, I don't think I would've ever thought of writing it this way. Pretty creative I have to say \:\)

Thanks for the play by play Richard! Much appreciated!
_________________________
Network Specialist
Chino Unified School District

Top
Page 1 of 1 1


Moderator:  Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Arend_, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 144 anonymous users online.
Newest Members
Jodey, Hiker, GTRoberts, KatieQuinn, Brennnnn
17684 Registered Users

Generated in 0.078 seconds in which 0.055 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org