SQL syntax

Home Forums Scripting Windows Script Host SQL syntax

This topic contains 4 replies, has 3 voices, and was last updated by Avatar jeff.schumacher 7 years, 3 months ago.

Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • Avatar
    h0ndzik
    Member
    #158927

    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


    sSQL = “CALL INS.RUN_PROBE(‘RULELESS_EVENTS’,’RESULT’);”
    Set oRS = oConn.Execute( sSQL )
    While Not oRS.EOF And Not oRS.BOF
    out= vbcrlf & oRS.Fields(0).Value
    oRS.MoveNext
    wend[/CODE]…

    [QUOTE]error:0 ‘SQL0104: Token ; was not valid. Valid tokens: .
    Cause . . . . . : A syntax error was detected at token ;. Token ; is not a valid token. A partial list of valid tokens is
    . 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 , correct the SQL statement because it does not end with a valid clause.'[/QUOTE][CODE]sSQL = “CALL INS.RUN_PROBE(‘RULELESS_EVENTS’,’RESULT’);”
    Set oRS = oConn.Execute( sSQL )
    While Not oRS.EOF And Not oRS.BOF
    out= vbcrlf & oRS.Fields(0).Value
    oRS.MoveNext
    wend[/CODE]…

    Quote:
    error:0 ‘SQL0104: Token ; was not valid. Valid tokens: .
    Cause . . . . . : A syntax error was detected at token ;. Token ; is not a valid token. A partial list of valid tokens is
    . 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 , correct the SQL statement because it does not end with a valid clause.’

    Rems
    Rems
    Moderator
    #228198

    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?

    /Rems

    #375375

    Re: SQL syntax

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

    Quote:
    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.’
    #375376

    Re: SQL syntax

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

    #375377

    Re: SQL syntax

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

    Quote:
    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
    cmd.Parameters.Refresh
    ‘ 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)
    cmd.execute
    wscript.echo “output: ” & cmd(0)
    cn.close()
Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic.