ODBC drive error for Oracle database

[charan]

Hi,

I want see the flow how to fetch data from Oracle to TM1 and how to load data from TM1 to back to Oracle [ Oracle->TM1 and TM1->Oracle]

I have tried to load data into dept table in oracle from TM1. The procedure I have tried is,

1) Established ODBC driver.

2) In TI selected odbc source, datasource is orcl, given user name and password and written query is select * from dept. I got data with deptno,dname,loc.

3) Mapped this to dimension with the the same names and created cube with this dimensions as Oracle.

4) Entered data in the cube and saved as default view.

5) Again in TI selected source as TM1 cube view and followed the procedure and written in Metadata tab as shown in the below figure and got error as shown in it.

Please guide me with suggestion and ideas for my requirement.

Thanks and regards

TM1 9.5.2
excel 2007

[qml]

Please guide me with suggestion and ideas for my requirement.

Hint: what is the SQL command to insert a row into a table? You need to submit a valid SQL statement there, that's it. Also, make sure you use ODBCOpen() and ODBCClose() in your Prolog and Epilog, respectively.

[charan]

Hi I am trying to load TM1 data into Oracle database.

My requirement is to load TM1 data into Oracle database. I have tried with the functions ODBCOpen in prolog tab, ODBCOutput in Data tab and ODBCclose in epilog tab.
Please find the attachment for the flow what I have tried. My doubt here is what is the source I should take in loading data from TM1 to oracle, I have to select source as orcl or cube view. please guide me.

TM1 9.5.2
excel 2007

[charan]

sorry here is the attachment

[David Usherwood]

Okay....
Since you are _reading_ data from TM1 and _writing_ it to Oracle, your data _source_ needs to be a TM1 view.

And your insert query has quotes (') inside quotes - you need to 'escape' the quotes ie to get ' in the output, write ''' in the script. (This isn't especially a TI issue, many other scripting environments do this)

And finally, to validate your SQL command line, the best way is to replace ODBCOUTPUT(orcl, by ASCIIOUTPUT('test.txt',
and inspect/test the generated command against your relational engine - this should sort out any issues.

[qml]

Charan, why can't you continue asking questions on this subject in your original thread? There really is no reason to multiply threads like that. It's for your own benefit - you will not have to explain your circumstances all over again.

Edit: thanks to Admins for merging both threads.

[charan]

sure qml, hereafter i will do that,

Hi Now I tried the same thing but now Iam getting syntax error as shown in the document please refer it.

[tomok]

sure qml, hereafter i will do that,

Hi Now I tried the same thing but now Iam getting syntax error as shown in the document please refer it.

That's because you have to use special handling for apostrophes when creating an SQL statement in a TI as the apostrophe is also a text delimiter for TM1. See this http://www.tm1forum.com/viewtopic.php?f=3&t=830.

[charan]

Hi Tomok,
Thank you, but still I am facing an error.
My exact requirement is, I want load a row/column in the dept table of oracle database. Thats what I am trying please correct me if anything is wrong.
I tried with your suggestion but I am facing an error, please find the attachment for the comment of the error.

[charan]

Hi Tomok,
Even if use four quotes like ''''|Manufacturing|'''' still I face the same error.

[Wim Gielis]

Please use the function ASCIIOUTPUT or TEXTOUTPUT to investigate your exact sQL query.
Obviously you are having problems with ' and " characters.

[charan]

Hi Wim,

I did not get you exactly, what you are suggesting me, I tried with Textoutput function and getting all the odbc cube data in CSV file exactly, but I did not get how to put in ODBCOutput function. Help me in this.

Thank you

[Michel Zijlema]

Hi Wim,

I did not get you exactly, what you are suggesting me, I tried with Textoutput function and getting all the odbc cube data in CSV file exactly, but I did not get how to put in ODBCOutput function. Help me in this.

Thank you

What Wim meant is that you can write the generated query text to a text file so that you can see what the generated query statement looks like. But, as shown in the screenshot above, you can also see this in the generated error message - and as you can read from there the generated code is far from correct SQL...
When you have trouble with the quote character you could use a variable for this and use this variable on the places where you would like to see the single quote in the generated SQL statement. This way your statement should look something like this:

Code: Select all

v_qt = Char(39);
ODBCOutput('orcl', 'Insert Into dept (DEPTNO, DNAME, LOC) values (50, ' | v_qt | 'Manufacturing' | v_qt | ', ' | v_qt | 'SiliconValley' | v_qt | ')');

Michel

[charan]

Hi Michel,
Thank you, but still error is the same.

[Michel Zijlema]

Hi Michel,
Thank you, but still error is the same.

The SQL looks better now - maybe you could add a space after the commas in (DEPTNO, DNAME, LOC).
Perhaps the dept table needs to have a schema identifier. And is the DEPTNO column indeed a numeric type column and the other 2 (var)char type?

Michel

[charan]

Hi Michel,

Thank you, I have used only '' value'' this is the syntax I used after Values in the statement and row updated in emp table but the issue is it has updated many times nearly 100 times. I have ran TI only 5 times.
Any suggestion for why the same row updated many times.

[tomok]

How many cube intersections are in the view you are using as a data source for this process. You are going to get a new row in the database for each intersection every time you run the process. The INSERT command does not look to see if you already have a row in the table for that intersection, it creates a new row every time.

[charan]

Hi Tomok,

Thank you, but what is the syntax to load cube view data(for example we have 20 rows of data in Cube view)into oracle at a time.

Intially what I was trying is updating only one row.

[tomok]

but what is the syntax to load cube view data(for example we have 20 rows of data in Cube view)into oracle at a time.

It doesn't exist. Using a cube view as a data source in a TI process will create one record for each intersection in the cube. If your view is configured to suppress zeroes then there will be one record for each non-zero intersection. Putting the INSERT code in the data tab will mean a single INSERT command will be executed for each record. This is exactly what you should want.

[charan]

Hi Tomok,
please find the attachment what I am trying to do. Image is the table I created for loading cube view data. I want all cube view data into the table.
please suggest my syntax where I am getting error after Values statement.

Thanking you