#138061 - 2005-04-15 06:58 PM
Getting the most recent entry from using DBGetRecordSet()
|
thepip3r
Hey THIS is FUN
Registered: 2005-03-02
Posts: 350
|
In the example to DBGetRecordSet(), Sealeopard uses two FOR loops to evaluate the results. I'd like to just get the LATEST result and tried to use ubound() to do it (after reading the function it sounded like it would've worked). But it KiX kicked it back and I was wondering if you could tell me if the way I did it was wrong or if there's another way to get the single, latest record from the DB?
Example with Sealeopard's "example":
Code:
;START MODIFICATIONS TO NTDOC'S SCRIPT TO WRITE THE GATHERED INFORMATION TO A MYSQL DATABASE
;DIM OBJECTS BY CATEGORY
DIM $objConn
DIM $compSQL,$processSQL
DIM $cid,$ProcessName,$ProcessID,$ProcessPath
DIM $queryForCID,$recordset,$retcode,$row,$column
;VARIABLES FOR DATABASE CONNECTIVITY
$ConnDSN = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=test;PASSWORD='';OPTION=3;"
$objConn = DBConnOpen($ConnDSN)
? "Error @ERROR: @SERROR"
sleep 1
;STRIP BACKSLASHES OUT OF THE FIELDS AND WRITE THE COMPUTER SPECIFIC INFORMATION TO THE DATABASE
$LogonServer = Replace('$LogonServer','\','|')
$SystemUpTime = Replace('$SystemUpTime','\','|')
$compSQL = "INSERT INTO computers(Name,LastLogonTime,LastLogonDate,LogonServer,Domain,CurrentIP,MACAddress,SerialNumber,OSType,OSServicePack,OSInstallDate,OSBuild,OSRole,ProductKey,RegisteredOwner,RegisteredOrganization,ProcessorType,ProcessorSpeed,MemorySize,SystemType,ChassisType,BIOSName,SMBIOSVersion,BIOSVersion,BIOSManufacturer,Uptime,ScriptRunTime)
VALUES('$HostName','$Time','$Date','$LogonServer','$DomainMemberOf','$CurrentIP','$MAC','"+$MyBIOSInfo[3]+"','$ProdType','"+$CurrentOS[3]+"','$InstallDate','"+$CurrentOS[4]+"','"+$CurrentOS[2]+"','"+$CurrentOS[8]+"','"+$CurrentOS[6]+"','"+$CurrentOS[7]+"','$CPU','$Mhz','$Ram','"+$ST[0]+"','"+$ST[1]+"','"+$MyBIOSInfo[0]+"','"+$MyBIOSInfo[2]+"','"+$MyBIOSInfo[1]+"','"+$MyBIOSInfo[4]+"','$SystemUpTime','$ScriptRunTime')"
$ = DBExecuteSQL($objConn,$compSQL)
? "Error @ERROR: @SERROR"
;QUERY THE DATABASE FOR THE CID OF THE COMPUTER THAT WAS JUST ENTERED INTO THE DATABASE
$queryForCID = "SELECT cid FROM computers WHERE Name='$HostName' AND MACAddress='$MAC'"
$recordset = DBGetRecordset($objConn,$queryForCID)
$retcode = DBConnClose($objConn)
for $row=0 to ubound($recordset,1)
for $column=0 to ubound($recordset,2)
? 'Field ='+$recordset[$row,$column]
next
next
My attempt modify Sealeopard's example to just get the most recent record:
Code:
;START MODIFICATIONS TO NTDOC'S SCRIPT TO WRITE THE GATHERED INFORMATION TO A MYSQL DATABASE
;DIM OBJECTS BY CATEGORY
DIM $objConn
DIM $compSQL,$processSQL
DIM $cid,$ProcessName,$ProcessID,$ProcessPath
DIM $queryForCID,$recordset,$retcode,$latestRecord
;VARIABLES FOR DATABASE CONNECTIVITY
$ConnDSN = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=test;PASSWORD='';OPTION=3;"
$objConn = DBConnOpen($ConnDSN)
? "Error @ERROR: @SERROR"
sleep 1
;STRIP BACKSLASHES OUT OF THE FIELDS AND WRITE THE COMPUTER SPECIFIC INFORMATION TO THE DATABASE
$LogonServer = Replace('$LogonServer','\','|')
$SystemUpTime = Replace('$SystemUpTime','\','|')
$compSQL = "INSERT INTO computers(Name,LastLogonTime,LastLogonDate,LogonServer,Domain,CurrentIP,MACAddress,SerialNumber,OSType,OSServicePack,OSInstallDate,OSBuild,OSRole,ProductKey,RegisteredOwner,RegisteredOrganization,ProcessorType,ProcessorSpeed,MemorySize,SystemType,ChassisType,BIOSName,SMBIOSVersion,BIOSVersion,BIOSManufacturer,Uptime,ScriptRunTime)
VALUES('$HostName','$Time','$Date','$LogonServer','$DomainMemberOf','$CurrentIP','$MAC','"+$MyBIOSInfo[3]+"','$ProdType','"+$CurrentOS[3]+"','$InstallDate','"+$CurrentOS[4]+"','"+$CurrentOS[2]+"','"+$CurrentOS[8]+"','"+$CurrentOS[6]+"','"+$CurrentOS[7]+"','$CPU','$Mhz','$Ram','"+$ST[0]+"','"+$ST[1]+"','"+$MyBIOSInfo[0]+"','"+$MyBIOSInfo[2]+"','"+$MyBIOSInfo[1]+"','"+$MyBIOSInfo[4]+"','$SystemUpTime','$ScriptRunTime')"
$ = DBExecuteSQL($objConn,$compSQL)
? "Error @ERROR: @SERROR"
;QUERY THE DATABASE FOR THE CID OF THE COMPUTER THAT WAS JUST ENTERED INTO THE DATABASE
$queryForCID = "SELECT cid FROM computers WHERE Name='$HostName' AND MACAddress='$MAC'"
$recordset = DBGetRecordset($objConn,$queryForCID)
$retcode = DBConnClose($objConn)
$latestRecord = ubound($recordset)
? $latestRecord
While this works, it returns the total number of rows, not the information from my query about the latest row. Can anyone put me in the right direction?
|
|
Top
|
|
|
|
|
#138062 - 2005-04-15 11:46 PM
Re: Getting the most recent entry from using DBGetRecordSet()
|
Kdyer
KiX Supporter
Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
|
Can you pleaee break up your long lines?
Question.. What about TOP 1 or TOP 10 or something like that?
For example:
Code:
SELECT TOP 1 cid FROM computers WHERE Name='$HostName' AND MACAddress='$MAC'
HTH,
Kent
|
|
Top
|
|
|
|
#138064 - 2005-04-18 05:10 PM
Re: Getting the most recent entry from using DBGetRecordSet()
|
thepip3r
Hey THIS is FUN
Registered: 2005-03-02
Posts: 350
|
kyder -
Sorry for the long code. It was just the SQL fields and values so I didn't think it was that important.
kyder/sealeopard -
I tried what you all suggested:
Code:
$queryForCID = "SELECT TOP 1 cid FROM computers WHERE Name='$HostName' AND MACAddress='$MAC'"
...but when I show the results (with ?), it returns "-1". When I take out the "TOP 1" portion of the SQL query, it works fine and I get 12-13 results... Do you know why that's happening? Isn't that SQL's error return?
|
|
Top
|
|
|
|
|
#138065 - 2005-04-18 05:19 PM
Re: Getting the most recent entry from using DBGetRecordSet()
|
Kdyer
KiX Supporter
Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
|
One idea you can do.. I frequently look at a query in the View (create a view and don't save it when completed) portion of SQL Enterprise Manager to "test" queries to insure they work and I have the syntax correct. When I get everything right, then I can paste the results into the program/app I need it in.
One other thing with your Name or MacAddress, either and/or both are going to be unique. Why do you need both in your query? That is, you can only have one computer with a name of xyz on the domain, correct? Also, the MAC Address will always be unique as well. So, why do you need both? I would most likely choose the one that is probably the easiest to work with like:
Code:
$queryForCID="SELECT TOP 1 cid FROM computers WHERE Name='"+@wksta+"'"
Note: I put the @Wksta macro outside the quotes for the SQL Statment.
To take this a step further, I created a view on my SQL Box and here is what I get:
Code:
SELECT TOP 1 id FROM dbo.Computers WHERE (pcname='NAME')
And it works great.
HTH,
Kent
|
|
Top
|
|
|
|
|
#138066 - 2005-04-18 06:07 PM
Re: Getting the most recent entry from using DBGetRecordSet()
|
thepip3r
Hey THIS is FUN
Registered: 2005-03-02
Posts: 350
|
kyder -
i used MySQL Query Browser to run this against my DB:
Code:
SELECT cid FROM computers
and then...
Code:
SELECT TOP 1 cid FROM computers
and got results on the first 1 but a SQL error on the second one. Do you know that "TOP 1" is a valid SQL syntax?
Also, the reason when I query, I'm querying for the MAC and the name is because computers can have different MACs (users move their network cards), OR users simply rename their computer and rejoin them to the domain. When I query for the information, I'm just making sure I get the right computer... that's all.
|
|
Top
|
|
|
|
|
#138067 - 2005-04-18 06:12 PM
Re: Getting the most recent entry from using DBGetRecordSet()
|
Kdyer
KiX Supporter
Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
|
thepip3r,
Yes, TOP is very much valid..
From Books on-line (or BOL) -
Quote:
Limiting Result Sets Using TOP and PERCENT
The TOP clause limits the number of rows returned in the result set.
TOP n [PERCENT]
n specifies how many rows are returned. If PERCENT is not specified, n is the number of rows to return. If PERCENT is specified, n is the percentage of the result set rows to return:
TOP 120 /*Return the top 120 rows of the result set. */
TOP 15 PERCENT /* Return the top 15% of the result set. */.
If a SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the ordered result set. The entire result set is built in the specified order and the top n rows in the ordered result set are returned.
The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP in these ways:
The SET ROWCOUNT limit applies to building the rows in the result set after an ORDER BY is evaluated. When ORDER BY is specified, the SELECT statement is terminated when n rows have been selected from a set of values that has been sorted according to specified ORDER BY classification.
The TOP clause applies to the single SELECT statement in which it is specified. SET ROWCOUNT remains in effect until another SET ROWCOUNT statement is executed, such as SET ROWCOUNT 0 to turn the option off
HTH,
Kent
|
|
Top
|
|
|
|
|
#138069 - 2005-04-18 07:41 PM
Re: Getting the most recent entry from using DBGetRecordSet()
|
Kdyer
KiX Supporter
Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
|
I guess I should read more closely.. I think with your MySQL, you will want to use the LIMIT instead of TOP.
Please see: http://dev.mysql.com/doc/mysql/en/select.html
HTH,
Kent
|
|
Top
|
|
|
|
|
#138070 - 2005-04-18 07:53 PM
Re: Getting the most recent entry from using DBGetRecordSet()
|
thepip3r
Hey THIS is FUN
Registered: 2005-03-02
Posts: 350
|
Thanx for the help Kyder, you got me looking in the right direction. Here is the code that finally worked:
Code:
SELECT cid FROM `computers` ORDER BY cid DESC LIMIT 1
Again, I appreciate the help! =)
Edited by thepip3r (2005-04-18 07:56 PM)
|
|
Top
|
|
|
|
Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart
|
0 registered
and 837 anonymous users online.
|
|
|
