What happened to the Test Source in the Query Builder using Server Connect?
This functionality was so useful for troubleshooting.

I'm having trouble with complex joins that were working fine using the old data source method. I use the exact same query with the new SC method, but can't get any records to show. 'Test Source' would have been so helpful to troubleshoot in this situation.

Hello David,
Please check this video: www.dmxzone.com/go/32354/debugging-problems-in-dmxzone-server-connect/

Hi Teodor

I think the problem is with Paged Queries and joined tables.
If I just use ONE table there is no problem. Loads quickly (3000 records)

Chrome Dev Tools gives this error:
"SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'topography_id' in on clause is ambiguous"

If I set up an ordinary query (without paging) in Server Connect using the joined tables as above, there is no error reported. Still a bit slow to display 3000 records! Which is why I need the Paged Query.

Dave

This error means both of your tables are using the same id column named 'topography_id' which causes the error. Can you send a screenshot of the two tables structures?

Hi Teodor - I emailed you 2 action files and the database structure last week.
Any ideas?
Dave

Hi David,
We are checking the issue right now.

Thanks Teodor

I've just tested another paged query with a simple INNER JOIN

Parent table: origins with PK origin_id
Child table: cases with PK cases_id, FK origin_id

I ran the following SQL using phpmyadmin:

SELECT cases.museum_no, cases.lab_no, cases.title, origins.origin
FROM origins
INNER JOIN cases
ON origins.origin_id = cases.origin_id

This works fine.

But using a SC paged query in the action file > >

Still get the 500 Internal server error:
{code: "23000", file: "D:\Documents\Sites XAMPP\digipath2015\dmxConnectLib\lib\db\Connection.php",…}
then expanded:

1. code:"23000"
2. file:"D:\Documents\Sites XAMPP\digipath2015\dmxConnectLib\lib\db\Connection.php"
3. line:75
4. message:"SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'origin_id' in on clause is ambiguous"
5. trace:"#0 D:\Documents\Sites XAMPP\digipath2015\dmxConnectLib\lib\db\Connection.php(75): PDOStatement->execute()↵#1 D:\Documents\Sites XAMPP\digipath2015\dmxConnectLib\modules\dbconnector.php(86): lib\db\Connection->execute('SELECT COUNT(*)...', Array)↵#2 D:\Documents\Sites XAMPP\digipath2015\dmxConnectLib\lib\App.php(184): modules\dbconnector->paged(Object(stdClass), 'qryCaseOriginTe...')↵#3 D:\Documents\Sites XAMPP\digipath2015\dmxConnectLib\lib\App.php(153): lib\App->execSteps(Object(stdClass))↵#4 D:\Documents\Sites XAMPP\digipath2015\dmxConnectLib\lib\App.php(123): lib\App->execSteps(Array)↵#5 D:\Documents\Sites XAMPP\digipath2015\dmxConnectLib\lib\App.php(71): lib\App->exec(Object(stdClass))↵#6 D:\Documents\Sites XAMPP\digipath2015\dmxConnect\api\Case\listCaseOriginTest.php(180): lib\App->define('{? "meta": {? ...')↵#7 {main}"

I'm using the new State Management behaviors to control the back & forward buttons.

If I change the SC action file to a simple joined query, there is no problem. Just get 3000 records on the same page!

I'm using Dreamweaver CS6 on Windows 7
Table Storage Engine: InnoDB

Dave

Hello David,
This will be fixed in tomorrows update, but until then you can fix it yourself in the following file:
/dmxConnectLib/lib/db/SqlBuilder.php line 263, change:

if ($this->type == 'select' && !empty($this->joins)) {
to
if (!empty($this->joins)) {

Hi Teodor

I changed this code above in SqlBuilder.php , but not quite there yet.

There is a blank table of 5 rows without content.

However there are no errors reported in Chrome developer tools.

The record paging is working OK.
For example:
No of records: 2942
Page 3 of 148

But the first, previous, next, last page buttons are not working properly - check the URLs below:
view_case_origin_test.php?page=0
view_case_origin_test.php?page=20
view_case_origin_test.php?page=40
...
...
view_case_origin_test.php?page=2940

Code for the FIRST page button:

<button type="button" class="btn btn-default btn-sm" onClick="dmxStateManagementAction('setUrlParam','page','{{sc_exec_CasePagedOrigin.data.qryCaseOriginTest.page.offset.first}}')"><i class="glyphicon glyphicon-fast-backward"></i></button>

I definitely selected the correct page > offset> first binding

Dave

I've tested this and it is working. Can you provide a link to your page?

Sorry Teodor - It's a testing site on a XAMPP server. I really wish that I could put it online for you to check it out.

I am also testing this locally on WAMP. All works fine after the fix i sent you.

Thanks Teodor - all working as it should now.
The repeat region was not bound on the <> data in the example above . I used the <>query by mistake!
Dave

Just another thing - there is still a general problem with how the URLs are displayed with paging.
For example in your video "Keep Pagination State with HTML5 Data Bindings State Management", each step forward displays the URLs like so :

index.php?page=0
index.php?page=10
index.php?page=20


Surely they should be displayed as :

index.php?page=0
index.php?page=1
index.php?page=2

Seems like the offset value in the name-value pair in the URL displayed should be divided by the limit

Dave

This value is an offset, it doesn't show the page and it is displayed exactly as it is supposed to be.
You can call the URL variable as you like, it may be "page" or "offset" or "anything" ...
This should not be displayed as ="1" ... ="2" etc. unless your limit is set to "1"

Got it now - one could have called the URL parameter anything but page

e.g. index.php?abc=2

I'll leave this alone now!

Thanks
Dave