ODBCOutPut Command - MS Access 2007

[weimerskirch]

MS ACCESS OUTPUT – Note that it created the table based on parameters below

Prolog
# ODBC datasource called ‘tm1output’ DSN='tm1output';
UName='';
Pass='';
#Open DB
ODBCOpen(DSN,UName,Pass);
#Create table
ODBCOutput(DSN, 'Create table af ( Plan TEXT, Versions TEXT, FY TEXT, ALC TEXT, GLAC TEXT, vMeasures TEXT, val_num TEXT)' );

NOTE THAT THE OPEN AND CREATE THE TABLE WORKED.

Data
ODBCOutPut('tm1output', ('INSERT INTO af ( [Plan], [Versions], [FY], [ALC], [GLAC], [vMeasures], [val_num]) VALUES ( "%V1%", "%V2%", “%V3%", "%V4%", "%V5%", “%V6%”, %val_num% )' ) );

I get an error each time with the Insert into command – "Baseline","Plan","ALC","FY 09","MSD STD","Cost of Operations CY","550.",Data Source line (1) Error: Data procedure line (14): Error executing SQL query: "INSERT INTO af ( [Plan], [Versions], [FY], [ALC], [GLAC], [vMeasures], [val_num]) VALUES ( "%V1%", "%V2%", “%V3%", "%V4%", "%V5%", “%V6%”, %val_num% )"

It must be user error, any thoughts, suggestions? Thanks

[Steve Rowe]

Hi weimerskirch ,
Welcome to the forum! I think you are missing the Expand command from your statement. This will convert the %variables% into what you really want.

Expand
This is a TM1 TurboIntegrator function, valid only in TurboIntegrator processes.
This function "expands" TurboIntegrator variable names, enclosed in % signs, to their values at run time. If the variable name represents a string variable, the entire variable expression must be enclosed on quotes. For example, "%V1%".
A common use of the Expand function is to pass the value of TurboIntegrator variables to the ODBCOutput function. Refer to the example below for details.
Syntax
Expand(String);
Argument
String Any string that includes TurboIntegrator variable names enclosed in % signs.

Example
ODBCOutPut( 'TransData', Expand( 'INSERT INTO SALES ( MONTH, PRODUCT, SALES ) VALUES ( "%V0%", "%V1%",%V2% )' ) );
This example illustrates the use of the Expand function within the ODBCOutput function. The example inserts records into a relational table named Sales that consists of three columns: Month, Product, and Sales.
The Expand function converts the variables V0, V1, and V2 to their actual values within the view. Assuming that the first value in the view is 123.456, and is defined by the elements Jan and Widget
Expand( 'INSERT INTO SALES ( MONTH, PRODUCT, SALES ) VALUES ( "%V0%", "%V1%",%V2% )' )
becomes
'INSERTINTO SALES ( MONTH, PRODUCT, SALES ) VALUES ( Jan, Widget, 123.456 )'
at run time.

HTH

[kpk]

Hello,

This works for me:
sSQL=EXPAND('INSERT INTO %sTable% (year, month, cn,cc, value) VALUES (%year1%,%sPeriod%,%cost_number%,%cost_center%,%sValue%)');

Could you try it without "" signs?
And try to use the EXPAND() function. You can read about it in the reference guide.

Regards,

Peter