Welcome to the VanDyke Software Forums

Join the discussion today!


Go Back   VanDyke Software Forums > Scripting

Reply
 
Thread Tools Rate Thread Display Modes
  #1  
Old 06-27-2014, 05:24 AM
DeeZ DeeZ is offline
Registered User
 
Join Date: Jun 2014
Posts: 10
MSSQL Search

Hello All.
I have an MSSQL Database where is table: [name, ip].
I'd like to make a search in it. I've installed "python-pymssql" before
Code:
# $language = "python"
import os
import sys
import pymssql

from subprocess import *

SCRIPT_TAB = crt.GetScriptTab()

def MMSSQL():
 selectedText = SCRIPT_TAB.Screen.Selection
 if not selectedText.strip():
  exit 
 conn = pymssql.connect(host='172.25.24.23', user='user', password='11111', database='Base')
 cur = conn.cursor()
 cur.execute("SELECT [IP] FROM [Base].[dbo].[Nodes] where [Base].[dbo].[Nodes].[Name] like '%s'",selectedText )
 row = cur.fetchone()

 crt.Dialog.MessageBox(row[0])
 #crt.Session.Connect("/S " & chr(34) & row[0] & chr(34))
 conn.close()

MMSSQL()
And now when I've selected the text '999.Node1' and start this script I get some errors:
the first:
SQL Server error. Incorrect syntax near '999'
line
cur.execute("SELECT [IP] FROM [Base].[dbo].[Nodes] where [Base].[dbo].[Nodes].[Name] like '%s'",selectedText )

Why not '999.Node1'?

the second:
'NoneType' object has attribute '__getitem__'
line
crt.Dialog.MessageBox(row[1])

Please help me? How should I correct this script to works?
Reply With Quote
  #2  
Old 06-27-2014, 08:38 AM
rtb rtb is offline
VanDyke Technical Support
 
Join Date: Aug 2008
Posts: 4,306
Hi DeeZ,

Thanks for the post. I am not familiar with pymssql. Is there anyone in the community that can help DeeZ?

What version of SecureCRT are you using?

What operating system are you using?
__________________
--Todd

VanDyke Software
Technical Support
support@vandyke.com
505-332-5730
Reply With Quote
  #3  
Old 06-29-2014, 10:39 PM
DeeZ DeeZ is offline
Registered User
 
Join Date: Jun 2014
Posts: 10
Quote:
What version of SecureCRT are you using?
What operating system are you using?
Python 2.7.6
SecureCRT Version 7.1.1 (build 264)
Ubuntu 14.04 LTS
pymssql 1.0.2

I've found an error in string:
Code:
cur.execute("SELECT [IP] FROM [Base].[dbo].[Nodes] where [Base].[dbo].[Nodes].[Name] like '%s'" % (selectedText))
Now I only have the second error. I can't get sql reply.
Code:
row = cur.fetchone()
crt.Dialog.MessageBox(row[0])
Reply With Quote
  #4  
Old 06-30-2014, 12:40 PM
rtb rtb is offline
VanDyke Technical Support
 
Join Date: Aug 2008
Posts: 4,306
Hi DeeZ,

Thanks for the update. I am glad to hear that you solved the first issue.

I don't have a database to test your code against, but my guess is that the problem is related to the value that is returned. The MessageBox() method requires text. Can you be certain that the call is returning text?

Here is something to try:
crt.Dialog.MessageBox(str(row[0]))
Do you get better results by forcing row to be a string?
__________________
--Todd

VanDyke Software
Technical Support
support@vandyke.com
505-332-5730
Reply With Quote
  #5  
Old 10-06-2014, 02:38 AM
DeeZ DeeZ is offline
Registered User
 
Join Date: Jun 2014
Posts: 10
Solved

Oh. I did it!
There is no сheck for errors yet. But... ) it already works.

Code:
cat  /etc/odbc.ini 
[srv]
Description=Server
Driver=FreeTDS
Servername=srv

cat  /etc/odbcinst.ini 
[ODBC]
Trace = Yes
TraceFile = /tmp/odbc.log
[FreeTDS]
Description = TDS driver (Sybase/MS SQL) 
Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/i386-linux-gnu/odbc/libtdsS.so         
UsageCount = 1


cat  /etc/freetds/freetds.conf
[global]
        tds version = 8.0
        port = 1433
        text size = 64512
        try server login = yes
        try domain login = no
        client charset = UTF-8
[srv]
        host = 172.25.24.7
        port = 1433
        tds version = 8.0




cat ./Search.py
# $language = "python"
# $interface = "1.0"

import os
import sys
import pyodbc

from subprocess import *

SCRIPT_TAB = crt.GetScriptTab()
crt.Screen.Synchronous = True


def SQLSearch():
    selectedText = SCRIPT_TAB.Screen.Selection
    if not selectedText.strip():
        return
    else:         
        cnx = pyodbc.connect("DSN=srv;DATABASE=db;UID=usr;PWD=123")
        cursor = cnx.cursor()
        cursor.execute("""SELECT TOP 1 [IP] 
                          FROM [db].[dbo].[Nodes] 
                          where [db].[dbo].[Nodes].[Name] like '%s' """ % selectedText)
        row = cursor.fetchone()
        IPa = row[0]
   
        crt.Session.ConnectInTab("/TELNET %s 23""" % IPa)

SQLSearch()
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -6. The time now is 11:56 PM.