Mapping Oracle Table Definitions

[max.madsen]

Joyce,

First, read your emails, hahaha..

Second, look for DSGetJobInfo and DSGetLinkMetaData command..
I believe will help you..

from your secret friend "mineiro"..

[joycerecacho]

Hello my friend.

When I use the DSGetLinkMetaData function, it shows the following message:

Array 'DSGetLinkMetaData' never dimensioned.
1 Errors detected, No Object Code Produced.

Do you know what is happening?
Is it necessary to mention a library like: $INCLUDE DSINCLUDE JOBCONTROL.H ??

Best Regards,

[ray.wurlod]

It is necessary to include the header file JOBCONTROL.H because it is in that header file that the function definition exists.

This $INCLUDE compiler directive must precede any executable statement in the code.

[chulett]

DSGetLinkMetaData may be a new 8.x function that is not available in your 7.x version.

[joycerecacho]

Oh ... really?


Do you know if there is another one with this features available in 7.x version?
I mean, one that describes the Table definition?

Thank you.

Best regards,

[max.madsen]

DSGetLinkMetaData may be a new 8.x function that is not available in your 7.x version.

I do not really believe this..
See this document:
http://pt.scribd.com/doc/58012197/49185 ... pers-Guide#

Go to page 365, have DSGetLinkMetaData information.

Now, im confused..

Anyway, i dont have this version to verify..

[chulett]

DSGetLinkMetaData may be a new 8.x function that is not available in your 7.x version.

I do not really believe this..

Recognize the fact that I said may since I had no way to check.

Now that I'm home, I too can find it in my old 7.5.x documentation so it should indeed exist - but someone with access to an actual system needs to check. Open up the JOBCONTROL.H file and see if it is defined there. If not, perhaps check some of the other header files.

[ray.wurlod]

Of course there is, probably with a lot of Field() functions. The structure returned by this function is a dynamic array. How much of the information do you require, and in what format? From memory the fields are:
column names
key
SQL data type (enumerated)
precision
scale
nullable
Each field is a comma-delimited list.

[max.madsen]

Your problem is just separate the fields? To after read correctly?
First, Try put this dynamic array in sequencial file.
In next step, set the sequencial file stage ,put the maximum quantity possible of columns and set the option "missing columns action" to value "Map empty string"..

Well, i hope works! lol..

Anyway, i'm curious.
Why the value "9" in your logic?

[ray.wurlod]

DSGetLinkMetadata() is documented on page 96 of the DataStage Programmer's Guide. It returns a dynamic array. You don't actually have to count anything. Here's how I'd approach it.

Code: Select all

* assuming JOBCONTROL.H is included

Result = DSGetLinkMetadata(hJob, StageName, LinkName)

ColumNames = Result<1>
KeyColumns = Result<2>  ;* 1 = is key column
SQLType = Result<3>  ;* see ODBC.H
Precision = Result<4>
Scale = Result<5>
DisplayWidth = Result<6>
Nullable = Result<7>  ;* 1 = nullable, 0 = not nullable
Description = Result<8>
Derivation = Result<9>

* The following dynamic array is built from constants in ODBC.H
SQLTypes = ""
SQLTypes<1> = "Char"
SQLTypes<2> = "Numeric"
SQLTypes<3> = "Decimal"
SQLTypes<4> = "Integer"
SQLTypes<5> = "SmallInt"
SQLTypes<6> = "Float"
SQLTypes<7> = "Real"
SQLTypes<8> = "Double"
SQLTypes<9> = "Date"
SQLTypes<10> = "Time"
SQLTypes<11> = "Timestamp"
SQLTypes<12> = "VarChar"

ColNo = 0
KeyList = ""
Ans = "("

Loop
Remove ColumnName From ColumnNames Setting MoreColumns
While MoreColumns

   ColNo += 1
   ColumnSQLType = SQLTypes<SQLType<1,ColNo>>
   ColumnPrecision = Precision<1,ColNo>
   ColumnScale = Scale<1,ColNo>
   ColumnNullable = Nullable<1,ColNo>
   If KeyColumns<1,ColNo> Then KeyList<-1> = ColumnName

   Ans<-1> = ColumnName : " " : ColumnSQLType
   Begin Case
      Case ColumnSQLType = "Char" Or ColumnSQLType = "VarChar"
         Ans := " " : ColumnSQLType : "(" : ColumnPrecision : ")"
      Case ColumnSQLType = "Numeric" Or ColumnSQLType = "Decimal"
         Ans := " " : ColumnSQLType : "(" : ColumnPrecision : "," : ColumnScale : ")"
   End Case

   If ColumnNullable Then Ans := " NOT NULL" Else Ans := " NULL"

Repeat

If Len(KeyList)
Then
   Ans<-1> = ", PRIMARY KEY (" : Convert(@FM,",",KeyList) 
End
Ans := " )"

Ans[2,1] = " "

Convert @FM To "," In Ans

You can add the table name (which is not returned by DSGetLinkMetadata).

[ray.wurlod]

The Remove statement is perfectly OK. When there are $INCLUDE directives line counts are often slightly out. We need to track down which line contains a variable that is used on the right hand side of an assignment statement and which has not been previously defined. It should be within ten lines of the Remove statement. I can't see one in the code I provided; can you verify yours?

I'm not sure about the DSR_GETRTCONFIG.H header file: perhaps it relates to something else in your code. What does the compiler complain of if you don't include DSR_GETRTCONFIG.H ?

I would ordinarily use

Code: Select all

$INCLUDE UNIVERSE.INCLUDE ODBC.H

but I have not actually used any constants from that header file.