#201803 - 2011-03-28 11:19 PM
DBGetRecordSet inconsistent with same query elsewhere...
|
Georges_K
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: 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:
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
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:
$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
|
|
|
|
#201806 - 2011-03-29 09:24 AM
Re: DBGetRecordSet inconsistent with same query elsewhere...
[Re: Georges_K]
|
Richard H.
Administrator
Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
|
I suspect that the code you are thinking of is more likely to be for
If $STRING="abc" OR $STRING="def" OR STRING="ghij"... Which could be replaced with
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
|
|
|
|
#201808 - 2011-03-29 09:56 AM
Re: DBGetRecordSet inconsistent with same query elsewhere...
[Re: Mart]
|
Richard H.
Administrator
Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
|
It can be done, but I'm not sure that this is "simplifying" the 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
|
|
|
|
#201819 - 2011-03-30 09:33 AM
Re: DBGetRecordSet inconsistent with same query elsewhere...
[Re: Georges_K]
|
Richard H.
Administrator
Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
|
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:
"Exit InStr('"+$s+"','"+Join(Split($sStringsToSearch,","),"') AND InStr('"+$s+"','")+"')"+@CRLF
Gives you:
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
|
|
|
|
Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart
|
0 registered
and 259 anonymous users online.
|
|
|