No announcement yet.

SQL syntax

  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL syntax

    Hi all, I have VBScript down bellow which send SQL command to database.
    But I get error about syntax. I think problem will be with "apostrophe" in line with sql command.
    Any idea?
    Best regards

    Set oRS = oConn.Execute( sSQL )
    While Not oRS.EOF And Not oRS.BOF
    out= vbcrlf & oRS.Fields(0).Value

    error:0 'SQL0104: Token ; was not valid. Valid tokens: <END-OF-STATEMENT>.
    Cause . . . . . : A syntax error was detected at token ;. Token ; is not a valid token. A partial list of valid tokens is <END-OF-STATEMENT>. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token ;. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.'
    Last edited by Rems; 5th June 2012, 12:14.

  • #2
    Re: SQL syntax

    It is a database error, not a vbs error. The script interpreter does not seem to have issues with the entered string and the quote signs embedded.
    In a script any quotes/apostrophes should never be "curly" quotes - so the "straight" quote signs that are used in the part of the script you showed seems fine. Also, there are no double-quote signs embedded that needs to be escaped either in the string.

    I don't know so much about Database sql commands. But the error message that you posted stated that the semicolon should not be there where it is. Have you tried leaving the semicolon out of the call?

    Last edited by Rems; 5th June 2012, 13:22.

    This posting is provided "AS IS" with no warranties, and confers no rights.


    ** Remember to give credit where credit's due **
    and leave Reputation Points for meaningful posts


    • #3
      Re: SQL syntax

      Hi, thanks for reply.
      I remove semicolon and get this error:

      Error:0 'SQL0469: IN, OUT, or INOUT not valid for parameter 2 in procedure RUN_PROBE in INS.
      Cause . . . . . : The IN, INOUT, or OUT attribute specified for parameter 2 when the procedure was defined is not valid. The parameter name is *N. One of the following errors occurred: -- The attribute is not consistent with the parameter on the CALL statement. If the parameter was declared INOUT or OUT, the parameter on the CALL statement must be specified as a host variable. -- The attribute was specified as INOUT or OUT and REXX was specified as the language. The attribute must be IN if REXX is specified. Recovery . . . : Either change the attribute of the parameter on the DECLARE PROCEDURE, CREATE PROCEDURE, or ALTER PROCEDURE statement or change the parameter. Try the request again.'


      • #4
        Re: SQL syntax

        A asked admin of database to run command directly and this work. But from my vbscribt it not work.


        • #5
          Re: SQL syntax

          I try update script, but still not work... Maybe it needs different driver for IBM AS400? Any Idea? Thanks

          set cn = CreateObject("ADODB.Connection")
          set cmd = CreateObject("ADODB.Command")
          cn.Open "Provider=IBMDA400;Data Source=IP;User Id=login;Password=pass;Default Collection=INS;"
          Set cmd.ActiveConnection = cn
          cmd.CommandText = "CALL INS.RUN_PROBE('RULELESS_EVENTS','RESULT');"
          cmd.CommandType = 4
          ' Ask the server about the parameters for this scalar function
          ' Assign a value to the 2nd parameter.
          ' because this scalar function only has one parameter
          ' in this case, it is an ip address
          ' so, its only parameter is varchar data type
          ' Index of parameter 0 represents return value.
          cmd(1) = wscript.arguments(0)
          wscript.echo "output: " & cmd(0)