sage2.php not creating equivalent mysql table from invoice_item table

[julianbryan]

Whenever the table invoice_item is included in the array $tablesArray, the script appears to fail without any error message when it gets to that table. All tables prior to that one execute successfully but if its the first in the array or part way thorugh nothing else executes.

[Boran]

I've not yet tried working on that table.

Does the sage1.php script understand that table, of does it give an error too? In sage2, is $debug=true

Another idea is to use sage3. Adapt the $tables array, and specifc the key colmn then. Enable some of the print_r lines to get information on the table as it progresses.

[julianbryan]

Many thanks Sean

I attach output from sage1 and sage2

Sage3 generated that table but no data got transferred. I don’t know whether that gives you a clue.

Julian

From: Sean Boran [mailto:notifications@github.com] Sent: 19 August 2013 06:48 To: Boran/sage Cc: julianbryan Subject: Re: [sage] sage2.php not creating equivalent mysql table from invoice_item table (#1)

I've not yet tried working on that table.

Does the sage1.php script understand that table, of does it give an error too? In sage2, is $debug=true

Another idea is to use sage3. Adapt the $tables array, and specifc the key colmn then. Enable some of the print_r lines to get information on the table as it progresses.

— Reply to this email directly or view it on GitHubhttps://github.com/Boran/sage/issues/1#issuecomment-22852129 .

Confidentiality & Disclaimer: This email and any attachments hereto, are intended for use by the addressee(s) named herein and may contain legally privileged material and confidential information that is exempt from disclosure by law. If you are not the intended recipient of this email, you are hereby notified that, any disclosure, tampering, copying or distribution of the content and/or any attachments hereto, is strictly prohibited and you must not read, use or take any action in reliance of the information contained herein. If you have received this e-mail in error, please immediately notify the sender by telephoning +44(0)161-236-2910 or by e-mailing: administrator@quill.co.uk. In addition you must permanently delete the original e-mail, all copies and destroy any hardcopies. Please be aware that internet email is not a secure communications medium. Although we have scanned this email and any attachments for viruses, we cannot guarantee that any file is virus free. It is your responsibility to ensure emails that you receive are 100% virus free. You accept and are to observe this lack of security when emailing us. Quill Computer Systems Limited, Barclay House, 35 Whitworth Street West, Manchester, M1 5NG, UK

[Boran]

I do not see any attached output.

[julianbryan]

Output from Sage1:

Connect to SageLine50v18 as julian Table COMPANY, fields=155, rows=1 Table PERIOD, fields=2, rows=14 Table SALES_LEDGER, fields=141, rows=800 Table PURCHASE_LEDGER, fields=138, rows=233 Table NOMINAL_LEDGER, fields=123, rows=255 Table AUDIT_HEADER, fields=78, rows=29669 Table AUDIT_SPLIT, fields=64, rows=43214 Table AUDIT_HISTORY_HEADER, fields=43, rows=0 Table AUDIT_HISTORY_SPLIT, fields=31, rows=0 Table AUDIT_HISTORY_JOURNAL, fields=25, rows=0 Table AUDIT_HISTORY_USAGE, fields=15, rows=0 Table AUDIT_JOURNAL, fields=34, rows=107041

Output from Sage2

Connect to SageLine50v18 as julian Array ( [0] => COMPANY [1] => PERIOD [2] => SALES_LEDGER [3] => PURCHASE_LEDGER [4] => NOMINAL_LEDGER [5] => AUDIT_HEADER [6] => AUDIT_SPLIT [7] => AUDIT_HISTORY_HEADER [8] => AUDIT_HISTORY_SPLIT [9] => AUDIT_HISTORY_JOURNAL [10] => AUDIT_HISTORY_USAGE [11] => AUDIT_JOURNAL [12] => AUDIT_VAT [13] => AUDIT_USAGE [14] => DEPARTMENT [15] => BANK [16] => STOCK [17] => STOCK_TRAN [18] => STOCK_CAT [19] => STOCK_COMP [20] => INVOICE [21] => SALES_ORDER [22] => PURCHASE_ORDER [23] => RECURRING [24] => FIXED_ASSET [25] => FIXED_ASSET_CAT [26] => PREPAYMENT [27] => ACCRUAL [28] => INVOICE_ITEM [29] => SOP_ITEM [30] => POP_ITEM [31] => SALES_DEL_ADDR [32] => PURCHASE_DEL_ADDR [33] => COMPANY_DEL_ADDR [34] => PRICE [35] => PRICE_LIST [36] => GRN_ITEM [37] => GDN_ITEM [38] => PROJECT [39] => PROJECT_TRAN [40] => PROJECT_ONLY_TRAN [41] => PROJECT_STATUS [42] => PROJECT_RESOURCE [43] => PROJECT_RESOURCE_TYPE [44] => PROJECT_COST_CODE [45] => PROJECT_REVENUE_CODE [46] => PROJECT_COST_TYPE [47] => PROJECT_RELATIONSHIP [48] => PROJECT_BUDGET [49] => CLEAR_AUDIT_TRAIL_EVENT [50] => CLEARED_TRAN_RANGE [51] => INTRASTAT [52] => COUNTRY_CODE [53] => DISPUTE_REASON [54] => BANK_STATEMENT [55] => BANK_STATEMENT_LINE [56] => FUND [57] => FUND_TYPE [58] => SOFA_CATEGORY [59] => STOCK_ALLOCATION [60] => CREDIT_BUREAU [61] => CHART_LIST [62] => CAT_TITLE [63] => CATEGORY [64] => TAX_CODE [65] => UPDATE_LEDGER [66] => CURRENCY [67] => VAT_SUMMARY [68] => VAT_RETURN [69] => RTD_SUMMARY [70] => FINANCIAL_BUDGET [71] => CIS_RETURN [72] => CIS_RETURNED_TRANSACTION [73] => CIS_SETTINGS [74] => CIS_SUBCONTRACTOR [75] => CIS_SUBCONTRACTOR_LEGACY [76] => CIS_SUBCONTRACTOR_RETURN [77] => CIS_SUBCONTRACTOR_TAX_HISTORY [78] => ESUBMISSION_SETTINGS [79] => PROTX_PAYMENT [80] => ECSALES_HEADER [81] => ECSALES_SPLIT [82] => ECSALES_FILL [83] => VAT_DETAILS [84] => VAT_ADJUSTMENT [85] => VAT_RETURN_RECEIPT [86] => VAT_REV_CHARGE_HEADER [87] => VAT_REV_CHARGE_RETURN [88] => VAT_REV_CHARGE_DETAIL [89] => CALENDAR_EVENT [90] => CALENDAR_EVENT_LABEL [91] => CALENDAR_EVENT_LOCATION [92] => CALENDAR_RECURRENCE_PATTERN [93] => CONTACT_HISTORY [94] => LETTER_TYPE [95] => COMMUNICATION_ADDRESS [96] => EC_VAT_DESCRIPTION [97] => CONTACT_HISTORY_CONTACT_MAJOR_TYPE [98] => CONTACT_HISTORY_CONTACT_TYPE [99] => CONTACT_HISTORY_CONTACT_OUTCOME [100] => DELETED_RECORDS [101] => PAYMENT_METHOD [102] => REMITTANCE ) Table COMPANY, fields=155, rows=1. OK Completed Import of COMPANYTable PERIOD, fields=2, rows=14. OK Completed Import of PERIODTable SALES_LEDGER, fields=141, rows=800. OK Error inserting/updating data on row: 1: Unknown column 'COMPANYID' in 'field list' Error inserting/updating data on row: 2: Unknown column 'PERIOD' in 'field list' Error inserting/updating data on row: 3: Unknown column 'PERIOD' in 'field list' Error inserting/updating data on row: 4: Unknown column 'PERIOD' in 'field list' Error inserting/updating data on row: 5: Unknown column 'PERIOD' in 'field list' Error inserting/updating data on row: 6: Unknown column 'PERIOD' in 'field list' Error inserting/updating data on row: 7: Unknown column 'PERIOD' in 'field list' Error inserting/updating data on row: 8: Unknown column 'PERIOD' in 'field list' Error inserting/updating data on row: 9: Unknown column 'PERIOD' in 'field list' Error inserting/updating data on row: 10: Unknown column 'PERIOD' in 'field list' Error inserting/updating data on row: 11: Unknown column 'PERIOD' in 'field list' Error inserting/updating data on row: 12: Unknown column 'PERIOD' in 'field list' Error inserting/updating data on row: 13: Unknown column 'PERIOD' in 'field list' Error inserting/updating data on row: 14: Unknown column 'PERIOD' in 'field list' Completed Import of SALES_LEDGERTable PURCHASE_LEDGER, fields=138, rows=233. OK Completed Import of PURCHASE_LEDGERTable NOMINAL_LEDGER, fields=123, rows=255. OK Completed Import of NOMINAL_LEDGERTable AUDIT_HEADER, fields=78, rows=29669

[julianbryan]

Sean, i have pasted in above as attachments getting stripped out along the way. I also looked at the table invoice_items that got created from sage3 and while it is empty i can run a Mysql INSERT query and ente data into rows.

Thanks

[julianbryan]

I think i know why this table doesnt populate. The primary key is created on invoice_number which is not unique because there is a row for every invoice and some invoices have multiple lines. An auto increment primary key field probably needs adding to the mysql table and the primary key taking off the invoice_number field it is currently on

[julianbryan]

That worked but double values are not being populated despite adding:

elseif ($value == "DOUBLE") $sql.= "DECIMAL(10,2)";

[kinfegebwg]

Hi There,

I kept Getting Error connecting to: SageLine50v19 here the code $odbc['dsn'] = "SageLine50v19"; $odbc['user'] = "ALEX"; $odbc['pass'] = "XXXXXX"; $mysql['host'] = "localhost"; $mysql['user'] = "root"; $mysql['pass'] = ""; $mysql['dbname'] = "sagetest"; $mysql['idfield'] = "id";

and After i select servername/ip address where Sage is installed when i hit Test i get a dialoug box Saying "Logon to" and ask me to enter Username and Password: Is that Sage login and password or What?. I tried on of Sage Login but I get error message saying " Invalid user ID"

[Boran]

What does this have to do with this thread? As regards your question: the scripts cannot be used, until you get ODBC to sage working.

[kinfegebwg]

@Boran Thank you for these awesome API. I have some questions

Do i have to run 'sage_t5_cust.php' to sync from sage Customer table to Take5 customer table in mysql or script automatically sync data between two tables?

And How can i access purchase_ledger, nominal_ledger, SALES LEDGER tables I cant see these table on the list ? i'm using Sage 50 us

It sound weird and out of topic but Is it possible to run from web server and access my local Database?

Thank you I wish you have a great Week!

[Boran]

I think you are not use the sage odbc drivers.

On 10 October 2014 21:13, Kinfe Woldegiorgis notifications@github.com wrote:

Hi @Boran https://github.com/Boran , I am trying to connect hosted webs application to Local database. First I was getting Fatal error: Call to undefined function odbc_connect() error but after add the "odbc" extension i started getting

Error connecting to the ODBC database: [unixODBC][Driver Manager]Data source name not found, and no default driver specified

Any idea?

— Reply to this email directly or view it on GitHub https://github.com/Boran/sage/issues/1#issuecomment-58702857.