Announcement

Collapse
No announcement yet.

program won't run from a batch file

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

  • #41
    Originally posted by Mike Doty View Post
    Post 35 says working from Admin command prompt
    Post 36 Says, nope now works everywhere.

    If it isn't working adding a TowerEmails.exe.manifest might help. Does it shows up anywhere in logs?
    sorry to be unclear:
    I ran as admin and it worked ONCE, then failed.
    Then it worked in normal (non-admin) prompt.
    Now it's failing again.

    It is still an issue.

    Comment


    • #42
      Call me naive if you will , but this sounds like an application program error.

      Probably something silly like, "if file not found, exit" or "on error, skip this next section of code" and in either case, the 'exit" or "skip" occurs because you forgot to perform an explicit error test somewhere.

      Why this is run from a batch file AT ALL is now the mystery!

      Mike Doty's guess at said batch/command file was...
      Code:
      c:
      cd\bas_src\cc\dataFlex
      towerEmails.exe
      exit /B 0
      .. but what the real batch/command file looks like and what the real 'toweremails.exe' program is doing is still guesswork.


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

      Comment


      • #43
        I reposted his batch file from post #1

        Comment


        • #44
          I reposted his batch file from post #1
          Well, I guess I missed that.

          However, it shows I was correct when I said this program does NOT need to run from a batch/command file. If a change directory is needed, the TowerEmails.exe program can do it, or the program can be launched (CreateProcess() or ShellExecuteEx()) specifying an initial directory.
          Michael Mattias
          Tal Systems Inc.
          Racine WI USA
          mmattias@talsystems.com
          http://www.talsystems.com

          Comment


          • #45
            I just don't think it's a batch problem because I have the same issue manually going to a command prompt, and running the program from its directory.

            Comment


            • #46
              Originally posted by Eric Pearson View Post
              I'd run an ODBC trace

              I turned on ODBC trace, and it runs perfectly (albeit slower that normal).
              So what I'm going to do is leave the trace on. At the end of the program, if everything works, it will erase sql.log.
              If it crashes, the sql.log file will be intact and I'll be able to investigate further.


              Comment


              • #47
                I didn't notice this before...

                Code:
                If SQL_ErrorQuickAll<>"" Then          
                      Function = SQL_ErrorQuickAll    
                      Exit Function
                End If
                From the SQL Tools Help File (emphasis added):

                SQL_ErrorQuickAll

                Returns a string that contains all of the Error Messages in the SQL Tools Error Stack, and clears the stack.
                ...so your SQL Tools error-handling isn't working as you probably intended.
                "Not my circus, not my monkeys."

                Comment


                • #48
                  Ah, you posted while I was composing. You effectively did a complete error check with that trace. Find "error" in the file...
                  "Not my circus, not my monkeys."

                  Comment


                  • #49
                    Originally posted by Eric Pearson View Post
                    I didn't notice this before...

                    Code:
                    If SQL_ErrorQuickAll<>"" Then
                    Function = SQL_ErrorQuickAll
                    Exit Function
                    End If
                    From the SQL Tools Help File (emphasis added):



                    ...so your SQL Tools error-handling isn't working as you probably intended.
                    That's in the function that opens the database.
                    I changed the calling function to
                    Code:
                        errmsg=openDatabase()                    
                        If errMsg<>"" Then goto errout
                    so at least I'll get an error.

                    Though I will say it always opens OK, it will run a few records and then crash.
                    I'll check sql.log for an error.
                    thanks

                    Comment


                    • #50
                      With SQL trace on, the program runs completely and there are no errors in the SQL.LOG file.

                      Comment


                      • #51
                        > the program runs completely

                        I'm not surprised, the trace is probably accomplishing the same thing as a SLEEP

                        > no errors in the SQL.LOG file.

                        Good, but that comes directly from the ODBC driver so there could still be a SQL-Tools-level error, so I'd definitely fix the QuickAll thing.
                        "Not my circus, not my monkeys."

                        Comment


                        • #52
                          update: it's worked every day since I added the SQL trace.
                          I've updated SQL_ErrorQuickAll error catch.
                          Good enough for now.
                          thanks!

                          Comment


                          • #53
                            This, once again, has reared it's ugly head. It will only run from the IDE. I'll post the entire code plus the trace file.

                            Code:
                            #Include "sqlt3.inc"
                            #Include "SQLT3ProDLL.INC"  
                            
                            %ticketsDB=1             
                            %nbsDB=2
                            $writePath="\\hahn\nbson\data\"              
                            $sq="'"
                            
                            Function PBMain()
                                Local sql As String   
                                Local errmsg As String         
                                Local userName As String      
                                Local wTower,wEmail As String          
                                Local counter As Long
                            
                                On Error GoTo errout
                            
                                SQL_Trace %TRACE_ODBC
                            
                              ' open and ititialize sql tools
                                errmsg=openDatabase()                     
                                If errMsg<>"" Then GoTo errout
                            
                              ' 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+="order by wireless.tower"      
                            
                                SQL_Statement(%nbsDB,1,%SQL_STMT_IMMEDIATE, sql)
                                errmsg = SQL_ErrorQuickAll
                                If errmsg <> "" Then GoTo errout    
                            
                                counter=0
                            
                                Print "working..."
                            
                                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)    
                            
                                   ' this dataflex data often has nulls and tabs: remove them
                                    Replace Chr$(0) With "" In wTower   
                                    Replace Chr$(0) With "" In wEmail                           
                                    Replace Chr$(9) With "" In wTower   
                                    Replace Chr$(9) With "" In wEmail           
                            
                                    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    
                            
                                    Incr counter
                            
                                    If counter=50 Then
                                        ' just trying something here
                                        sleep 1000
                                        counter=0
                                    End If
                            
                                  Else
                                    Exit Do
                                  End If
                                Loop  
                            
                                Print ""
                                Color 12,0
                                Print "done... quitting in 5 seconds"
                                Sleep 5000              
                            
                                SQL_Trace %TRACE_OFF
                            
                                sql_Shutdown          
                            
                                Sleep 1000
                                If IsFile( "C:\Users\shawn7\AppData\Local\Temp\sql.log" ) Then Kill "C:\Users\shawn7\AppData\Local\Temp\sql.log"
                            
                                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
                            the sql.log file is very large, so I'll just post the last few lines
                            Code:
                            towerEmails     1dc8-299c    ENTER SQLExecDirect 
                                    HSTMT               0x0279E590
                                    UCHAR *             0x0040296C [      81] "insert into tblTowerEmails (tower,email) values('ATKIN5GHZ','agftim@nbsmail.net')"
                                    SDWORD                    81
                            
                            towerEmails     1dc8-299c    EXIT  SQLExecDirect  with return code 0 (SQL_SUCCESS)
                                    HSTMT               0x0279E590
                                    UCHAR *             0x0040296C [      81] "insert into tblTowerEmails (tower,email) values('ATKIN5GHZ','agftim@nbsmail.net')"
                                    SDWORD                    81
                            
                            towerEmails     1dc8-299c    ENTER SQLNumResultCols 
                                    HSTMT               0x0279E590
                                    SWORD *             0x0018FBEC
                            
                            towerEmails     1dc8-299c    EXIT  SQLNumResultCols  with return code 0 (SQL_SUCCESS)
                                    HSTMT               0x0279E590
                                    SWORD *             0x0018FBEC (0)
                            
                            towerEmails     1dc8-299c    ENTER SQLFetchScroll 
                                    SQLHSTMT            0x005CE1B8
                                    SQLSMALLINT                  1 <SQL_FETCH_NEXT>
                                    SQLLEN                     0
                            
                            towerEmails     1dc8-299c    EXIT  SQLFetchScroll  with return code 0 (SQL_SUCCESS)
                                    SQLHSTMT            0x005CE1B8
                                    SQLSMALLINT                  1 <SQL_FETCH_NEXT>
                                    SQLLEN                     0
                            
                            towerEmails     1dc8-299c    ENTER SQLFreeStmt 
                                    HSTMT               0x0279E590
                                    UWORD                        0 <SQL_CLOSE>
                            
                            towerEmails     1dc8-299c    EXIT  SQLFreeStmt  with return code 0 (SQL_SUCCESS)
                                    HSTMT               0x0279E590
                                    UWORD                        0 <SQL_CLOSE>
                            
                            towerEmails     1dc8-299c    ENTER SQLFreeStmt 
                                    HSTMT               0x0279E590
                                    UWORD                        2 <SQL_UNBIND>
                            
                            towerEmails     1dc8-299c    EXIT  SQLFreeStmt  with return code 0 (SQL_SUCCESS)
                                    HSTMT               0x0279E590
                                    UWORD                        2 <SQL_UNBIND>
                            
                            towerEmails     1dc8-299c    ENTER SQLFreeStmt 
                                    HSTMT               0x0279E590
                                    UWORD                        3 <SQL_RESET_PARAMS>
                            
                            towerEmails     1dc8-299c    EXIT  SQLFreeStmt  with return code 0 (SQL_SUCCESS)
                                    HSTMT               0x0279E590
                                    UWORD                        3 <SQL_RESET_PARAMS>
                            
                            towerEmails     1dc8-299c    ENTER SQLFreeHandle 
                                    SQLSMALLINT                  3 <SQL_HANDLE_STMT>
                                    SQLHANDLE           0x0279E590
                            
                            towerEmails     1dc8-299c    EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
                                    SQLSMALLINT                  3 <SQL_HANDLE_STMT>
                                    SQLHANDLE           0x0279E590
                            
                            towerEmails     1dc8-299c    ENTER SQLAllocHandle 
                                    SQLSMALLINT                  3 <SQL_HANDLE_STMT>
                                    SQLHANDLE           0x005C4068
                                    SQLHANDLE *         0x0018FCB0
                            
                            towerEmails     1dc8-299c    EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
                                    SQLSMALLINT                  3 <SQL_HANDLE_STMT>
                                    SQLHANDLE           0x005C4068
                                    SQLHANDLE *         0x0018FCB0 ( 0x0279E590)
                            
                            towerEmails     1dc8-299c    ENTER SQLSetStmtAttr 
                                    SQLHSTMT            0x0279E590
                                    SQLINTEGER                   6 <SQL_ATTR_CURSOR_TYPE>
                                    SQLPOINTER                 3 <SQL_CURSOR_STATIC>
                                    SQLINTEGER                   4 
                            
                            towerEmails     1dc8-299c    EXIT  SQLSetStmtAttr  with return code 0 (SQL_SUCCESS)
                                    SQLHSTMT            0x0279E590
                                    SQLINTEGER                   6 <SQL_ATTR_CURSOR_TYPE>
                                    SQLPOINTER                 3 <SQL_CURSOR_STATIC>
                                    SQLINTEGER                   4 
                            
                            towerEmails     1dc8-299c    ENTER SQLExecDirect 
                                    HSTMT               0x0279E590
                                    UCHAR *             0x0040296C [      95] "insert into tblTowerEmails (tower,email) values('ATKIN5GHZ','KaneMichael.Causemaker@jbssa.com')"
                                    SDWORD                    95

                            Comment


                            • #54
                              1. Curious, if you tried creating shortcuts to the batch file with and without admin privileges?
                              2. Did you create a manifest?

                              Comment


                              • #55
                                >>1. Curious, if you tried creating shortcuts to the batch file with and without admin privileges?<<
                                Yes.
                                I can open an "administrator" command prompt and run the EXE and it will crash.
                                It ONLY works consistently from the IDE.

                                >>2. Did you create a manifest?<<
                                I thought a manifest was used to embed files into an EXE? How would that apply here?

                                I've also tried running it with no output to the screen, adding sleep statements, etc.

                                Comment


                                • #56
                                  > I thought a manifest was used to embed files into an EXE? How would that apply here?
                                  It might prevent a virus checker from issuing a false positive.

                                  Running out of environment space?
                                  Might try compiling with another name in case something wrong in registry.
                                  Last edited by Mike Doty; 11 Aug 2017, 05:09 PM.

                                  Comment


                                  • #57
                                    Shawn, first take a look at the "Detecting the End Of Data" and following "Error Handling..." sections of your SQL Tools help. You might want to add error checking to your fetch and EOD. If you get an error on the fetch, the Result() calls would blow up.

                                    Like this (from documentation examples) ...

                                    Code:
                                    DO
                                        SQL_Fetch %NEXT_ROW
                                        IF SQL_EOD THEN EXIT LOOP
                                        IF SQL_ErrorPending THEN
                                            'Check the error type,
                                            'and exit if necessary.
                                        END IF
                                        'process a row of data here
                                    LOOP
                                    Or this ...

                                    Code:
                                    DO
                                        IF SQL_Fetch(%NEXT_ROW) <> %SQL_SUCCESS THEN
                                            EXIT LOOP
                                        END IF
                                        'process a row of data here
                                    LOOP

                                    I would add additional error checking after each of your Result() calls. If you get an error on the Result() calls, the INSERT would blow up ...

                                    Code:
                                    wTower=sql_resultColumnString(%nbsDB,1,1)
                                    wEmail=sql_resultColumnString(%nbsDB,1,2)
                                    Per the documentation ...

                                    "These functions do not return Error Codes »p180 because they return string values.
                                    They can, however, generate ODBC Error Messages »p181 and SQL Tools Error
                                    Messages."

                                    I would also write your wTower and wEmail strings out to a file inside your fetch loop. You might need to open/close the file for every write to make sure you get all occurrences before the app craps and dies. Then look them over with a hex viewer to see if there are any other unwanted characters in those strings - other than the nulls and tabs that you already know about.

                                    I don't know that any of this will make a difference ... but it's good coding and debugging technique that might catch something.

                                    Comment


                                    • #58
                                      Application Manifests (MSDN)

                                      I thought a manifest was used to embed files into an EXE?
                                      Think again after reading.
                                      Michael Mattias
                                      Tal Systems Inc.
                                      Racine WI USA
                                      mmattias@talsystems.com
                                      http://www.talsystems.com

                                      Comment


                                      • #59
                                        I added more error checking, but the same issue.
                                        What is different about running in the IDE as opposed from the command prompt?

                                        Code:
                                            Do      
                                        
                                              result= SQL_FetchResult(%nbsDB,1,%NEXT_ROW)      
                                        
                                                  If SQL_EndOfData(%nbsDB,1) Then Exit Loop       
                                        
                                                  If result <> %SQL_SUCCESS Then 
                                                    errMsg= "success error "+Format$(result,"0")
                                                    Exit Loop 
                                                  End If  
                                        
                                                  If SQL_ErrorPending Then
                                                        errmsg = SQL_ErrorQuickAll
                                                        If errmsg <> "" Then GoTo errout 
                                                  End If     
                                        
                                                  wTower=Trim$(sql_resultColumnString(%nbsDB,1,1))
                                                  wEmail=Trim$(sql_resultColumnString(%nbsDB,1,2))
                                                  errmsg = SQL_ErrorQuickAll
                                                  If errmsg <> "" Then GoTo errout             
                                        
                                        
                                                  Print wEmail
                                        
                                                  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  
                                        
                                            Loop

                                        Comment


                                        • #60
                                          I should also note that I have 2 or 3 other batch programs schedule to run every day, from and to the same databases. They all run with no issue, except this one.

                                          Comment

                                          Working...
                                          X