Results 1 to 14 of 14
  1. #1
    Join Date
    May 2015
    Posts
    8

    Unanswered: automated query in windows ?

    Hello, I registered here hoping someone can help me.

    A friend of mine has a small business and got a Sybase Database on a Windows 8 PC.
    I have access credentials for a read-only user on that DB.

    what I want to do is the following:
    - run a daily automated query
    - put the resultset into a text file (CSV)
    - transfer the text file to my FTP server
    - delete the file

    The problem is, I am an Linux/webserver programmer, on my server I would have a working script in about 30 minutes to do the above, but I got no idea how to do things in Windows.

    I guess I could solve the FTP transfer by using a remote folder sync tool.

    But how to automate a query to run on the database and save the result in a file?

    Thank you for help.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use windows scheduler to call a batch file (s) which do the process(es) you want, when you want
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2015
    Posts
    8
    Quote Originally Posted by healdem View Post
    use windows scheduler to call a batch file (s) which do the process(es) you want, when you want
    Yes, the scheduler part isn't the problem.

    The batch file... what file extension/file type is it? how to execute it? what language is it programmed in? does the language work similarly to PHP?
    Is it possible to simply make the batch file connect to the DB, run the SQL and save the result in a file, as easily as in PHP?

    I have no clue

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you want to run PHP from a batch file, then run php for batch file
    a batch file is nothing more than a list of commands that instead of requiring keyboard input can be 'read' from the file
    by tradition in the windows / MSDOS word they have the extension BAT
    you dont' need to run a critping language, a batch file can be no more than a series of commands you'd issue if you sued cmd. (the old command line shell). from waht you have said so far I doubt youneed to use a scritping language such as PHP or VBScript

    just as in *NIX many programs in windows can also accpet command line arguments that jick off inetrnal processes / 'scripts'
    from what you have said so far you need to kick off a query in Sybase
    sendf the output of that query to a file
    send that file to a remote machine

    as to how you do it I don't know:-
    I don't use Sybase,
    I don't know what FTP tools you'd use and so on
    Windows scheduler will run whatever tasks you want from a batch file when you want
    providing you can run the steps you want from the command shell don't need to use PHP or VBScript
    if thise commands invoce a program, change a driectory and so on then its fine

    if you need nbetter error handling, more flexible processign then by all means use PHP or VBScript or Pythin oir whatever else takes your fancy
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2015
    Posts
    8
    Quote Originally Posted by healdem View Post
    if you want to run PHP from a batch file, then run php for batch file
    a batch file is nothing more than a list of commands that instead of requiring keyboard input can be 'read' from the file
    by tradition in the windows / MSDOS word they have the extension BAT
    you dont' need to run a critping language, a batch file can be no more than a series of commands you'd issue if you sued cmd. (the old command line shell). from waht you have said so far I doubt youneed to use a scritping language such as PHP or VBScript

    just as in *NIX many programs in windows can also accpet command line arguments that jick off inetrnal processes / 'scripts'
    from what you have said so far you need to kick off a query in Sybase
    sendf the output of that query to a file
    send that file to a remote machine

    as to how you do it I don't know:-
    I don't use Sybase,
    I don't know what FTP tools you'd use and so on
    Windows scheduler will run whatever tasks you want from a batch file when you want
    providing you can run the steps you want from the command shell don't need to use PHP or VBScript
    if thise commands invoce a program, change a driectory and so on then its fine

    if you need nbetter error handling, more flexible processign then by all means use PHP or VBScript or Pythin oir whatever else takes your fancy
    okay, thanks for replying, but the "how to do it" is the part that I am wondering about as well!
    I posted in the sybase forum to hear from people who use sybase how they normally do this kind of thing under windows.

    I'm pretty sure the command line (cmd) does not allow to run queries and process the recordset.

    In Debian, I would just write a PHP script doing everything I need and then add a cron job telling the php interpreter to run the script, et voilà!

    I have been searching the net for hours already, but find nothing except expensive "business intelligence automation tools" claiming to do exactly what I want, but selling for several thousand dollars for what would take half an hour to do on debian, I'm gutted!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    For cron read windows scheduler. Thats the bit in the windows world that wakes up and does stuff without human intervention as cron does in the *NIX world

    i would expect sybase to have a command line option which allows you to run a query
    if you are used to using php to do this sort of thing then use php to do this sort of thing. Php isnt unique to Linux, nor is it unique to webservers.

    As expected sybase does have the option of running a query from the command line. Try googling 'sybase run query from command line'

    If you can design a php script to do this task in Debian Linux then you can do this task in windows. The only difference is going to be the file specifications the rest should all be the same, esoecially if you are using the right objects within PHP
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1

    Commands to look at

    Use isql to run a query
    C:\>isql -?
    Syntax Error in '-?'.
    usage: isql [-b] [-e] [-F] [-p] [-n] [-v] [-W] [-X] [-Y] [-Q]
    [-a display_charset] [-A packet_size] [-c cmdend] [-D database]
    [-E editor] [-h header] [-H hostname] [-i inputfile]
    [-I interfaces_file] [-J client_charset] [-K keytab_file]
    [-l login_timeout] [-L textlimit] [-m errorlevel] [-M labelname labelvalue]
    [-o outputfile] [-P password] [-R remote_server_principal]
    [-s col_separator] [-S server_name] [-t timeout] [-U username]
    [-V [security_options]] [-w column_width] [-y sybase directory]
    [-z localename] [-Z security_mechanism] [-x trusted.txt_file]
    [--retserverror] [--conceal [wildcard]] [--appname application_name]
    [--history [p]length [--history_file file_name]] [--URP rempwstring]
    [--filemode outputfile_permission] [--help]

    Or maybe use bcp
    C:\>bcp -?
    usage: bcp [[db_name.]owner.]table_name[:slice_num] [partition pname] {in | out} [filename]
    [-m maxerrors] [-f formatfile] [-e errfile] [-d discardfileprefix]
    [-F firstrow] [-L lastrow] [-b batchsize]
    [-n] [-c] [-t field_terminator] [-r row_terminator]
    [-U username] [-P password] [-I interfaces_file] [-S server]
    [-a display_charset] [-z language] [-v]
    [-i input_file] [-o output_file]
    [-A packet size] [-J client character set]
    [-T text or image size] [-E] [-g id_start_value] [-N] [-W] [-X]
    [-M LabelName LabelValue] [-labeled]
    [-K keytab_file] [-R remote_server_principal] [-C]
    [-V [security_options]] [-Z security_mechanism] [-Q] [-Y]
    [-y sybase directory] [-x trusted.txt_file]
    [--clienterr errfile] [--maxconn maximum_connections]
    [--show-fi] [--hide-vcc]
    [--colpasswd [[[db_name.[owner].]table_name.]column_name [password]]]
    [--keypasswd [[db_name.[owner].]key_name [password]]]
    [--initstring ASE initialization string] [--quoted-fname]
    [--filemode file_permission]

    And for ftp:
    C:\>ftp -?

    Transfers files to and from a computer running an FTP server service
    (sometimes called a daemon). Ftp can be used interactively.

    FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a] [-A] [-x:sendbuffer] [-r:recvbuffer] [-b:asyncbuffers] [-w:windowsize] [host]

    -v Suppresses display of remote server responses.
    -n Suppresses auto-login upon initial connection.
    -i Turns off interactive prompting during multiple file
    transfers.
    -d Enables debugging.
    -g Disables filename globbing (see GLOB command).
    -s:filename Specifies a text file containing FTP commands; the
    commands will automatically run after FTP starts.
    -a Use any local interface when binding data connection.
    -A login as anonymous.
    -x:send sockbuf Overrides the default SO_SNDBUF size of 8192.
    -r:recv sockbuf Overrides the default SO_RCVBUF size of 8192.
    -b:async count Overrides the default async count of 3
    -w:windowsize Overrides the default transfer buffer size of 65535.
    host Specifies the host name or IP address of the remote
    host to connect to.

    Notes:
    - mget and mput commands take y/n/q for yes/no/quit.
    - Use Control-C to abort commands.

  8. #8
    Join Date
    May 2015
    Posts
    8
    ok, for anyone looking for actual hands-on help instead of high level blabber and copies of man pages, I have put together the following VBS script from different sources on the net.
    It's still in a basic state, but it should get the ball rolling for most uses, even with most databases accessible by ODBC under Windows.

    The first part of the script solves the problem of 32 bit DB drivers used on 64 bit systems.

    Code:
    ' C:\Windows\System32\WScript.exe = WScript.exe
    Dim ScriptHost : ScriptHost = Mid(WScript.FullName, InStrRev(WScript.FullName, "\") + 1, Len(WScript.FullName))
    
    Dim oWs : Set oWs = CreateObject("WScript.Shell")
    Dim oProcEnv : Set oProcEnv = oWs.Environment("Process")
    
    ' Am I running 64-bit version of WScript.exe/Cscript.exe? So, call script again in x86 script host and then exit.
    If InStr(LCase(WScript.FullName), LCase(oProcEnv("windir") & "\System32\")) And oProcEnv("PROCESSOR_ARCHITECTURE") = "AMD64" Then
        ' rebuild arguments
        If Not WScript.Arguments.Count = 0 Then
            Dim sArg, Arg
            sArg = ""
            For Each Arg In Wscript.Arguments
                  sArg = sArg & " " & """" & Arg & """"
            Next
        End If
    
        Dim sCmd : sCmd = """" &  oProcEnv("windir") & "\SysWOW64\" & ScriptHost & """" & " """ & WScript.ScriptFullName & """" & sArg
    '    WScript.Echo "Call " & sCmd
        oWs.Run sCmd
        WScript.Quit
    End If
    
    
    Dim data
    Dim recordCount
    Dim regEx
    Set regEx = new RegExp
    regEx.Pattern = "\r|\n|,|"""
    
    Set con = CreateObject("ADODB.Connection")
    con.ConnectionString = "DSN=YOUR DSN NAME;Driver={SYBASE SYSTEM 11};Uid=YOURUSERNAME;Pwd=YOURPASSWORD;"
    con.Open
        
    strQry = "YOUR SQL QUERY"
    set data = con.execute(strQry)
    
    Set filsSysObj = CreateObject("Scripting.FileSystemObject")  
    
    If filsSysObj.FileExists("C:\Users\Admin\Desktop\EXAMPLE.csv") Then
    filsSysObj.DeleteFile "C:\Users\Admin\Desktop\EXAMPLE.csv"
    End If 
    
    Set csvFile = filsSysObj.OpenTextFile("C:\Users\Admin\Desktop\EXAMPLE", 8, True)
    
    recordCount = data.Fields.Count      
    
    do until data.EOF
    Separator = ""
    for i = 0 to data.Fields.Count - 1
    Column = data.Fields( i ).Value & ""
    if regEx.Test( Column ) then
    Column = """" & Replace( Column, """", """""" ) & """"
    end if
    csvFile.Write Separator & Column
    Separator = ","
    next
    csvFile.Write vbNewLine
    data.MoveNext
    loop
    
    csvFile.Close
    
    'FTP Upload
    
    Set oShell = CreateObject("Shell.Application")
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    'Path to file or folder to upload
    path = "C:\Users\Admin\Desktop\FTP_Sync\CSVFileGR.csv"
    
    FTPUpload(path)
    Sub FTPUpload(path)
    
    On Error Resume Next
    
    'Copy Options: 16 = Yes to All
    Const copyType = 16
    
    'FTP Wait Time in ms
    waitTime = 10000
     
    FTPUser = "YOURFTPUSER"
    FTPPass = "YOURFTPPASSWORD"
    FTPHost = "YOURDOMAIN"
    FTPDir = "/"
    
    strFTP = "ftp://" & FTPUser & ":" & FTPPass & "@" & FTPHost & FTPDir
    Set objFTP = oShell.NameSpace(strFTP)
    
    'Make new folder on FTP site
    'objFTP.NewFolder "FTP Backup"
    
    'Upload single file      
    If objFSO.FileExists(path) Then
    
    Set objFile = objFSO.getFile(path)
    strParent = objFile.ParentFolder
    Set objFolder = oShell.NameSpace(strParent)
    
    Set objItem = objFolder.ParseName(objFile.Name)
    
    'Wscript.Echo "Uploading file " & objItem.Name & " to " & strFTP
    objFTP.CopyHere objItem, copyType
    
    End If
    
    'Upload all files in folder
    'If objFSO.FolderExists(path) Then
    'Code below can be used to upload entire folder
    'Set objFolder = oShell.NameSpace(path)
    'Wscript.Echo "Uploading folder " & path & " to " & strFTP
    'objFTP.CopyHere objFolder.Items, copyType
    'End If
    
    If Err.Number <> 0 Then
    Wscript.Echo "Error: " & Err.Description
    End If
    
    'Wait for upload
    WScript.Sleep waitTime
    
    End Sub

  9. #9
    Join Date
    May 2015
    Posts
    8
    For anyone looking for actual hands-on help instead of high level blabber and copies of man pages, I have put the following VBS togther from various sources on the web.
    It is still in a basic state, but should get the ball rolling for most uses and is even usable not just for Sybase, but also for any other database accessible through ODBC on Windows. Just replace the driver reference.
    The first part of the script solves the problem of 32 bit ODBC drivers used on 64 bit systems.

    One problem that remains is that I have found no way to overwrite or delete the remote file without dialogbox interaction.
    I solved this by having my server script delete the FTP file after processing.

    Code:
    ' C:\Windows\System32\WScript.exe = WScript.exe
    Dim ScriptHost : ScriptHost = Mid(WScript.FullName, InStrRev(WScript.FullName, "\") + 1, Len(WScript.FullName))
    
    Dim oWs : Set oWs = CreateObject("WScript.Shell")
    Dim oProcEnv : Set oProcEnv = oWs.Environment("Process")
    
    ' Am I running 64-bit version of WScript.exe/Cscript.exe? So, call script again in x86 script host and then exit.
    If InStr(LCase(WScript.FullName), LCase(oProcEnv("windir") & "\System32\")) And oProcEnv("PROCESSOR_ARCHITECTURE") = "AMD64" Then
        ' rebuild arguments
        If Not WScript.Arguments.Count = 0 Then
            Dim sArg, Arg
            sArg = ""
            For Each Arg In Wscript.Arguments
                  sArg = sArg & " " & """" & Arg & """"
            Next
        End If
    
        Dim sCmd : sCmd = """" &  oProcEnv("windir") & "\SysWOW64\" & ScriptHost & """" & " """ & WScript.ScriptFullName & """" & sArg
    '    WScript.Echo "Call " & sCmd
        oWs.Run sCmd
        WScript.Quit
    End If
    
    
    Dim data
    Dim recordCount
    Dim regEx
    Set regEx = new RegExp
    regEx.Pattern = "\r|\n|,|"""
    
    Set con = CreateObject("ADODB.Connection")
    con.ConnectionString = "DSN=YOUR DSN NAME;Driver={SYBASE SYSTEM 11};Uid=YOURUSERNAME;Pwd=YOURPASSWORD;"
    con.Open
        
    strQry = "YOUR SQL QUERY"
    set data = con.execute(strQry)
    
    Set filsSysObj = CreateObject("Scripting.FileSystemObject")  
    
    If filsSysObj.FileExists("C:\Users\Admin\Desktop\EXAMPLE.csv") Then
    filsSysObj.DeleteFile "C:\Users\Admin\Desktop\EXAMPLE.csv"
    End If 
    
    Set csvFile = filsSysObj.OpenTextFile("C:\Users\Admin\Desktop\EXAMPLE", 8, True)
    
    recordCount = data.Fields.Count      
    
    do until data.EOF
    Separator = ""
    for i = 0 to data.Fields.Count - 1
    Column = data.Fields( i ).Value & ""
    if regEx.Test( Column ) then
    Column = """" & Replace( Column, """", """""" ) & """"
    end if
    csvFile.Write Separator & Column
    Separator = ","
    next
    csvFile.Write vbNewLine
    data.MoveNext
    loop
    
    csvFile.Close
    
    'FTP Upload
    
    Set oShell = CreateObject("Shell.Application")
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    'Path to file or folder to upload
    path = "C:\Users\Admin\Desktop\FTP_Sync\CSVFileGR.csv"
    
    FTPUpload(path)
    Sub FTPUpload(path)
    
    On Error Resume Next
    
    'Copy Options: 16 = Yes to All
    Const copyType = 16
    
    'FTP Wait Time in ms
    waitTime = 10000
     
    FTPUser = "YOURFTPUSER"
    FTPPass = "YOURFTPPASSWORD"
    FTPHost = "YOURDOMAIN"
    FTPDir = "/"
    
    strFTP = "ftp://" & FTPUser & ":" & FTPPass & "@" & FTPHost & FTPDir
    Set objFTP = oShell.NameSpace(strFTP)
    
    'Make new folder on FTP site
    'objFTP.NewFolder "FTP Backup"
    
    'Upload single file      
    If objFSO.FileExists(path) Then
    
    Set objFile = objFSO.getFile(path)
    strParent = objFile.ParentFolder
    Set objFolder = oShell.NameSpace(strParent)
    
    Set objItem = objFolder.ParseName(objFile.Name)
    
    'Wscript.Echo "Uploading file " & objItem.Name & " to " & strFTP
    objFTP.CopyHere objItem, copyType
    
    End If
    
    'Upload all files in folder
    'If objFSO.FolderExists(path) Then
    'Code below can be used to upload entire folder
    'Set objFolder = oShell.NameSpace(path)
    'Wscript.Echo "Uploading folder " & path & " to " & strFTP
    'objFTP.CopyHere objFolder.Items, copyType
    'End If
    
    If Err.Number <> 0 Then
    Wscript.Echo "Error: " & Err.Description
    End If
    
    'Wait for upload
    WScript.Sleep waitTime
    
    End Sub

  10. #10
    Join Date
    May 2015
    Posts
    8
    For anyone looking for actual hands-on help instead of high level blabber and copies of man pages, I have put the following VBS togther from various sources on the web.
    It is still in a basic state, but should get the ball rolling for most uses and is even usable not just for Sybase, but also for any other database accessible through ODBC on Windows. Just replace the driver reference.
    The first part of the script solves the problem of 32 bit ODBC drivers used on 64 bit systems.

    One problem that remains is that I have found no way to overwrite or delete the remote file without dialogbox interaction.
    I solved this by having my server script delete the FTP file after processing.

    Since it looks like I cannot paste my code here (this forum has some serious cache and content filtering issues), here is a pastebin link:
    http://pastebin.com/mvFFB1WM

  11. #11
    Join Date
    May 2015
    Posts
    8
    this is a test, it seems I can't reply

    EDIT: forum will not accept code, will not accept link to code... I'm outta here.

  12. #12
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Have you tried the [CODE] tag?
    I'm not crazy, I'm an aeroplane!

  13. #13
    Join Date
    May 2015
    Posts
    8
    Quote Originally Posted by Martijnvs View Post
    Have you tried the [CODE] tag?
    I don't know vbscript, odbc and windows... but I'm not a total idiot.

    This site probably runs some filter, mod_security or something similar.

  14. #14
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by manarak View Post
    I don't know vbscript, odbc and windows... but I'm not a total idiot.

    This site probably runs some filter, mod_security or something similar.
    Relax, I was just trying to help. There are gurus and newbies here, and everything in between. We can't smell the a user's knowledge so people offer help on various levels of complexity.
    I'm not crazy, I'm an aeroplane!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •