Resolving FactoryPMI runPrepStmt Stored Procedure Errors

Error Signatures and Their Causes

This failure shows up when a button's actionPerformed script loops a Table component and calls a SQL Server stored procedure once per qualifying row. There are two separate faults and one latent data fault. Each produces a different signature.

Why the Parenthesized EXEC Fails Through JDBC

T-SQL calls a procedure with a comma-separated argument list and no parentheses:

• EXEC proc 'a','b'
• or with named arguments: EXEC proc @x='a', @y='b'

EXEC ( ... ) with parentheses is a different construct. It executes a dynamic SQL string. A parenthesized argument list after a procedure name is therefore a syntax error.

A prepared statement does not send the ? markers to SQL Server as written. The SQL Server JDBC driver rewrites each marker into a named parameter, @P0, @P1, and so on. It then sends the batch with those parameter declarations. The parser fails on the first token inside the bad parentheses, which is the driver's first parameter name. That is the '@PO' in the error.

The token does not exist in the procedure body, and FactoryPMI does not inject it. The driver generates it. The same error appears on plain INSERT INTO prepared statements whenever the placeholder lands in an invalid position.

FactoryPMI sends the command as a query rather than as a T-SQL batch typed into Management Studio. SQL Server accepts EXEC sent that way. The execution path is not the fault; the syntax is.

The runPrepStmt Argument Contract

The fpmi.db.runPrepStmt argument rules:

• The query string is required.
• The parameter list is required.
• Only the datasource is optional.

That is why the traceback reports expected 2-3 args; got 1. Check the function reference for where the optional datasource argument goes before you add it.

When the statement has no ? markers, do not use a prepared statement. Use one of these instead:

• fpmi.db.runQuery when the statement returns a result set.
• fpmi.db.runUpdateQuery when it does not.

usp_PMIDepletionsInsert runs SET NOCOUNT ON, performs a single INSERT, and never selects rows. It returns no result set, so it belongs with runUpdateQuery or runPrepStmt, not runQuery.

Approach Comparison

Recommendation: use approach D, or E if the procedure signature is likely to change. Values typed into the table by operators should never be spliced into SQL text.

Keep approach C as the fallback. It is confirmed working, so use it if the prepared form misbehaves on your driver. When you do, escape apostrophes.

Procedure: Parameterized EXEC Per Row

1. Test the exact statement in Management Studio. Before anything else, confirm the procedure accepts the argument order you plan to bind. Use the same dbo. prefix and no parentheses. The reference call is EXEC dbo.usp_PMIDepletionsInsert '144','98','2009','3','PAL','5','2',''. Confirm that a row lands in tblDepletions_test before you touch the script.
2. Map the columns. The table columns map as follows:
   • Column 0 is Brand.
   • Column 3 is Packsizeid.
   • Column 4 is EndingInv.
   • Column 5 is Sales.
   The Distributor and Date components supply selectedValue. Print both once and confirm they are the database keys (for example 144 and 98), not display text.
3. Normalize every cell to a string before validating. isdigit() exists only on strings. A None cell or a numeric column type raises an exception before the call. Convert None to '', then apply str() and strip().
4. Bind all eight values in declaration order. The order is:
   1. @distid
   2. @Dateid
   3. @CurYear
   4. @CurMonth
   5. @Brand
   6. @Packsizeid
   7. @EndingInv
   8. @Sales
   All are declared as strings in the procedure, so pass strings.
5. Print the argument list on the first run. Watch the console. Do not move on until the printed values match the SSMS test values for the same row.

import time
localtime = time.localtime(time.time())
CurYear = str(localtime[0])
CurMonth = str(localtime[1])

table = event.source.parent.getComponent('Table')
data = table.data
Dateid = event.source.parent.getComponent('Date').selectedValue
Distid = event.source.parent.getComponent('Distributor').selectedValue

sql = "EXEC dbo.usp_PMIDepletionsInsert ?,?,?,?,?,?,?,?"
# Alternative (order-proof):
# sql = "EXEC dbo.usp_PMIDepletionsInsert @distid=?,@Dateid=?,@CurYear=?,@CurMonth=?,@Brand=?,@Packsizeid=?,@EndingInv=?,@Sales=?"

def clean(v):
    if v is None:
        return ''
    return str(v).strip()

for row in range(data.getRowCount()):
    Brand = clean(data.getValueAt(row, 0))
    Packsize = clean(data.getValueAt(row, 3))
    EndingInv = clean(data.getValueAt(row, 4))
    Sales = clean(data.getValueAt(row, 5))
    if EndingInv.isdigit() or Sales.isdigit():
        args = [str(Distid), str(Dateid), CurYear, CurMonth,
                Brand, Packsize, EndingInv, Sales]
        print args
        fpmi.db.runPrepStmt(sql, args)

isdigit() already returns a boolean, so the original <> 0 comparison adds nothing. Converting the dataset to a PyDataSet is optional here, because the loop reads through getValueAt.

Fallback: Concatenated EXEC With runUpdateQuery

This form is confirmed to insert correctly. Build the literal statement and hand it to fpmi.db.runUpdateQuery.

Double every apostrophe in cell values first. Otherwise a brand or text value containing ' terminates the literal early and raises a new syntax error.

def q(v):
    return "'" + clean(v).replace("'", "''") + "'"

strSQL = "EXEC dbo.usp_PMIDepletionsInsert " + ",".join(
    [q(Distid), q(Dateid), q(CurYear), q(CurMonth),
     q(Brand), q(Packsize), q(EndingInv), q(Sales)])
print strSQL
fpmi.db.runUpdateQuery(strSQL)

Copy the printed strSQL for the first row into Management Studio. Confirm it runs there as-is before trusting the loop.

Row Validation Pitfalls

@Brand is declared CHAR(3). Longer brand codes are truncated at bind time and then miss the product lookup. Keep brand codes at three characters or widen the parameter.

Verification

1. Clear the client console. Press the button with one row filled in. Confirm the printed argument list or statement and confirm that no traceback or Gateway error 301 appears.
2. Query the target table. Run tblDepletions_test filtered on AreaID, DATEID, FiscalYear, and AccountingPeriod. Confirm exactly one new row with the expected EndingInv, Sales, and a non-null ProdID. Do not rely on the update count returned by the call; SET NOCOUNT ON suppresses the row-count message.
3. Test a blank Sales cell. Fill a row with EndingInv only. Confirm it inserts with Sales = 0. This proves blanks travel as '', not NULL.
4. Test an apostrophe. Enter a value containing ' in a text column. Confirm the parameterized version runs cleanly; the unescaped concatenated version will fail.
5. Trace one call on the database side. Run a SQL Server trace while pressing the button. Confirm the received batch shows dbo.usp_PMIDepletionsInsert followed by the driver parameters without parentheses. Confirm the number of calls matches the number of rows that passed validation.

FAQ