InTouch 8 Stored Procedure execution

Need help. Intouch 8.0 I am attempting to poke info into a database using using connect and set statements, the amount of data I am attempting to move is much larger than 131 characters and I am attempting to use the append statement to complete the data write. It appears if I use more the three SQLAppendStatements I receive a syntax error in my database. Is there anyway around this limitation? When it hits the database, it is making 2 calls and I have no clue why the second call is being made. The database gets 'EXEC prcInsertData c1,c2,c3...' followed by a 'SELECT * FROM EXEC prcInsertData c1,c2,c3...'. Of course the second statement is invalid. Why does it send 2 calls calls?

Please post the InTouch script(s) you are trying to run, including all SQL statements and maybe we can help.

Scripts using SQLAppendStatement

DataCollectionZero = 0;

TotalWeight25-Added = sRMS25-TotalAutoAdded + TotalDrumDump25-SP;

{Create Database Connection}

IF DBConnection_SR_Batch == 0 THEN
DBResultCodeConn_SR_Batch = SQLConnect( DBConnID_SR_Batch, "DSN=dsn;SRVR=sever;DB=database" );
ENDIF;

IF DBConnection_SR_Batch == 0 THEN
DBResultCodeConn_SR_Batch = SQLConnect( DBConnID_SR_Batch, "DSN=dsn;SRVR=server;DB=database;UID=user; PWD=password" );
ENDIF;

IF DBResultCodeConn_SR_Batch == 0 THEN
DBConnection_SR_Batch = 1; {Database is connected}
TestConnect = 1;
ELSE
DBConnection_SR_Batch = 0; {Database is NOT connected}
ENDIF;


DIM SQLString AS MESSAGE;
DIM SQLString2 AS MESSAGE;
DIM SQLString3 AS MESSAGE;
DIM SQLString4 AS MESSAGE;
DIM SQLString5 AS MESSAGE;
DIM SQLString6 AS MESSAGE;
DIM SQLString7 AS MESSAGE;


SQLString = "EXEC prc_RunData_Insert "+"'"+HMI_ID+"'" +","
+"'"+StringFromReal(TotalWeight25-Added,1,"f")+"'"+","
+"'"+StringFromReal(TotalWeight25-LC,1,"f")+"'"+","
+"'"+StringFromReal(CondimentWeight25-LC,1,"f")+"'"+","
+"'"+StringFromReal(PressureFinal25,1,"f")+"'"+","
+"'"+StringFromReal(FinalTemperature25,1,"f")+"'"+","
+"'"+StringFromReal(FinalConductivity25,1,"f")+"'"+","
+"'"+StringFromReal(FBFinalPressureLong25,1,"f")+"'"+","
+"'"+StringFromReal(FBFinalPressureShort25,1,"f")+"'"+","
+"'"+StringFromReal(FBFinalDensity25,1,"f")+"'"+","
+"'"+StringFromReal(FBFinalWeight25,1,"f")+"'";


SQLString2 =
"'"+StringFromReal(sRMSCH2O25-TotalAutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSCH2O25-TotalTopOffAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSCH2O25-TotalHoseAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSCH2O25-TotalAmountAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSHH2O25-TotalAutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSHH2O25-TotalTopOffAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSHH2O25-TotalHoseAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSHH2O25-TotalAmountAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSChillH2O25-TotalAutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSChillH2O25-TotalTopOffAdded,1,"f")+"'";

SQLString3 =
"'"+StringFromReal(sRMSChillH2O25-TotalHoseAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSChillH2O25-TotalAmountAdded,1,"f")+"'"+","
+"'"+StringFromReal(TotalSpargeStm25-AutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSCS25-TotalAutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSCS25-TotalAmountAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSTP25-TotalAutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSTP25-TotalAmountAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSBr25-TotalAutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSBr25-TotalAmountAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSEmul25-TotalAutoAdded,1,"f")+"'";

SQLString4 =
"'"+StringFromReal(sRMSEmul25-TotalAmountAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSCond25-TotalAutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSCond25-TotalAmountAdded,1,"f")+"'"+","
+"'"+StringFromReal(CondDSI25-LastDensity,1,"f")+"'"+","
+"'"+StringFromReal(CondDSI25-AvgDensity,1,"f")+"'"+","
+"'"+StringFromReal(CondDSI25-DensityStdv,1,"f")+"'"+","
+"'"+StringFromReal(CondDSI25-DensityCount,1,"f")+"'"+","
+"'"+StringFromReal(CondDSI25-TotalSteam,1,"f")+"'"+","
+"'"+StringFromReal(sRMSThick25-TotalAutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSThick25-TotalAmountAdded,1,"f")+"'";

SQLString5 =
"'"+StringFromReal(sRMSBS25-TotalAutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSBS25-TotalAmountAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSCM125-TotalAutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSCM125-TotalAmountAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSPS25-TotalAutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSPS25-TotalAmountAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSCornSyr25-TotalAutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSCornSyr25-TotalAmountAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSFruct25-TotalAutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSFruct25-TotalAmountAdded,1,"f")+"'";

SQLString6 =
"'"+StringFromReal(sRMSBroth25-TotalAutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSBroth25-TotalAmountAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSTS25-TotalAutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSTS25-TotalAmountAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSButter25-TotalAutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSButter25-TotalAmountAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSCheese25-TotalAutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSCheese25-TotalAmountAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSCream25-TotalAutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSCream25-TotalAmountAdded,1,"f")+"'";

SQLString7 =
"'"+StringFromReal(sRMSV725-TotalAutoAdded,1,"f")+"'"+","
+"'"+StringFromReal(sRMSV725-TotalAmountAdded,1,"f")+"'";



DBResultCodeGen_SR_Batch = SQLSetStatement (DBConnID_SR_Batch, SQLString);
DBResultCodeGen_SR_Batch = SQLAppendStatement (DBConnID_SR_Batch, SQLString2);
DBResultCodeGen_SR_Batch = SQLAppendStatement (DBConnID_SR_Batch, SQLString3);
DBResultCodeGen_SR_Batch = SQLAppendStatement (DBConnID_SR_Batch, SQLString4);
DBResultCodeGen_SR_Batch = SQLAppendStatement (DBConnID_SR_Batch, SQLString5);
DBResultCodeGen_SR_Batch = SQLAppendStatement (DBConnID_SR_Batch, SQLString6);
DBResultCodeGen_SR_Batch = SQLAppendStatement (DBConnID_SR_Batch, SQLString7);
DBResultCodeGen_SR_Batch = SQLExecute( DBConnID_SR_Batch, "BatchCompleteCapture", 0 );
DBResultCodeGen_SR_Batch =SQLEnd(DBConnID_SR_Batch);


{Close Database Connection}
DBResultCodeConn_SR_Batch = SQLDisconnect(DBConnID_SR_Batch);

IF DBResultCodeConn_SR_Batch == 0 OR DBResultCodeConn_SR_Batch == -1002 THEN
DBConnection_SR_Batch = 0; {Database is Disconnected}
ENDIF;

Is there any special magic going on in your prc_RunData_Insert stored procedure other than just inserting the data into a table? If not, have you considered just creating a bind list and using a SQLInsert statement?

Stored Procedure

Actually the procedure does a few look ups and creates an ID field then does the insert. It adds about 5 values in addition to the parameters passed. We were wanting to understand why the SQLAppendStatement causes InTouch to make a second call to the database, prefixing the SQLstring with 'SELECT * FROM '. Maybe this is a bug or something.

On a hunch, try changing your SQLExecute statement to:

DBResultCodeGen_SR_Batch = SQLExecute( DBConnID_SR_Batch, 0 );

You'll notice that the bind list parameter is gone, which may have been triggering the second "SELECT * FROM....". Since you are passing all of your parameters in the SQL string, was there a reason you had a bind list parameter?

"Cannot mix another type with string"
Is the error generated when we remove the bindlist and try to validate.

Also we have another InTouch script which does the same thing and executes a different stored procedure. It uses only 2 SQLAppendStatements and the other SQLExecute, etc are the same. It strangely does not add the second call to the database.

Sorry....try an empty string in place of the bind list:

DBResultCodeGen_SR_Batch = SQLExecute( DBConnID_SR_Batch,"", 0 );

No go. We still see 2 calls in the SQL Trace. The ' Exec prcInsertData...' followed by a 'SELECT * FROM EXEC prcInsertData....'

I just noticed that you are reporting that your error refers to a procedure called "prcInsertData", yet the call in your SQL statement is to procedure "prc_RunData_Insert".....

Are you sure these errors relate to the same procedure? Where is the "prcInsertData" procedure being called from?

I was just stating an example of the errors in my post. They were not the actual errors from the SQL profiler Trace screen. Sorry for any confusion. The procedure names actaully match. We are still seeing two database calls being made for no apparent reason when one InTouch script is being executed as opposed to the other.

Would it be possible for you to post your stored procedure? I'm starting to wonder if the SELECT * is an implied or triggered InTouch operation if you have output parameters specified in your procedure. If this is the case, you may need to somehow split the operation you are trying to perform into 2 operations, or add the bind list parameter back into the SQLExecute making sure that it matches with the output parameters of the procedure.

Stored Procedure

I am new to using InTouch, but not new to databases. Been a DBA for many years, and am pretty sure its not a problem with the proc. Here is the procedure simplified to <10000 characters post limitation:

CREATE PROC [dbo].[prc_RunData_Insert]
@HMI_ID varchar(15),
@RecipeMOA varchar(15),
@TotalWeightSP numeric(6, 1)=NULL,
@PressureConstant numeric(4, 2)=NULL,
@DrumDumpSP numeric(6, 1)=NULL,
@ColdWaterAutoSP numeric(6, 1)=NULL,
@ColdWaterTopoffSP numeric(6, 1)=NULL,
@ColdWaterHoseSP numeric(6, 1)=NULL,
@HotWaterAutoSP numeric(6, 1)=NULL,
@HotWaterTopoffSP numeric(6, 1)=NULL,
@HotWaterHoseSP numeric(6, 1)=NULL,
@ChilledWaterAutoSP numeric(6, 1)=NULL,
@ChilledWaterTopoffSP numeric(6, 1)=NULL,
@ChilledWaterHoseSP numeric(6, 1)=NULL,
@ChickenStockAutoSP numeric(6, 1)=NULL,
@PasteAutoSP numeric(6, 1)=NULL,
@BrineAutoSP numeric(6, 1)=NULL,
@EmulsionAutoSP numeric(6, 1)=NULL,
@CondimentAutoSP numeric(6, 1)=NULL,
@ThickenerAutoSP numeric(6, 1)=NULL,
@BoneStockAutoSP numeric(6, 1)=NULL,
@CMAutoSP numeric(6, 1)=NULL,
@PoultryStockAutoSP numeric(6, 1)=NULL,
@CornSyrupAutoSP numeric(6, 1)=NULL,
@FructoseAutoSP numeric(6, 1)=NULL,
@BrothAutoSP numeric(6, 1)=NULL,
@TurkeyStockAutoSP numeric(6, 1)=NULL,
@ButterAutoSP numeric(6, 1)=NULL,
@CheeseAutoSP numeric(6, 1)=NULL,
@CreamAutoSP numeric(6, 1)=NULL,
@V7AutoSP numeric(6,1)=NULL

AS
SET NOCOUNT ON

DECLARE @ProcessOrderNo VARCHAR(12),
@RunID VARCHAR(14),
@SystemID VARCHAR(7),
@LineID VARCHAR(5)

--Future use
SET @ProcessOrderNo =''

--Set variables for HMI Test
SELECT @ProcessOrderNo ='Lookup',
@RunID =ISNULL(MAX(RunID), 0)+ 1 ,
@SystemID ='Lookup',
@LineID ='LookUp'
FROM RunData



INSERT RunData(ProcessOrderNo, RunID, SystemID, LineID, RecipeMOA, StartDateTime, EndDateTime, TotalWeightSP,...)



VALUES(@ProcessOrderNo, @RunID, @SystemID, @LineID, @RecipeMOA,GETDATE(),NULL, @TotalWeightSP, ...)



SET NOCOUNT OFF

I hope it wasn't this simple..........

Looking back at your original InTouch SQL statement, I just noticed you have 2 SQLConnect statements:

{Create Database Connection}

IF DBConnection_SR_Batch == 0 THEN
DBResultCodeConn_SR_Batch = SQLConnect( DBConnID_SR_Batch, "DSN=dsn;SRVR=sever;DB=database" );
ENDIF;

IF DBConnection_SR_Batch == 0 THEN
DBResultCodeConn_SR_Batch = SQLConnect( DBConnID_SR_Batch, "DSN=dsn;SRVR=server;DB=database;UID=user; PWD=password" );
ENDIF;

Click to expand...

Stored Procedure

The two connections were meant to try the connection again if not successful the first time. Both scripts do this, yet only one is having the issue descibed. We have actually created a new script which is exactly like the old and it is not making the second connecting to the database server. Thanks for the help.