Jade Db To Mysql Migration

[Scott Thompson]

I am trying to migrate data from a jade database using ODBC (it is from a product called Greentree) but I keep getting errors. From writing a few SQL queries mysqlf I have noticed that all SQL keywords must be in capitals, e.g SELECT/FROM/GROUP BY etc. In the log SQLyog seems to use lowercase for the 'select' but capitals for the 'FROM' clause. How can I force uppercase for this? I have set a few options in the preferences which I thought may do this but no luck. Refer attached images.

Thanks.

Scott.

__________________________________________________________________________________________

SQLyog Job Agent Version 8.17 Copyright(c) Webyog Softworks Pvt. Ltd. All Rights Reserved.

Check C:Documents and SettingsScott Thompson.SCOTTApplication DataSQLyogsja.log for complete error details.

Job started at Thu Aug 26 10:25:25 2010

DBMS Information: JADE

Importing table schema: ARInvoice… Successful…

Importing table foreign keys: ARInvoice…

ERROR: 1070, Too many key parts specified; max 16 parts allowed

Table:ARInvoice

Sql: ALTER TABLE `ARInvoice` ADD FOREIGN KEY(`myARControl`,`myARInvoiceBatch`,`myOpeningBalance`,`myOutstandingPeriodSummary`,`myARPeriodSummary`,`myOverdueInterestPeriodSummary`,`myBatch`,`myBranch`,`myCurrency`,`myCustomer`,`myRWCustomer`,`myDeliveryAddress`,`myGenGLForcedBalanceTran`,`myGeneratedTaxJournal`,`myGenDebtorsControlJournal`,`mySourceModule`,`myModule`,`myOutputTaxPeriodSummary`,`myPeriodSummary`,`mySalesPerson`,`myStandingInvoice`,`myReceiptType`,`myRWSupplier`,`myHoldCode`,`myPaymentTerm`)REFERENCES `UTPaymentTerm`(`oid`,`oid`,`oid`,`oid`,`oid`,`oid`,`oid`,`oid`,`oid`,`oid`,`oid`,`oid`,`oid`,`oid`,`oid`,`oid`,`oid`,`oid`,`oid`,`oid`,`oid`,`oid`,`oid`,`oid`,`oid`)ON DELETE CASCADE ON UPDATE CASCADE

Check C:Documents and SettingsScott Thompson.SCOTTApplication DataSQLyogsja.log for complete error details.

Successful…

Importing table data: ARInvoice…

ERROR in SQL:

select “calApplyDetailAmount”,”canBeDeleted”,”canUseInARCreditCardPayment”,”checkForLabelPrintConversion”,”convertLCTransAmountToWords”,”convertTransAmountToWords”,”currencyRate”,”doLinesHaveAnyQty”,”documentDate”,”entryTimeStamp”,”entryUser”,”fcDiscount”,”fcHoldAmount”,”fcNetAmount”,”fcOutstandingAmount”,”fcTaxAmount”,”getCMAnalysisCode”,”getCalendar”,”getCompany”,”getDetail”,”getExchangeVariance”,”getExchangeVarianceAccount”,”getExchangeVarianceReserve”,”getFCApplyTotal”,”getFCOutstandingMinusHold”,”getFCRoundingJournal”,”getFCTotal”,”getFcHeldAmount”,”getFcOutstandingAmount”,”getFunctionalCreditCardDetail”,”getGLTotal”,”getJobCodeString”,”getLCApplyTotal”,”getLCTotal”,”getLastLineItem”,”getLastReceivedDate”,”getLcOutstandingAmount”,”getLcOutstandingAmountForAged_”,”getMDAGLAccount”,”getMasterfile”,”getMyBank”,”getMyCompany”,”getMyCustomer”,”getMySupplier”,”getObjectDisplayText”,”getOutputTaxAccount”,”getPaymentDate”,”getProfitForInvoice”,”getReconciledDetails”,”getReferenceToTen”,”getSOorPSnumber”,”getSavingStatus”,”getTaxPeriodEndDate”,”getTotalDimensionUnits”,”getTranDelAddress”,”getTranType”,”getTransTypeForBankStatement”,”getUpdateSTInvoiceTotalAmount”,”gtGetOwnerModule”,”gti_GetDocumentDate”,”gti_GetPostingDate”,”hasAppliedUnapplied”,”hasBLLine”,”hasBeenAppliedFrom”,”hasBeenUpdatedToGL”,”hasBeenWriteOff”,”hasINDimensionTrackedItems”,”hasINLine”,”hasJCLines”,”hasMyRWCustomerChildAccount”,”hasNarrationOnTransaction”,”hasTranDelAddress”,”holdCode”,”identity”,”isARInvoice”,”isARTypeTransaction”,”isCreditCardLimintExceeded”,”isCurrencyRateMultiply”,”isFCTransaction”,”isFromExchange”,”isFromFactoryOrderReceipt”,”isFromSalesOrder”,”isJCTimesheetOrHRPay”,”isLineItemsFromARInvoiceForm”,”isPackingSlip”,”isPaymentType”,”isPrinted”,”isReadOnly”,”isRootTransaction”,”isSelected”,”isSystemDelete”,”isTransactionSourcePayroll”,”isUpdatingTaxPeriodSummary”,”isUsingWithholdingTax”,”lcDiscount”,”lcHoldAmount”,”lcNetAmount”,”lcOutstandingAmount”,”lcTaxAmount”,”manageTaxJournals”,”modifiedTimeStamp”,”modifiedUser”,”myARControl”,”myARInvoiceBatch”,”myARPeriodSummary”,”myBatch”,”myBranch”,”myCurrency”,”myCustomer”,”myDeliveryAddress”,”myFunctionalCreditCardDetail”,”myGenDebtorsControlJournal”,”myGenGLForcedBalanceTran”,”myGeneratedTaxJournal”,”myHoldCode”,”myModule”,”myOpeningBalance”,”myOutputTaxPeriodSummary”,”myOutstandingPeriodSummary”,”myOverdueInterestPeriodSummary”,”myPaymentTerm”,”myPeriodSummary”,”myRWCustomer”,”myRWSupplier”,”myReceiptType”,”mySalesPerson”,”mySourceModule”,”myStandingInvoice”,”needToCheckJCInvocingAuthory”,”oid”,”orderNumber”,”paymentDate”,”postingDate”,”printARStatementBalance”,”promptPaymentDate”,”reference”,”rwARInvoiceFCValue”,”rwARInvoiceLCValue”,”rwCustomerOrderNumber”,”rwFCGross”,”rwFCNet”,”rwFCTotal”,”rwHasTotalLineQtyZero”,”rwLCGross”,”rwLCNet”,”rwLCTotal”,”rwPackingSlipReference”,”rwPaymentDate”,”rwStatBFTransValue”,”sourceStdingInvRef”,”sourceSystem”,”sourceTranLongRef”,”sourceTranShortRef”,”standardText”,”taxDeductedAmount”,”tranType”,”uniqueKey” FROM “ARInvoice”

ERROR: [Jade Software Corporation][JADE ODBC Driver] Expecting: 'statement'; found 'select' at 0

Check C:Documents and SettingsScott Thompson.SCOTTApplication DataSQLyogsja.log for complete error details.

ERROR: [Microsoft][ODBC Driver Manager] Function sequence error

Check C:Documents and SettingsScott Thompson.SCOTTApplication DataSQLyogsja.log for complete error details.

Total time taken – 58 sec(s)

[Khushboo]

Hi Scott,

Quote:

In the log SQLyog seems to use lowercase for the 'select' but capitals for the 'FROM' clause. How can I force uppercase for this?

There is no way to select a case for the keywords in the queries written in log file.

But you are absolutely correct that there is inconsistency as FROM and SELECT both are keywords and still there is difference in cases.

We will change this behavior from next release and make sure that SELECT is also in UPPER CASE along with other keywords in the queries which we are framing while importing.

Thank You.

Regards,

Khushboo

[Scott Thompson]

Great, thanks for that. How often are new releases?

[Khushboo]

Hi Scott,

This fix will be included in next public release 8.61 GA, releasing probably today.

Thank You.

Regards,

Khushboo

[Khushboo]

Hi,

We have released 8.61 GA with this fix.

“Import external data now always uses UPPERCASE for keywords in queries sent to the ODBC-source as some ODBC-sources require this.”

Please refer to:

http://www.webyog.com/blog/2010/09/02/sqlyog-mysql-gui-8-61-released/

Thanks & Regards,

Khushboo

[Scott Thompson]

Thanks heaps, I updated and it seems to run now, I did about 20 tables with no issue. I then tried doind the entire database and it ran for about an hour then I received an error “Not enough memory, application terminated!” Any ideas?

Thanks.

[Khushboo]

Hi Scott,

Thank you for verifying the fix.

Quote:

I then tried doing the entire database and it ran for about an hour then I received an error “Not enough memory, application terminated!”

We actually did some code optimization in version 8.61 which solved “Not enough memory” issue. But in case you are still getting this issue then please let us know:

1) What is the size of the database which you are trying to Import? (number of tables and maximum row size in case tables have blob/text data-types)

2) Is it happening for a particular table? If yes then please send us the structure of the table and let us know the maximum row size of the table.

3) How much physical memory do you have?

Regards,

Khushboo

[Author]

Posts