mysql script!

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

mysql script!

Logged in as: Guest

arrSession:exec spGetSession 2,2,46597

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> mysql script!
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

mysql script! - 5/3/2007 2:05:00 AM

jtrinidadmap

Posts: 6
Score: 0
Joined: 5/3/2007
Status: offline

Original message moved by dm_4ever
Reason : Moving To Appropriate Forum
hi, im newbi in vbscript, i need help, i try to do a script that makes 2 conections, first, with one server to get the printer that are installed there, on this server, 2n u have to make another conection in my sql server to put the info of the printers in a table, i already have the code for make the conections and the code for get the info of the printers, now when i run the script and check my database, the table is empty, somebody can help me?
thanks!!

Post #: 1

RE: mysql script! - 5/3/2007 2:41:23 AM

ebgreen

Posts: 5250
Score: 31
Joined: 7/12/2005
Status: offline

we will need to see your code...change the server names and any security information in the script before you post it.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to jtrinidadmap)

Post #: 2

RE: mysql script! - 5/3/2007 2:51:09 AM

jtrinidadmap

Posts: 6
Score: 0
Joined: 5/3/2007
Status: offline

On Error Resume Next

Dim strComputer, k, objGroup, objShell
Dim objWMIService, colItems, ErrState, strPorts, strPortNum
dim consulta, cnn
Set objShell = CreateObject("WScript.Shell")

'Value assignment

Set objShell = CreateObject("WScript.Shell")

''Sheet = spreadsheet page, k = row in sheet
Sheet = 1
k = 2

strComputer = InputBox ("Please type the print server name to check, " & vbCrLf & _
"Else enter ALL for all CC print servers", "Server Name","CCPS01")

if strComputer = "" then
' WScript.quit
'end if

''Change this to fit your server situation
Select Case UCase(strComputer)
Case "ALL"
   PrintServer("CCPS01")
   Sheet = Sheet + 1
   PrintServer("PS02")
   Sheet = Sheet + 1
   PrintServer("PS03")
Case Else
   PrintServer(strComputer)
End Select

Function PrintServer(strComputer)

k=2
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colItems = objWMIService.ExecQuery("Select * from Win32_Printer",,48)

Set colPorts = objWMIService.ExecQuery("Select * from Win32_TCPIPPrinterPort")
Set strPorts = CreateObject("Scripting.Dictionary")
Set strPortNum = CreateObject("Scripting.Dictionary")
For Each objPort in colPorts
strPorts.add objPort.Name, objPort.HostAddress
strPortNum.add objPort.Name, objPort.PortNumber
Next
end function

'para la conexion con mysql

Private Sub Form_Load()
   Set cnn = New ADODB.Connection
   cnn.ConnectionString ="Driver={MySQL ODBC 3.51 Driver};Server=servername;Port=3306;Database=dbname;User=user;Password=password;Option=3;"
   cnn.Open
     Set rec = New ADODB.Recordset
   rec.Open "SELECT * FROM ""TABLE""" , cnn, adOpenDynamic, adOpenDynamic
   rec.MoveFirst


Do While Not rec.EOF
       MsgBox rec.Fields("Cuenta").Value
       rec.MoveNext
Set command = New ADODB.Command
   command.ActiveConnection=cnn

LOAD DATA INFILE "C:File.xls" INTO TABLE
   consulta = "insert into tabla (Nom_Impresora,IP,Marca_Modelo,Depto,Ubicacion_Logica,Status,Ubicacion_Fisica) values("& objItem.Name &","strPorts.Item","objItem.DriverName","objItem.Comment","","objItem.Status","objItem.Location")"
command.text=consulta
command.execute
Loop
cnn.close
   'Next

End Sub
'WScript.quit
end if

WScript.Echo "Printer listing is done"

Wscript.quit

(in reply to jtrinidadmap)

Post #: 3

RE: mysql script! - 5/3/2007 7:53:34 AM

ehvbs

Posts: 2223
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi jtrinidadmap,

Because I can't test drive your script, all I can say is:

(1) get rid of the global "On Error Resume Next" and see what the error messages tell you

(2) Check
       rec.Open "SELECT * FROM ""TABLE""" , cnn, adOpenDynamic, adOpenDynamic

(3) Try
      Set command.ActiveConnection=cnn

(4) Is there a Comment ' missing
     LOAD DATA INFILE "C:File.xls" INTO TABLE

(5) This looks fishy to me:

consulta = "insert into tabla (Nom_Impresora,IP,Marca_Modelo,Depto,Ubicacion_Logica,Status,Ubicacion_Fisica)
values("& objItem.Name
   &","strPorts.Item","objItem.DriverName","objItem.Comment","","objItem.Status","objItem.Location")"

   I would expect something like:

consulta = "insert into tabla (Nom_I, ... ) values( '" & oItem.Name & "', '" ... ) "

   String Values should be enclosed in single quotes.

Sorry I'm not able to give better advice.

Good luck!

ehvbs

(in reply to jtrinidadmap)

Post #: 4

RE: mysql script! - 5/4/2007 1:37:06 AM

jtrinidadmap

Posts: 6
Score: 0
Joined: 5/3/2007
Status: offline

thanks! i try that i'll tell you what happend, i hope i found more info! jeje thanks!

(in reply to jtrinidadmap)

Post #: 5

RE: mysql script! - 5/7/2007 3:01:21 AM

jtrinidadmap

Posts: 6
Score: 0
Joined: 5/3/2007
Status: offline

thanks i try that but when the scripts un, display a error message, says: "Error in line 72 char 11" Expected end statement"

and the line 72 is (in bold)
LOAD DATA INFILE "C:Printers_ejercito_root.xls" INTO IMPRESORAS
consulta = "insert into tabla (Nom_Impresora,IP,Marca_Modelo,Depto,Ubicacion_Logica,Status,Ubicacion_Fisica) values('& objItem.Name &','strPorts.Item','objItem.DriverName','objItem.Comment',' ','objItem.Status','objItem.Location')"
command.text=consulta
command.execute
Loop
cnn.close
Next
End Sub
'WScript.quit
end if

WScript.Echo "Printer listing is done"
Wscript.quit

and i check and the file exists i'll been thankful if anyone help me thanks!!

(in reply to jtrinidadmap)

Post #: 6

RE: mysql script! - 5/7/2007 3:59:02 AM

ehvbs

Posts: 2223
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi jtrinidadmap,

your line 72

LOAD DATA INFILE "C:Printers_ejercito_root.xls" INTO IMPRESORAS

isn't valid VBScript but a MySQL statement (implemented by the following code, I assume).
As I told you before, it should be commented out/hidden from the compiler by putting
a ' (or REM) before it.

(in reply to jtrinidadmap)

Post #: 7

RE: mysql script! - 5/8/2007 1:50:42 AM

jtrinidadmap

Posts: 6
Score: 0
Joined: 5/3/2007
Status: offline

HI, i make the changes you recommend but the scripts dosnt makes anything. this is my code looks like

'On Error Resume Next

Dim strComputer, k, objGroup, objShell
Dim objWMIService, colItems, ErrState, strPorts, strPortNum
dim consulta, cnn
Set objShell = CreateObject("WScript.Shell")

'Value assignment

Set objShell = CreateObject("WScript.Shell")

''Sheet = spreadsheet page, k = row in sheet
Sheet = 1
k = 2

strComputer = InputBox ("Please type the print server name to check, " & vbCrLf & _
"Else enter ALL for all CC print servers", "Server Name","CCPS01")

if strComputer = "" then
' WScript.quit
'end if

''Change this to fit your server situation
Select Case UCase(strComputer)
Case "ALL"
   PrintServer("CCPS01")
   Sheet = Sheet + 1
   PrintServer("PS02")
   Sheet = Sheet + 1
   PrintServer("PS03")
Case Else
   PrintServer(strComputer)
End Select

WScript.Echo "Conectando con el Print Server..."

Function PrintServer(strComputer)

k=2
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colItems = objWMIService.ExecQuery("Select * from Win32_Printer",,48)

Set colPorts = objWMIService.ExecQuery("Select * from Win32_TCPIPPrinterPort")
Set strPorts = CreateObject("Scripting.Dictionary")
Set strPortNum = CreateObject("Scripting.Dictionary")
For Each objPort in colPorts
strPorts.add objPort.Name, objPort.HostAddress
strPortNum.add objPort.Name, objPort.PortNumber
Next
end function

WScript.Echo "Conectando con el servidor MYSQL..."

'para la conexion con mysql

' Private Sub Form_Load()
Sub Form_Load()
   Set cnn = New ADODB.Connection
   cnn.ConnectionString ="Driver={MySQL ODBC 3.51 Driver};Server=Servername;Port=3306;Database=dbname;User=usr;Password=pass;Option=3;"
   cnn.Open
     Set rec = New ADODB.Recordset
   rec.Open "SELECT * FROM ""TABLE""" , cnn, adOpenDynamic
   rec.MoveFirst


Do While Not rec.EOF
       MsgBox rec.Fields("Cuenta").Value
       rec.MoveNext
Set command = New ADODB.Command
Set command.ActiveConnection=cnn
WScript.Echo "Insertando Datos..."

'LOAD DATA INFILE "C:Printers_ejercito_root.xls" INTO IMPRESORAS
   consulta = "insert into IMPRESORAS(Nom_Impresora,IP,Marca_Modelo,Depto,Ubicacion_Logica,Status,Ubicacion_Fisica) values(""&objItem.Name&"",""&strPorts.Item&"",""&objItem.DriverName&"",""&objItem.Comment&"","",""&objItem.Status&"",""&objItem.Location&"")"

command.text=consulta
command.execute
Loop
cnn.close
   'Next

End Sub
'WScript.quit
end if

WScript.Echo "Printer listing is done"

Wscript.quit

i put echo sentences for know if the script do some activities and didnt display anything, only the last echoe, i didnt know why! can anybody help me? thanks, i revie the table and the script dosnt insert anything, i think its because it dosnt bring any data from the print server. thanks!

(in reply to jtrinidadmap)

Post #: 8

RE: mysql script! - 5/8/2007 7:52:28 AM

ehvbs

Posts: 2223
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi jtrinidadmap,

you need to invest some efforts in the structuring and the layout
of your script. E.g.:

   if strComputer = "" then
   ' WScript.quit
   'end if

By keeping the If and disabling the End If you disabled all code
up to the next End If. So no wonder your script doesn't do anything.

End Sub
'WScript.quit
end if

This proves that you put a Sub into your main code. That is not
legal VBScript, even if the Compiler doesn't complain.

Do While Not rec.EOF
       ...
     Set command = New ADODB.Command

"New ADODB.Command" is not VBScript; you should get an error here. Furthermore
an ADODB.Command should be created once and then used many times (with different
parameters). Creating it in a loop is like buying a new car for each trip.

In your shoes, I'd start afresh with a simple test script that opens
a connection and adds some rows to a test table without trying
to do all things (WMI, User Input, Commands) at once.

One point to start could be the ADO SDK documentation and its samples.

If you insists on continuing with your script, at least use a debugger
to step thru it.

Sorry for being destructive!

ehvbs

(in reply to jtrinidadmap)