Writing to access db - KiXtart.org

You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » Basic Scripting » Writing to access db

Page 1 of 1

Topic Options

#40667 - 2003-05-24 12:48 AM

Writing to access db

brewdude6 Offline

Hey THIS is FUN

Registered: 2000-10-21
Posts: 280
Loc: Nashville, TN

I'm working on polling computers for disk space and writing to an access db. Here is a snip of what I'm using:

code:

 ;Setconsole ("HIDE")
$engine = "dao.dbengine.36"
$database = "c:\backup1\db1.mdb"
$dbe = olecreateobject ( "$engine" )
$dbs = val ( "&" + olecallfunc ( $dbe, "opendatabase", "s", "$database" ))
$Space = GetDiskSpace("\\romburner\c$")
$Server = "romburner"
$Drive = "C"
Gosub "Calculate"
Gosub "WriteToFile"
$Space = GetDiskSpace("\\romburner\d$")
$Server = "romburner"
$Drive = "D"
Gosub "Calculate"
Gosub "WriteToFile"
$Space = GetDiskSpace("\\towens2k\c$")
$Server = "towens2k"
$Drive = "C"
Gosub "Calculate"
Gosub "WriteToFile"
$Space = GetDiskSpace("\\towens2k\d$")
$Server = "towens2k"
$Drive = "D"
Gosub "Calculate"
Gosub "WriteToFile"

Goto "End"
:WriteToFile
;$X = Writeline (1, "Free Space On $Server $Drive Drive: " + "$ErrMsg" + Chr(013) + Chr(010))
$rs = olecallfunc ( $dbs, "execute", "s","INSERT INTO TABLE3 (Dato,Server,Drive,Space) VALUES ('@Date','$Server','$Drive','$ErrMsg');")
if @error goto End endif

The error I get says

"Sript error invalid function/method call: missing ")"!

$dbs = val ( "&" + olecallfunc ( $dbe, "opendatabase", "s", "$database" ))

Any help would be appreciated.

_________________________
I could have made a neat retort but didn't, for I was flurried and didn't think of it till I was downstairs.
-Mark Twain

Top

#40668 - 2003-05-24 01:53 AM Re: Writing to access db
Shawn Shawn Administrator Registered: 1999-08-13 Posts: 8611	well, although you didn't state it in your post, my guess is that your running this script with KiXtart 4.x and unfortunately (fortunately?), the OLExxx functions have become extinct ! If this is the case, will have to convert this script to 4.0 syntax (COM Syntax), if you are running this script against 3.63 please advise.
Top

#40669 - 2003-05-24 02:35 AM Re: Writing to access db
brewdude6 brewdude6 Hey THIS is FUN Registered: 2000-10-21 Posts: 280 Loc: Nashville, TN	Ahhh...I am using 4.x Thanks for the info and any help _________________________ I could have made a neat retort but didn't, for I was flurried and didn't think of it till I was downstairs. -Mark Twain
Top

#40670 - 2003-05-24 03:16 PM Re: Writing to access db
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	If you are using KiXtart 4.x then please switch over to the DB... UDFs that are posted in the UDF Forum, like DBCommand() - Executes a SQL statement and returns a recordset if applicable _________________________ There are two types of vessels, submarines and targets.
Top

#40671 - 2003-06-02 09:43 PM Re: Writing to access db
brewdude6 brewdude6 Hey THIS is FUN Registered: 2000-10-21 Posts: 280 Loc: Nashville, TN	First off, I know you guys are going to rail me about using UDF's and newer versions of Kix, but I'm stuck in the past I tell ya...please forgive. I've got the script posted above dumping to my access db like I want, BUT I'd also like to capture the IP address of the machine that I'm executing the GETDISKSPACE command on. I've got as far as piping the ping results into a .txt file, but am having trouble figuring out how I'm going to strip that and insert it into my db. Here is what I've got so far on parsing into a text file. If any of you that can offer some help would be appreciated. code: Break On Shell "cmd.exe /c ping romburner -n 1 > c:\backup1\result.txt" If Open(3,"result.txt",2) = 0 _________________________ I could have made a neat retort but didn't, for I was flurried and didn't think of it till I was downstairs. -Mark Twain
Top

#40672 - 2003-06-02 09:48 PM Re: Writing to access db
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	Take a look at the KiXtart Manual under READLINE(). Read the lines until the desired line, then use LEFT/RIGHT/SUBSTR/INSTR to parse out the IP address. _________________________ There are two types of vessels, submarines and targets.
Top

#40673 - 2003-06-02 09:52 PM Re: Writing to access db
Allen Allen KiX Supporter Registered: 2003-04-19 Posts: 4572 Loc: USA	I happened to do exactly what you are looking for as an example of an object called DynuExec... here is the link Exec() or Pipe() I'm not sure if the version of kixtart you are using supports objects, but this object was very easy to use, and it is free _________________________ (... better days ahead)
Top

#40674 - 2003-06-02 09:57 PM Re: Writing to access db
Allen Allen KiX Supporter Registered: 2003-04-19 Posts: 4572 Loc: USA	There are also some UDF links suggested by Lonkero on the above link that might help as well... _________________________ (... better days ahead)
Top

#40675 - 2003-06-02 10:09 PM Re: Writing to access db
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	But he stated that he's stuck in the past, thus I didn't quote any UDFs _________________________ There are two types of vessels, submarines and targets.
Top

#40676 - 2003-06-02 10:20 PM Re: Writing to access db
Jochen Jochen KiX Supporter Registered: 2000-03-17 Posts: 6380 Loc: Stuttgart, Germany	lol! once upon a time there was a young dude who was sentenced to track main folders diskspace on all the customers Company Servers over an unspecified period of time, not for being naughty or soemthing, just for one others satisfaction. So, the young dude decided to run a script on a daily base on all the kingdoms Servers using diruse.exe and write those results to an ini based file on that Server. Furthermore he ran, clever as he was, a scheduled script on his machine that grabbed all the results of all the servers to be written to a database on the young dudes Domain Servers.(Shawn, the Wizzard of OLE was kind enough to show the young dude how to write through KiX 3.x to a database) So the days did go by and became weeks, the weeks grew up to be months and the young dude did report to his Boss ... and the Boss said: "What? Diskspace tracking? Nice thing indeed, but who wants to know this ?" "Ummm ... I thought it was the cusomers big chief that wanted us to report this over an unspecified amount of time..." the young dude replied. "Oh yeah," the boss said, "they decided to implement a 'not to be named here expensive Systems Management System' in the mean time, so we don't need that anymore!" And the morale of this tale : Don't expect too much in writing code, but always expect the Spanish Inquisition _________________________
Top

#40677 - 2003-06-03 01:05 AM Re: Writing to access db
Shawn Shawn Administrator Registered: 1999-08-13 Posts: 8611	jpo - great story. brew - so what vers of kixtart are you going to be working worth here ? 3.63 ? -Shawn
Top

#40678 - 2003-06-03 01:12 AM

Re: Writing to access db

Shawn

Administrator

Registered: 1999-08-13
Posts: 8611

Heres something cobbled together quickly under 3.63:

code:

Break On
 
$HOST = "@WKSTA"
$TEMP = "%temp%\ping.txt"
 
SHELL '%COMSPEC% /c ping $HOST -n 1 | find /i "TTL" >"$TEMP"'
 
$IP = ""
If Open(1,$TEMP) = 0 
 $Line = ReadLine(1)
 If $Line
  $IP = SubStr($Line,12,InStr($Line,":")-12)
 EndIf
 $= Close(1)
EndIf
 
?"IP=" $IP
 
Exit 1

Hope this helps

-Shawn

Top

#40679 - 2003-06-03 02:53 AM

Re: Writing to access db

NTDOC

Administrator

Registered: 2000-07-28
Posts: 11634
Loc: CA

Sure would be nice if you could just copy/paste the code above....

Ok, tested the above code and it did work as expectd for me. [Wink]


Break On
$HOST = "@WKSTA"
$TEMP = "%temp%\ping.txt"
SHELL '%COMSPEC% /c ping $HOST -n 1 | find /i "TTL" >"$TEMP"'
$IP = ""
If Open(1,$TEMP) = 0
 $Line = ReadLine(1)
  If $Line
   $IP = SubStr($Line,12,InStr($Line,":")-12)
  EndIf
  $= Close(1)
EndIf
?"IP=" $IP
Exit 1

Top

#40680 - 2003-06-03 03:46 PM

Re: Writing to access db

brewdude6 Offline

Hey THIS is FUN

Registered: 2000-10-21
Posts: 280
Loc: Nashville, TN

Wow, thanks for the help guys. It really is appreciated. I am now capturing IP's like I wanted, but now I'm having a problem writing drivespace to my db now. I get the server named "romburner" c:\ drive and the rest are ".0" in their table. I'm sure that I'm doing something fundamentally wrong and just can't see the forrest for the trees. I've run this in debug mode with no errors. Here's the code....btw, I'm using ver. 3.62

code:

 
;Setconsole ("HIDE")
$engine = "dao.dbengine.36"
$database = "c:\inetpub1\wwwroot\db1.mdb"
$dbe = olecreateobject ( "$engine" )
$dbs = Val ( "&" + olecallfunc ( $dbe, "opendatabase", "s", "$database" ))
$Space = GetDiskSpace("\\romburner\c$")
$Server = "romburner"
$Drive = "C"
Gosub "Calculate"
Gosub "Ip"
Gosub "WriteToFile"
$Space = GetDiskSpace("\\romburner\d$")
$Server = "romburner"
$Drive = "D"
Gosub "Calculate"
Gosub "Ip"
Gosub "WriteToFile"
$Space = GetDiskSpace("\\towens2k\c$")
$Server = "towens2k"
$Drive = "C"
Gosub "Calculate"
Gosub "Ip"
Gosub "WriteToFile"
$Space = GetDiskSpace("\\towens2k\d$")
$Server = "towens2k"
$Drive = "D"
Gosub "Calculate"
Gosub "Ip"
Gosub "WriteToFile"

GoTo "End"
:WriteToFile
;$X = Writeline (1, "Free Space On $Server $Drive Drive: " + "$ErrMsg" + Chr(013) + Chr(010))
$rs = olecallfunc ( $dbs, "execute", "s","INSERT INTO TABLE3 (Dato,Server,Drive,Space,Ip) VALUES ('@Date','$Server','$Drive','$ErrMsg','$ip');")
If @error GoTo End EndIf
Return
:Calculate
Select
Case $Space < 1024 ;** Kilobytes
$Space = $Space * 100
$Units = " Kilobytes" 
Case $Space => 1024 AND $Space < 1048576 ;** Megabytes
$Space = ($Space * 100) / 1024 
$Units = " Megabytes"
Case $Space => 1048576 ;** Gigabytes
$Space = $Space / 10486
$Units = " Gigabytes"
EndSelect
;** Convert the $Space value to a string by adding a space to the beginning
$Space = " " + $Space
$Length = Len($Space)

;** Add the decimal point 2 spaces from the end.
$Space = SubStr($Space, 1, $Length - 2) + "." + SubStr($Space,$Length - 1, 2)
$ErrMsg = LTrim($Space)

:Ip
$TEMP = "%temp%\ping.txt"
 
SHELL '%COMSPEC% /c ping $Server -n 1 | find /i "TTL" >"$TEMP"'
 
$IP = ""
If Open(1,$TEMP) = 0 
 $Line = ReadLine(1)
 If $Line
  $IP = SubStr($Line,12,InStr($Line,":")-12)
 EndIf
 $= Close(1)
EndIf
Return 
:End
If $dbe
$rs= olereleaseobject ( $dbe )
EndIf
If $dbs
$rs= olereleaseobject ( $dbs )
EndIf

_________________________
I could have made a neat retort but didn't, for I was flurried and didn't think of it till I was downstairs.
-Mark Twain

Top

#40681 - 2003-06-03 03:52 PM Re: Writing to access db
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	Try converting the diskspace into integer format by using VAL. BTW, the code would be so much shorter with KiXtart 4.21 and a couple of UDFs. It would also be much cleaner. _________________________ There are two types of vessels, submarines and targets.
Top

#40682 - 2003-06-03 08:54 PM Re: Writing to access db
brewdude6 brewdude6 Hey THIS is FUN Registered: 2000-10-21 Posts: 280 Loc: Nashville, TN	Thanks for the tip, although I don't want to ask for someone to do this for me, I am stumped. I looked up the use of "val" and I'm at a loss as to what is failing. Is it possible that the variable I'm declaring initially isn't getting changed? _________________________ I could have made a neat retort but didn't, for I was flurried and didn't think of it till I was downstairs. -Mark Twain
Top

#40683 - 2003-06-03 09:45 PM Re: Writing to access db
Lonkero Lonkero KiX Master Guru Registered: 2001-06-05 Posts: 22346 Loc: OK	eh. not sure what this returns: code: olecallfunc ( $dbe, "opendatabase", "s", "$database" ) but I bet it's not string nor integer. if it's object or something else than string, try to remove your val() around it. mm... what more I think of it, it looks like object "pointer" or reference of somekind, not a val-able stuff at all. _________________________ ! download KiXnet
Top

#40684 - 2003-06-03 10:29 PM Re: Writing to access db
Shawn Shawn Administrator Registered: 1999-08-13 Posts: 8611	Im a little bit at a loss in terms of what exactly isn't working. I assume that the oledb inserts are working were and still are working ? Whats broke exactly ?
Top

#40685 - 2003-06-03 11:02 PM

Re: Writing to access db

brewdude6 Offline

Hey THIS is FUN

Registered: 2000-10-21
Posts: 280
Loc: Nashville, TN

Sorry for not being concise in my last post. I am writing to my db just fine. Shawn, when I took your code and made the ":IP" section, everything works without errors except that the drivespace that is returned for every other drive and computer other than the first drive checked (romburner drive c:\) returns a "0" value. The script doesn't error at all.

I've put a few spaces in the script to hopefully make it a little easier to read.

code:

 
;Setconsole ("HIDE")
$engine = "dao.dbengine.36"
$database = "c:\inetpub1\wwwroot\db1.mdb"
$dbe = olecreateobject ( "$engine" )
$dbs = Val ( "&" + olecallfunc ( $dbe, "opendatabase", "s", "$database" ))

$Space = GetDiskSpace("\\romburner\c$")
$Server = "romburner"
$Drive = "C"
Gosub "Calculate"
Gosub "Ip"
Gosub "WriteToFile"
		
$Space = GetDiskSpace("\\romburner\d$")
$Server = "romburner"
$Drive = "D"
Gosub "Calculate"
Gosub "Ip"
Gosub "WriteToFile"

$Space = GetDiskSpace("\\towens2k\c$")
$Server = "towens2k"
$Drive = "C"
Gosub "Calculate"
Gosub "Ip"
Gosub "WriteToFile"

$Space = GetDiskSpace("\\towens2k\d$")
$Server = "towens2k"
$Drive = "D"
Gosub "Calculate"
Gosub "Ip"
Gosub "WriteToFile"

GoTo "End"

:WriteToFile
;$X = Writeline (1, "Free Space On $Server $Drive Drive: " + "$ErrMsg" + Chr(013) + Chr(010))
$rs = olecallfunc ( $dbs, "execute", "s","INSERT INTO TABLE3 (Dato,Server,Drive,Space,Ip) VALUES ('@Date','$Server','$Drive','$ErrMsg','$ip');")
If @error GoTo End EndIf
Return

:Calculate
Select
Case $Space < 1024 ;** Kilobytes
$Space = $Space * 100
$Units = " Kilobytes" 
Case $Space => 1024 AND $Space < 1048576 ;** Megabytes
$Space = ($Space * 100) / 1024 
$Units = " Megabytes"
Case $Space => 1048576 ;** Gigabytes
$Space = $Space / 10486
$Units = " Gigabytes"
EndSelect
;** Convert the $Space value to a string by adding a space to the beginning
$Space = " " + $Space
$Length = Len($Space)

;** Add the decimal point 2 spaces from the end.
$Space = SubStr($Space, 1, $Length - 2) + "." + SubStr($Space,$Length - 1, 2)
$ErrMsg = LTrim($Space)
Return

:Ip
$TEMP = "%temp%\ping.txt"
 
SHELL '%COMSPEC% /c ping $Server -n 1 | find /i "TTL" >"$TEMP"'
 
$IP = ""
If Open(1,$TEMP) = 0 
 $Line = ReadLine(1)
 If $Line
  $IP = SubStr($Line,12,InStr($Line,":")-12)
 EndIf
 $= Close(1)
EndIf
Return 
:End
If $dbe
$rs= olereleaseobject ( $dbe )
EndIf
If $dbs
$rs= olereleaseobject ( $dbs )
EndIf

Thanks in advance

_________________________
I could have made a neat retort but didn't, for I was flurried and didn't think of it till I was downstairs.
-Mark Twain

Top

#40686 - 2003-06-04 04:24 AM Re: Writing to access db
brewdude6 brewdude6 Hey THIS is FUN Registered: 2000-10-21 Posts: 280 Loc: Nashville, TN	I guess if you struggle long enough you eventually find your way....at least I seem to. My problem was in my "close" statement for the txt file I was writing to. Thanks to everyone that tried and did help me. _________________________ I could have made a neat retort but didn't, for I was flurried and didn't think of it till I was downstairs. -Mark Twain
Top

Page 1 of 1

Previous Topic View All Topics

Index Next Topic

Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart

Hop to:

Shout Box

Search the board with:
superb Board Search
or try with google:

	Web kixtart.org