Heavy-Lifting PHP script produces 500 Error... why?

jcaprajcapra Posts: 4Member
edited November 2018 in Development
Hello James Ratcliffe and the API Team!

James here with Green Valley Range.

I wrote a PHP script to access all our monthly billings (it finds every customer in a custom-made 1st or 15th Group, then lists all the recurring payment history of their current membership cycle). We have just a dozen or so billings on the 1st of the month, so I can retrieve those, no problem.

We have far more on the 15th of the month. It is a looooong operation that retrieves the first 65 records and then displays the message below...

Is this a timeout thing? A redirection thing? And how do I fix it? 

Thank you! Have a great week!

-James


An unexpected error occurred. Traceback (most recent call last): File "cherrypy/wsgiserver/__init__.pyc", line 1214, in communicate File "cherrypy/wsgiserver/__init__.pyc", line 733, in respond File "cherrypy/wsgiserver/__init__.pyc", line 1895, in respond File "lsserver/lib/gzip_middleware.pyc", line 163, in stream_gen File "web/application.pyc", line 625, in wrap File "web/application.pyc", line 617, in next File "lsserver/search/search_base.pyc", line 709, in generate_response File "lsserver/search/search_query_builder.pyc", line 451, in generate_response File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/orm/query.py", line 2571, in count File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/orm/query.py", line 2215, in scalar File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/orm/query.py", line 2184, in one File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/orm/query.py", line 2227, in __iter__ File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/orm/query.py", line 2242, in _execute_and_instances File "lsserver/database/database_service.pyc", line 366, in execute File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/engine/base.py", line 1449, in execute File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/engine/base.py", line 1584, in _execute_clauseelement File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/engine/base.py", line 1698, in _execute_context File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/engine/base.py", line 1691, in _execute_context File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/engine/default.py", line 331, in do_execute File "pgdb.pyc", line 277, in execute File "pgdb.pyc", line 307, in executemany DatabaseError: (DatabaseError) error 'ERROR: invalid input syntax for type date: "" LINE 6: ...'), '') LIKE '%Billing%') AND invoice.date_cre >= '' ORDER B... ^ ' in 'SELECT count(*) AS count_1 FROM (SELECT invoice._rowid AS invoice__rowid, invoice.date_cre AS invoice_date_cre, coalesce(invoice.total_paid_currency, 0.00) AS total_paid_currency FROM invoice LEFT OUTER JOIN terms ON terms._rowid = invoice.fk_terms_id LEFT OUTER JOIN customer ON customer._rowid = invoice.fk_customer_id AND customer._rowid > 0 WHERE invoice._rowid != 0 AND coalesce(customer.id_customer, '') = 'C-32382' AND invoice._rowid IN (SELECT invoice_line.fk_invoice_id FROM invoice_line WHERE coalesce(coalesce(invoice_line.description, ''), '') LIKE '%Billing%') AND invoice.date_cre >= '' ORDER BY invoice.datetime_cre ASC NULLS LAST, invoice._rowid ASC NULLS LAST) AS anon_1' 'SELECT count(*) AS count_1 \nFROM (SELECT invoice._rowid AS invoice__rowid, invoice.date_cre AS invoice_date_cre, coalesce(invoice.total_paid_currency, %(param_1)s) AS total_paid_currency \nFROM invoice LEFT OUTER JOIN terms ON terms._rowid = invoice.fk_terms_id LEFT OUTER JOIN customer ON customer._rowid = invoice.fk_customer_id AND customer._rowid > %(_rowid_1)s \nWHERE invoice._rowid != %(_rowid_2)s AND coalesce(customer.id_customer, %(param_2)s) = %(coalesce_1)s AND invoice._rowid IN (SELECT invoice_line.fk_invoice_id \nFROM invoice_line \nWHERE coalesce(coalesce(invoice_line.description, %(param_3)s), %(param_4)s) LIKE %(coalesce_2)s) AND invoice.date_cre >= %(date_cre_1)s ORDER BY invoice.datetime_cre ASC NULLS LAST, invoice._rowid ASC NULLS LAST) AS anon_1' {'coalesce_2': u'%Billing%', 'coalesce_1': u'C-32382', 'param_4': u'', 'date_cre_1': u'', 'param_1': Decimal('0.00'), 'param_3': u'', 'param_2': u'', '_rowid_2': 0, '_rowid_1': 0} string(3952) "HTTP/1.1 500 Internal Server Error Content-Length: 3853 Content-Type: text/xml; charset=utf-8 An unexpected error occurred. Traceback (most recent call last): File "cherrypy/wsgiserver/__init__.pyc", line 1214, in communicate File "cherrypy/wsgiserver/__init__.pyc", line 733, in respond File "cherrypy/wsgiserver/__init__.pyc", line 1895, in respond File "lsserver/lib/gzip_middleware.pyc", line 163, in stream_gen File "web/application.pyc", line 625, in wrap File "web/application.pyc", line 617, in next File "lsserver/search/search_base.pyc", line 709, in generate_response File "lsserver/search/search_query_builder.pyc", line 451, in generate_response File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/orm/query.py", line 2571, in count File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/orm/query.py", line 2215, in scalar File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/orm/query.py", line 2184, in one File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/orm/query.py", line 2227, in __iter__ File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/orm/query.py", line 2242, in _execute_and_instances File "lsserver/database/database_service.pyc", line 366, in execute File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/engine/base.py", line 1449, in execute File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/engine/base.py", line 1584, in _execute_clauseelement File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/engine/base.py", line 1698, in _execute_context File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/engine/base.py", line 1691, in _execute_context File "build/bdist.macosx-10.4-x86_64/egg/sqlalchemy/engine/default.py", line 331, in do_execute File "pgdb.pyc", line 277, in execute File "pgdb.pyc", line 307, in executemany DatabaseError: (DatabaseError) error 'ERROR: invalid input syntax for type date: "" LINE 6: ...'), '') LIKE '%Billing%') AND invoice.date_cre >= '' ORDER B... ^ ' in 'SELECT count(*) AS count_1 FROM (SELECT invoice._rowid AS invoice__rowid, invoice.date_cre AS invoice_date_cre, coalesce(invoice.total_paid_currency, 0.00) AS total_paid_currency FROM invoice LEFT OUTER JOIN terms ON terms._rowid = invoice.fk_terms_id LEFT OUTER JOIN customer ON customer._rowid = invoice.fk_customer_id AND customer._rowid > 0 WHERE invoice._rowid != 0 AND coalesce(customer.id_customer, '') = 'C-32382' AND invoice._rowid IN (SELECT invoice_line.fk_invoice_id FROM invoice_line WHERE coalesce(coalesce(invoice_line.description, ''), '') LIKE '%Billing%') AND invoice.date_cre >= '' ORDER BY invoice.datetime_cre ASC NULLS LAST, invoice._rowid ASC NULLS LAST) AS anon_1' 'SELECT count(*) AS count_1 \nFROM (SELECT invoice._rowid AS invoice__rowid, invoice.date_cre AS invoice_date_cre, coalesce(invoice.total_paid_currency, %(param_1)s) AS total_paid_currency \nFROM invoice LEFT OUTER JOIN terms ON terms._rowid = invoice.fk_terms_id LEFT OUTER JOIN customer ON customer._rowid = invoice.fk_customer_id AND customer._rowid > %(_rowid_1)s \nWHERE invoice._rowid != %(_rowid_2)s AND coalesce(customer.id_customer, %(param_2)s) = %(coalesce_1)s AND invoice._rowid IN (SELECT invoice_line.fk_invoice_id \nFROM invoice_line \nWHERE coalesce(coalesce(invoice_line.description, %(param_3)s), %(param_4)s) LIKE %(coalesce_2)s) AND invoice.date_cre >= %(date_cre_1)s ORDER BY invoice.datetime_cre ASC NULLS LAST, invoice._rowid ASC NULLS LAST) AS anon_1' {'coalesce_2': u'%Billing%', 'coalesce_1': u'C-32382', 'param_4': u'', 'date_cre_1': u'', 'param_1': Decimal('0.00'), 'param_3': u'', 'param_2': u'', '_rowid_2': 0, '_rowid_1': 0} "

4 comments

  • jamesratcliffejamesratcliffe Posts: 160Administrator, Lightspeed Staff moderator
    edited November 2018
    @jcapra The important part of the error is
    ERROR: invalid input syntax for type date

    I'm not sure if this is a problem with the API call or something weird in the OnSite database. Please send the API call that's triggering the error.


    James Ratcliffe
    Lightspeed HQ
  • jcaprajcapra Posts: 4Member
    James,

    I shared a Dropbox link with [email protected] ...

    I’m attaching the whole suite of apps here. From the index, click on Billings, select 15th and hit Go to get the following error:

    ERROR: invalid input syntax for type date

    The formBillings.php makes the API call, and billings.php is the dashboard file that displays the result.

    In the lightspeed folder, in the rest_connector.php, I have the dynamic server address commented out, but that’s what I was using to make calls outside of our network…

    Thanks!

    -James
  • jamesratcliffejamesratcliffe Posts: 160Administrator, Lightspeed Staff moderator
    I can't find the link. I see an email from you, but no link.

    Please find the exact call that's getting that error.

    I looked over the error again, and it looks like the problem is a blank date being used with the created date filter.
    James Ratcliffe
    Lightspeed HQ
  • jamesratcliffejamesratcliffe Posts: 160Administrator, Lightspeed Staff moderator
    @jcapra It must be the call that's searching invoices with a filter based on the created_date. Either the string interpolation is working, or the variable isn't being set correctly. 
    James Ratcliffe
    Lightspeed HQ
Sign In or Register to comment.