Announcement

Collapse
No announcement yet.

program won't run from a batch file

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • program won't run from a batch file

    I'm not sure where to put this, but since PB folks are smart, here is what's happening.
    I have a scheduled maintenance program that runs every night.
    It's a small PBCC program called towerEmails.exe.

    I've always started with a batch file in my c:\batch folder
    Here is what the batch file looks like:
    Code:
    c:
    cd\bas_src\cc\dataFlex
    towerEmails.exe
    exit /B 0
    If I run the batch file from the c:\batch folder, it crashes after it lists 5 records.



    If I run the EXE from the c:\bas_src\cc\dataFlex folder, it will crash about 1/2 the time.

    If I open the IDE and compile and run it, it will run 100% of the time.


    I don't know how to start troubleshooting this. It's been running for months and suddenly won't work right.


    here is the program if it helps:

    Code:
    #Include "sqlt3.inc"
    #Include "SQLT3ProDLL.INC"  
    
    %ticketsDB=1             
    %nbsDB=2
    $writePath="\\hahn\websites\nbson\Data\"              
    $sq="'"
    
    Function PBMain()
        Local sql As String   
        Local errmsg As String         
        Local userName As String      
        Local wTower,wEmail As String
    
        On Error GoTo errout
    
      ' open and ititialize sql tools
        openDatabase()                     
    
      ' remove all old records
        sql="delete from tblTowerEmails where ID is not null"
        SQL_Statement(%ticketsDB,1,%SQL_STMT_IMMEDIATE, sql)
        errmsg = SQL_ErrorQuickAll
        If errmsg <> "" Then GoTo errout   
    
        sql="select"+$CrLf
        sql+="    WIRELESS.TOWER,"+$CrLf
        sql+="    CMASTER.EMAIL"+$CrLf
        sql+="from WIRELESS"+$CrLf
        sql+="left join CMASTER on WIRELESS.CUSTID=CMASTER.CUSTID"+$CrLf
        sql+="where CMASTER.EMAIL is not null"+$CrLf
        sql+="and CMASTER.EMAIL <> "+$Sq+$Sq+$CrLf        
    
        SQL_Statement(%nbsDB,1,%SQL_STMT_IMMEDIATE, sql)
        errmsg = SQL_ErrorQuickAll
        If errmsg <> "" Then GoTo errout    
    
        Do
          SQL_FetchResult(%nbsDB,1,%NEXT_ROW)
          If Not SQL_EndOfData(%nbsDB,1) Then    
            wTower=sql_resultColumnString(%nbsDB,1,1)
            wEmail=sql_resultColumnString(%nbsDB,1,2)   
    
            Print wTower,wEmail  
    
            ' insert
            sql="insert into tblTowerEmails (tower,email) values("+sqlPad(wTower)+","+sqlpad(wEmail)+")"
            SQL_Statement(%ticketsDB,1,%SQL_STMT_IMMEDIATE, sql)
            errmsg = SQL_ErrorQuickAll
            If errmsg <> "" Then GoTo errout   
          Else
            Exit Do
          End If
        Loop  
    
        Print ""
        Color 12,0
        Print "done... quitting in 5 seconds"
        Sleep 5000
    
        sql_Shutdown        
    
        Exit Function
    errOut:
      Print "error: "+errMsg        
      Print ""
      Color 12,0
      Print "press any key to exit"
    
      sql_Shutdown          
      waitkey$    
    
    End Function                     
    
    
    
    '---------------------------------------------------------------------------
    '      sub name: openDatabase
    '   description: opens sql database
    '       returns: empty string (OK) or error
    Function openDatabase() As String         
         Local cString As String 
    
      ' open and ititialize sql tools
        SQL_Authorize $mySQLkey
        SQL_Initialize 3,3,50,3,3,0,0,0                 
    
        cString="Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=n:\nbson\data\tickets.mdb;Uid=Admin;Pwd=;"   
        sql_openDatabase(%ticketsDB,cString,%PROMPT_TYPE_NOPROMPT)      
        If SQL_ErrorQuickAll<>"" Then          
          Function = SQL_ErrorQuickAll     
          Exit Function
        End If         
    
        cString="DRIVER=CONNX32;DSN=CNX_06142014;dd=F:\NBS\DF32\usr\cnxnbs3.cdd;Dbq=F:\NBS\DF32\usr\cMaster.dat;Uid=foo;pwd=foo;"
        sql_openDatabase(%nbsDB,cString,%PROMPT_TYPE_NOPROMPT)                                                     
        If SQL_ErrorQuickAll<>"" Then          
          Function = SQL_ErrorQuickAll     
          Exit Function
        End If     
    
        Function=""
    
    End Function         
    
    
    
    '---------------------------------------------------------------------------
    '      sub name: sqlPad ()
    '   description: pads variable to ready it for sql statement submission
    '         input: string to format
    '       outputs: formatted string
    Function sqlPad(s As String) As String      
    
      s=Trim$(s)      
      If s="undefined" Then s=""
      If UCase$(Trim$(s))="NULL" Then s=""
    
      If Trim$(s) = "" Then
        Function = "NULL"
      Else    
        'REPLACE $sq WITH $sq+$sq IN s
        Replace $sq With "`" In s
        Replace $Dq With "`" In s
        Function = $sq+s+$sq     
      End If    
    End Function








  • #2
    In a GUI program "has stopped working" usually (?) means... "is not servicing its message loop often enough to keep Windows happy". Not sure what it means in a console app. Maybe poll the keyboard (or add SLEEP 1) inside your main loop?

    I'd run a SQL Tools trace to narrow down where it's failing.

    "Not my circus, not my monkeys."

    Comment


    • #3
      Sleep 1 didn't do it, but Sleep 100 did.
      Takes a little longer, but that's fine.

      Thank you!

      Comment


      • #4
        What did you see in the event log as the error?

        Comment


        • #5
          What I have found seems to help these CMD file things is to use the start verb..

          Code:
           c:
          cd\bas_src\cc\dataFlex
          start [options] towerEmails.exe
          exit /B 0
          Also not sure I'd force the exit with RC 0 .. if TowerEmails.exe (your program) sets a return code, that value is in the environment variable %ERRORLEVEL% when TowerEmails.exe completes.

          "Typically" if I create a CMD-line application, at a minimum I exit with RC = 0 (no error) or 1 (some kind of error, check log). Obviously you can get quite sophisticated with return codes, logging etc.

          "Creating Programs for Unattended Operation" seems to be a lost art. I have worked with a couple of "experts" over the past couple of years, none of whom seemed to have so much a clue that you can't just "Exit Program" and for sure you cannot prompt an operator to dismiss a message if you are creating applications designed for unattended operation.

          As it turns out, "return code" can be a really good way to communicate any 'issues' which occurred during runtime of these programs.

          MCM
          Last edited by Michael Mattias; 12 May 2017, 04:04 PM.
          Michael Mattias
          Tal Systems Inc.
          Racine WI USA
          mmattias@talsystems.com
          http://www.talsystems.com

          Comment


          • #6
            > It's been running for months and suddenly won't work right.

            > Sleep 1 didn't do it, but Sleep 100 did

            Wow, I hate problems like that, you never know when they'll return. I'd be tempted to fiddle with it, since you can now turn the problem on and off. Just a hunch, since I got lucky on my third guess... try disabling your anticrap software.

            "Not my circus, not my monkeys."

            Comment


            • #7
              You may have already found solution, but just looking
              Code:
              Function PBMAIN()
                On Error GoTo errout   ' If a PB error,  ERR and or ERL not displayed in errout
                ' If errmsg <> "" Then GoTo errout   'may work, but EXIT suggested and handle outside of loop
              End function

              Comment


              • #8
                I am with Mike on using the start command.
                I event put this most of the time in batch files to keep from having issues.
                Code:
                IF EXIST C:\DIRECTORY\PROGRAM.EXE  START C:\DIRECTORY\PROGRAM.EXE
                p purvis

                Comment


                • #9
                  OK well the sleep 100 crashed.
                  I changed my batch to
                  Code:
                  start c:\bas_src\cc\dataflex\towerEmails.exe
                  exit
                  Again, if I run from a batch file, it crashes. If I run from the IDE it runs, even after removing the sleep statement.

                  It ALWAYS crashes after the 5th record, so it makes me wonder if there is some data corruption in the source data, thought that doesn't really explain why it runs in the IDE.

                  Comment


                  • #10
                    Originally posted by Shawn Anderson View Post

                    It ALWAYS crashes after the 5th record, so it makes me wonder if there is some data corruption in the source data, thought that doesn't really explain why it runs in the IDE.
                    It is my guess that the '5th record' is more likely to be something to do with timing, or it is the point that you run out of memory - or get a memory corruption..

                    It might be useful to change 'something' to see if you can change the 5th record to another record. That would give us all a lead.

                    Delete the 5th record from the input (if that makes sense) and see what happens to the error.

                    Delete much of the program (you can always do that) and see if the fifth record changes or the error disappears

                    Or add some SLEEPs here and there and see if it changes.

                    Maybe you keep deleting bits of program (or not deleting, but commenting out) until the outcome changes then you will know more. That is what I do when I get an error of this sort. If you can then say the error happens at instruction x - then you do know something that might be useful.

                    Try doing a batch file with another program. This might check whether it is an environment issue - or something to do with batch files.

                    The second rule of debugging is to isolate, isolate, isolate [The first rule is to look somewhere else!!!]

                    Good luck

                    [I]I made a coding error once - but fortunately I fixed it before anyone noticed[/I]
                    Kerry Farmer

                    Comment


                    • #11
                      First thing I'd do is:

                      Code:
                       Do      
                           SQL_FetchResult(%nbsDB,1,%NEXT_ROW)
                      Print "About to fetch Next Row"      
                           If Not SQL_EndOfData(%nbsDB,1) Then    
                      Print "Next Row Fetched"        
                               wTower=sql_resultColumnString(%nbsDB,1,1)        
                               wEmail=sql_resultColumnString(%nbsDB,1,2)            
                      
                               Print wTower,wEmail            
                      
                      ' insert        
                               sql="insert into tblTowerEmails (tower,email) values("+sqlPad(wTower)+","+sqlpad(wEmail)+")"
                      Print sql               
                               SQL_Statement(%ticketsDB,1,%SQL_STMT_IMMEDIATE, sql)
                      Print "Inserted"        
                                errmsg = SQL_ErrorQuickAll        
                               If errmsg <> "" Then\
                      Print "Error Trapped"
                                 GoTo errout          
                              Else
                      Print "No more Data"        
                                 Exit Do      
                             End If    
                      Loop
                      to see exactly where the problem is occurring
                      Last edited by Stuart McLachlan; 14 May 2017, 03:38 AM.
                      --
                      [URL="http://www.camcopng.com"]CAMCo - Applications Development & ICT Consultancy[/URL][URL="http://www.hostingpng.com"]
                      PNG Domain Hosting[/URL]

                      Comment


                      • #12
                        A number of years ago I had a problem with trying to open more than one statement on a single connection. It occurred only with MS-SQL/Server, and only because a certain configuration option was selected when SQL/?erver was installed (# open statements per connection = 1).

                        I see you are trying to execute an INSERT when you still have an open cursor from the SELECT. I don't know how SQL-Tools handles that SQL_Statement call.. but It does not appear that this should or could be the problem, as it looks like the INSERT is against a different DMBS than the open SELECT statement.

                        But maybe it's something to think about.. or maybe just generally how you are handling multiple DBMSs. .
                        Michael Mattias
                        Tal Systems Inc.
                        Racine WI USA
                        mmattias@talsystems.com
                        http://www.talsystems.com

                        Comment


                        • #13
                          Originally posted by Shawn Anderson View Post
                          It ALWAYS crashes after the 5th record, so it makes me wonder if there is some data corruption in the source data, thought that doesn't really explain why it runs in the IDE.
                          Can you change the sort order of %nbsDB, and see if it still fails on the 5th record? The SQLTools code looks OK to me, though I don't recognize the CONNX32 driver, so I can't speak to the syntax there.
                          Real programmers use a magnetized needle and a steady hand

                          Comment


                          • #14
                            Originally posted by Bud Durland View Post

                            Can you change the sort order of %nbsDB, and see if it still fails on the 5th record? The SQLTools code looks OK to me, though I don't recognize the CONNX32 driver, so I can't speak to the syntax there.
                            I changed the sort order and so far, it works everywhere now

                            Code:
                            select
                                WIRELESS.TOWER,
                                CMASTER.EMAIL
                            from WIRELESS
                            left join CMASTER on WIRELESS.CUSTID=CMASTER.CUSTID
                            where CMASTER.EMAIL is not null
                            and CMASTER.EMAIL <> ''
                            order by wireless.tower
                            This makes sense to me as a fix, because the source data is a DataFlex database, which is proprietary. It's not a true SQL database, I use a 3rd party ODBC driver which provides some sort of SQL wrapper for DataFlex.

                            Comment


                            • #15
                              Wow, I hate problems like that,
                              It's been running for months and suddenly won't work right.
                              The explanation is simple: "Software Rot."

                              Michael Mattias
                              Tal Systems Inc.
                              Racine WI USA
                              mmattias@talsystems.com
                              http://www.talsystems.com

                              Comment


                              • #16
                                > It's not a true SQL database

                                No such thing. Structured Query Language support is implemented and supported entirely in software, either thru an ODBC driver, OLE provider, or a proprietary interface.

                                I suppose you could call a DBMS which supports SQL via a proprietary interface a "SQL database" but I wouldn't. .

                                In your case..
                                , I use a 3rd party ODBC driver which provides some sort of SQL wrapper for DataFlex.
                                .. you kind of make my point.

                                More than that, the fact you changed the "ORDER BY" and that 'fixed' your problem tells me that ODBC driver is maybe suspect.

                                (Sheesh, DataFlex? Seems to me I first worked with that back in the early 1980s. That cannot still be around, can it?)
                                Michael Mattias
                                Tal Systems Inc.
                                Racine WI USA
                                mmattias@talsystems.com
                                http://www.talsystems.com

                                Comment


                                • #17
                                  Originally posted by Michael Mattias View Post

                                  (Sheesh, DataFlex? Seems to me I first worked with that back in the early 1980s. That cannot still be around, can it?)
                                  When I had my own software company we used DataFlex as our main language - that was up until 1993
                                  [I]I made a coding error once - but fortunately I fixed it before anyone noticed[/I]
                                  Kerry Farmer

                                  Comment


                                  • #18
                                    Originally posted by Michael Mattias View Post
                                    More than that, the fact you changed the "ORDER BY" and that 'fixed' your problem tells me that ODBC driver is maybe suspect.

                                    (Sheesh, DataFlex? Seems to me I first worked with that back in the early 1980s. That cannot still be around, can it?)
                                    Maybe the ODBC driver is an oldie too. ODBC drivers were not exactly reliable when I first made their acquaintance in the 90s.

                                    Which version of ODBC does the driver implement?

                                    Comment


                                    • #19
                                      Originally posted by Kerry Farmer View Post

                                      When I had my own software company we used DataFlex as our main language - that was up until 1993
                                      I was a Dataflex Development Partner back then - I worked with it regularly from about 1986 to 1993 and built some major systems with it. It was a really powerful RDBMS and development system in its time..

                                      Then I built my first MS Access system in 93 (ver 1.1) and that was the end of new Dataflex development for me. I did continue to work with a few legacy systems until fairly recently.
                                      --
                                      [URL="http://www.camcopng.com"]CAMCo - Applications Development & ICT Consultancy[/URL][URL="http://www.hostingpng.com"]
                                      PNG Domain Hosting[/URL]

                                      Comment


                                      • #20
                                        Originally posted by Michael Mattias View Post
                                        >t.

                                        (Sheesh, DataFlex? Seems to me I first worked with that back in the early 1980s. That cannot still be around, can it?)
                                        .... if you only knew ....

                                        Comment

                                        Working...
                                        X