LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only

  • 1.  SP_Help Or Table Schema Information

    Employee
    Posted 09-29-2015 08:44

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Brad Brezinski

    Hello,

    We've run a DB query node with:

    SELECT Top 100 *
    FROM EXACT.EXACT_SEG2

    We are getting:
    Error Code: brain.node.JJulianCalendar.cpp.425

    I think it's because one of the columns is in a format that we need to manage within the query. I don't know all the column names. The query will run if I replace star with a column name that I know isn't related to any date. We can't see the column in the graph until it runs. Is there a way to get table scheme and sp_help type information using BRE?

    Thanks,
    Brad


  • 2.  RE: SP_Help Or Table Schema Information

    Employee
    Posted 09-29-2015 08:59

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: stonysmith

    The answer here depends upon the type of database you are connecting to.

    for MS_SQL server..
    select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE 
from INFORMATION_SCHEMA.COLUMNS IC
where TABLE_NAME = 'tablename'
    for Oracle...
    select at.owner,atc.TABLE_NAME,atc.COLUMN_NAME,atc.DATA_TYPE,atc.DATA_LENGTH,atc.DATA_PRECISION,atc.DATA_SCALE,atc.NULLABLE
from	sys.all_tables at
	left outer join sys.all_tab_columns atc
	 on at.table_name = atc.table_name


  • 3.  RE: SP_Help Or Table Schema Information

    Employee
    Posted 09-29-2015 09:46

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Brad Brezinski

    Thanks Stony,

    Correction

    We are not using and Oracle and it's also not a MySQL db, I tried this:

    select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
    from INFORMATION_SCHEMA.COLUMNS IC
    where TABLE_NAME = EXACT.EXACT_SEG2

    I got the error: INFORMATION_SCHEMA does not exit.
    brain.node.TDParcels.cpp.88

    Thanks, Hi5,

    Brad


  • 4.  RE: SP_Help Or Table Schema Information

    Employee
    Posted 09-29-2015 09:58

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: stonysmith

    If it's oracle, start simply with
    select * from sys.all_tables


  • 5.  RE: SP_Help Or Table Schema Information

    Employee
    Posted 09-29-2015 10:21

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Brad Brezinski

    It is not an oracle. I had a typo in my last post.... I edited the post above. Thanks, Brad


  • 6.  RE: SP_Help Or Table Schema Information

    Employee
    Posted 09-29-2015 10:26

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: stonysmith

    To help understand what type of database.. what do you have in the DBDriver parameter? (I assume that you're using the JDBC node)


  • 7.  RE: SP_Help Or Table Schema Information

    Employee
    Posted 09-29-2015 10:27

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Brad Brezinski

    DbInterface: CLI


  • 8.  RE: SP_Help Or Table Schema Information

    Employee
    Posted 09-29-2015 10:35

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: stonysmith

    That's Teradata.. try this..
    select * from dbc.columns


  • 9.  RE: SP_Help Or Table Schema Information

    Employee
    Posted 09-29-2015 10:41

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Brad Brezinski

    That works for me. Thanks and Hi5 Stony! Brad