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.  JDBC Execute Node & Create Table

    Employee
    Posted 07-25-2012 05:56

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

    Originally posted by: dwong80

    We're having problems creating a table using the JDBC Execute Node in BRE 4.5.4.

    Error message:
    The identifier that starts with 'create table [ActiveCustWithDNBDetail] ( [Family] [varchar] (100) NULL, [BPID] [varchar] (10) NULL, [CLID] [varchar] (15) NULL, ' is too long. Maximum length is 128.


    The node is included below:

    node:JDBC_Execute
    bretype:core::JDBC Execute
    editor:sortkey=500fe547195d6a11
    prop:DbDriver=com.microsoft.sqlserver.jdbc.SQLServ erDriver
    prop:DbPassword=<passwordhere>
    prop:DbUrl=jdbc:sqlserver://TXMSDSQ010\ANALYTICS:5001;database=analytics
    prop:DbUser=LavaStormMA_sql
    prop:SqlQuery=<<EOX
    sqlNonSelect("create table [ActiveCustWithDNBDetail] ( [Family] [varchar] (100) NULL, [BPID] [varchar] (10) NULL, [CLID] [varchar] (15) NULL, [Biller] [varchar] (12) NULL, [BusinessUnit] [varchar] (15) NULL, [Division] [varchar] (30) NULL, [Channel] [varchar] (30) NULL, [CustomerSegment] [varchar] (30) NULL, [EmployeeSize] [varchar] (15) NULL, [RepID] [varchar] (10) NULL, [RepName] [varchar] (60) NULL, [Product0] [varchar] (25) NULL, [Product1] [varchar] (30) NULL, [Product2] [varchar] (30) NULL, [Product3] [varchar] (30) NULL, [RevPrevJan] [money] NULL, [Mrgn1PrevJan] [money] NULL, [Mrgn2PrevJan] [money] NULL, [Cost1PrevJan] [money] NULL, [Cost2PrevJan] [money] NULL, [RevPrevFeb] [money] NULL, [Mrgn1PrevFeb] [money] NULL, [Mrgn2PrevFeb] [money] NULL, [Cost1PrevFeb] [money] NULL, [Cost2PrevFeb] [money] NULL, [RevPrevMar] [money] NULL, [Mrgn1PrevMar] [money] NULL, [Mrgn2PrevMar] [money] NULL, [Cost1PrevMar] [money] NULL, [Cost2PrevMar] [money] NULL, [RevPrevApr] [money] NULL, [Mrgn1PrevApr] [money] NULL, [Mrgn2PrevApr] [money] NULL, [Cost1PrevApr] [money] NULL, [Cost2PrevApr] [money] NULL, [RevPrevMay] [money] NULL, [Mrgn1PrevMay] [money] NULL, [Mrgn2PrevMay] [money] NULL, [Cost1PrevMay] [money] NULL, [Cost2PrevMay] [money] NULL, [RevPrevJun] [money] NULL, [Mrgn1PrevJun] [money] NULL, [Mrgn2PrevJun] [money] NULL, [Cost1PrevJun] [money] NULL, [Cost2PrevJun] [money] NULL, [RevPrevJul] [money] NULL, [Mrgn1PrevJul] [money] NULL, [Mrgn2PrevJul] [money] NULL, [Cost1PrevJul] [money] NULL, [Cost2PrevJul] [money] NULL, [RevPrevAug] [money] NULL, [Mrgn1PrevAug] [money] NULL, [Mrgn2PrevAug] [money] NULL, [Cost1PrevAug] [money] NULL, [Cost2PrevAug] [money] NULL, [RevPrevSep] [money] NULL, [Mrgn1PrevSep] [money] NULL, [Mrgn2PrevSep] [money] NULL, [Cost1PrevSep] [money] NULL, [Cost2PrevSep] [money] NULL, [RevPrevOct] [money] NULL, [Mrgn1PrevOct] [money] NULL, [Mrgn2PrevOct] [money] NULL, [Cost1PrevOct] [money] NULL, [Cost2PrevOct] [money] NULL, [RevPrevNov] [money] NULL, [Mrgn1PrevNov] [money] NULL, [Mrgn2PrevNov] [money] NULL, [Cost1PrevNov] [money] NULL, [Cost2PrevNov] [money] NULL, [RevPrevDec] [money] NULL, [Mrgn1PrevDec] [money] NULL, [Mrgn2PrevDec] [money] NULL, [Cost1PrevDec] [money] NULL, [Cost2PrevDec] [money] NULL, [RevCurrJan] [money] NULL, [Mrgn1CurrJan] [money] NULL, [Mrgn2CurrJan] [money] NULL, [Cost1CurrJan] [money] NULL, [Cost2CurrJan] [money] NULL, [RevCurrFeb] [money] NULL, [Mrgn1CurrFeb] [money] NULL, [Mrgn2CurrFeb] [money] NULL, [Cost1CurrFeb] [money] NULL, [Cost2CurrFeb] [money] NULL, [RevCurrMar] [money] NULL, [Mrgn1CurrMar] [money] NULL, [Mrgn2CurrMar] [money] NULL, [Cost1CurrMar] [money] NULL, [Cost2CurrMar] [money] NULL, [RevCurrApr] [money] NULL, [Mrgn1CurrApr] [money] NULL, [Mrgn2CurrApr] [money] NULL, [Cost1CurrApr] [money] NULL, [Cost2CurrApr] [money] NULL, [RevCurrMay] [money] NULL, [Mrgn1CurrMay] [money] NULL, [Mrgn2CurrMay] [money] NULL, [Cost1CurrMay] [money] NULL, [Cost2CurrMay] [money] NULL, [RevCurrJun] [money] NULL, [Mrgn1CurrJun] [money] NULL, [Mrgn2CurrJun] [money] NULL, [Cost1CurrJun] [money] NULL, [Cost2CurrJun] [money] NULL, [RevCurrJul] [money] NULL, [Mrgn1CurrJul] [money] NULL, [Mrgn2CurrJul] [money] NULL, [Cost1CurrJul] [money] NULL, [Cost2CurrJul] [money] NULL, [RevCurrAug] [money] NULL, [Mrgn1CurrAug] [money] NULL, [Mrgn2CurrAug] [money] NULL, [Cost1CurrAug] [money] NULL, [Cost2CurrAug] [money] NULL, [RevCurrSep] [money] NULL, [Mrgn1CurrSep] [money] NULL, [Mrgn2CurrSep] [money] NULL, [Cost1CurrSep] [money] NULL, [Cost2CurrSep] [money] NULL, [RevCurrOct] [money] NULL, [Mrgn1CurrOct] [money] NULL, [Mrgn2CurrOct] [money] NULL, [Cost1CurrOct] [money] NULL, [Cost2CurrOct] [money] NULL, [RevCurrNov] [money] NULL, [Mrgn1CurrNov] [money] NULL, [Mrgn2CurrNov] [money] NULL, [Cost1CurrNov] [money] NULL, [Cost2CurrNov] [money] NULL, [RevCurrDec] [money] NULL, [Mrgn1CurrDec] [money] NULL, [Mrgn2CurrDec] [money] NULL, [Cost1CurrDec] [money] NULL, [Cost2CurrDec] [money] NULL, [HQCompanyID] [varchar] (255) NULL, [HQCompanyIDNum] [float] NULL, [HQCompanyNM] [varchar] (60) NULL, [HQAddress] [varchar] (30) NULL, [HQCity] [varchar] (30) NULL, [HQFSA] [varchar] (255) NULL, [HQPCode] [varchar] (7) NULL, [HQProvince] [varchar] (2) NULL, [HQProvinceNew] [varchar] (2) NULL, [NumOfLocations] [float] NULL, [NumOfPreferredLocations] [float] NULL, [PercentPreferredLocations] [float] NULL, [PercRangePreferredLocations] [varchar] (20) NULL, [TotalEmployeeSize] [varchar] (20) NULL, [EmpTNew] [float] NULL)")
    EOX
    editor:XY=890,500
    end:JDBC_Execute
    This statement executes as a DB Execute Node. However, we need to use JDBC Execute.

    Can you advise how/if this can be done? Or is there a node specifically for creating tables in the destination database?

    Thanks. Darren


  • 2.  RE: JDBC Execute Node & Create Table

    Employee
    Posted 07-25-2012 06:02

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

    Originally posted by: dwong80

    Ah, I think we figured out the issue. We removed the sqlNonSelect("") from the JDBC Execute statement and kept ONLY the actual commands to be sent to the database:

    create table [ActiveCustWithDNBDetail] ( [Family] [varchar] (100) NULL, [BPID] [varchar] (10) NULL, [CLID] [varchar] (15) NULL, [Biller] [varchar] (12) NULL, [BusinessUnit] [varchar] (15) NULL, [Division] [varchar] (30) NULL, [Channel] [varchar] (30) NULL, [CustomerSegment] [varchar] (30) NULL, [EmployeeSize] [varchar] (15) NULL, [RepID] [varchar] (10) NULL, [RepName] [varchar] (60) NULL, [Product0] [varchar] (25) NULL, [Product1] [varchar] (30) NULL, [Product2] [varchar] (30) NULL, [Product3] [varchar] (30) NULL, [RevPrevJan] [money] NULL, [Mrgn1PrevJan] [money] NULL, [Mrgn2PrevJan] [money] NULL, [Cost1PrevJan] [money] NULL, [Cost2PrevJan] [money] NULL, [RevPrevFeb] [money] NULL, [Mrgn1PrevFeb] [money] NULL, [Mrgn2PrevFeb] [money] NULL, [Cost1PrevFeb] [money] NULL, [Cost2PrevFeb] [money] NULL, [RevPrevMar] [money] NULL, [Mrgn1PrevMar] [money] NULL, [Mrgn2PrevMar] [money] NULL, [Cost1PrevMar] [money] NULL, [Cost2PrevMar] [money] NULL, [RevPrevApr] [money] NULL, [Mrgn1PrevApr] [money] NULL, [Mrgn2PrevApr] [money] NULL, [Cost1PrevApr] [money] NULL, [Cost2PrevApr] [money] NULL, [RevPrevMay] [money] NULL, [Mrgn1PrevMay] [money] NULL, [Mrgn2PrevMay] [money] NULL, [Cost1PrevMay] [money] NULL, [Cost2PrevMay] [money] NULL, [RevPrevJun] [money] NULL, [Mrgn1PrevJun] [money] NULL, [Mrgn2PrevJun] [money] NULL, [Cost1PrevJun] [money] NULL, [Cost2PrevJun] [money] NULL, [RevPrevJul] [money] NULL, [Mrgn1PrevJul] [money] NULL, [Mrgn2PrevJul] [money] NULL, [Cost1PrevJul] [money] NULL, [Cost2PrevJul] [money] NULL, [RevPrevAug] [money] NULL, [Mrgn1PrevAug] [money] NULL, [Mrgn2PrevAug] [money] NULL, [Cost1PrevAug] [money] NULL, [Cost2PrevAug] [money] NULL, [RevPrevSep] [money] NULL, [Mrgn1PrevSep] [money] NULL, [Mrgn2PrevSep] [money] NULL, [Cost1PrevSep] [money] NULL, [Cost2PrevSep] [money] NULL, [RevPrevOct] [money] NULL, [Mrgn1PrevOct] [money] NULL, [Mrgn2PrevOct] [money] NULL, [Cost1PrevOct] [money] NULL, [Cost2PrevOct] [money] NULL, [RevPrevNov] [money] NULL, [Mrgn1PrevNov] [money] NULL, [Mrgn2PrevNov] [money] NULL, [Cost1PrevNov] [money] NULL, [Cost2PrevNov] [money] NULL, [RevPrevDec] [money] NULL, [Mrgn1PrevDec] [money] NULL, [Mrgn2PrevDec] [money] NULL, [Cost1PrevDec] [money] NULL, [Cost2PrevDec] [money] NULL, [RevCurrJan] [money] NULL, [Mrgn1CurrJan] [money] NULL, [Mrgn2CurrJan] [money] NULL, [Cost1CurrJan] [money] NULL, [Cost2CurrJan] [money] NULL, [RevCurrFeb] [money] NULL, [Mrgn1CurrFeb] [money] NULL, [Mrgn2CurrFeb] [money] NULL, [Cost1CurrFeb] [money] NULL, [Cost2CurrFeb] [money] NULL, [RevCurrMar] [money] NULL, [Mrgn1CurrMar] [money] NULL, [Mrgn2CurrMar] [money] NULL, [Cost1CurrMar] [money] NULL, [Cost2CurrMar] [money] NULL, [RevCurrApr] [money] NULL, [Mrgn1CurrApr] [money] NULL, [Mrgn2CurrApr] [money] NULL, [Cost1CurrApr] [money] NULL, [Cost2CurrApr] [money] NULL, [RevCurrMay] [money] NULL, [Mrgn1CurrMay] [money] NULL, [Mrgn2CurrMay] [money] NULL, [Cost1CurrMay] [money] NULL, [Cost2CurrMay] [money] NULL, [RevCurrJun] [money] NULL, [Mrgn1CurrJun] [money] NULL, [Mrgn2CurrJun] [money] NULL, [Cost1CurrJun] [money] NULL, [Cost2CurrJun] [money] NULL, [RevCurrJul] [money] NULL, [Mrgn1CurrJul] [money] NULL, [Mrgn2CurrJul] [money] NULL, [Cost1CurrJul] [money] NULL, [Cost2CurrJul] [money] NULL, [RevCurrAug] [money] NULL, [Mrgn1CurrAug] [money] NULL, [Mrgn2CurrAug] [money] NULL, [Cost1CurrAug] [money] NULL, [Cost2CurrAug] [money] NULL, [RevCurrSep] [money] NULL, [Mrgn1CurrSep] [money] NULL, [Mrgn2CurrSep] [money] NULL, [Cost1CurrSep] [money] NULL, [Cost2CurrSep] [money] NULL, [RevCurrOct] [money] NULL, [Mrgn1CurrOct] [money] NULL, [Mrgn2CurrOct] [money] NULL, [Cost1CurrOct] [money] NULL, [Cost2CurrOct] [money] NULL, [RevCurrNov] [money] NULL, [Mrgn1CurrNov] [money] NULL, [Mrgn2CurrNov] [money] NULL, [Cost1CurrNov] [money] NULL, [Cost2CurrNov] [money] NULL, [RevCurrDec] [money] NULL, [Mrgn1CurrDec] [money] NULL, [Mrgn2CurrDec] [money] NULL, [Cost1CurrDec] [money] NULL, [Cost2CurrDec] [money] NULL, [HQCompanyID] [varchar] (255) NULL, [HQCompanyIDNum] [float] NULL, [HQCompanyNM] [varchar] (60) NULL, [HQAddress] [varchar] (30) NULL, [HQCity] [varchar] (30) NULL, [HQFSA] [varchar] (255) NULL, [HQPCode] [varchar] (7) NULL, [HQProvince] [varchar] (2) NULL, [HQProvinceNew] [varchar] (2) NULL, [NumOfLocations] [float] NULL, [NumOfPreferredLocations] [float] NULL, [PercentPreferredLocations] [float] NULL, [PercRangePreferredLocations] [varchar] (20) NULL, [TotalEmployeeSize] [varchar] (20) NULL, [EmpTNew] [float] NULL);