Connection to SQL Server 2012

[alabil]

Hi,

I need to be able to connect to SQL server instead of the local db supplied with the installation, How can I do that ?

I have made a small form to display content of 1 table and nothing is showing. I have create a db connection , works fine, I have create a db resources, see all the table and fields so OK. assign it to a business view, all OK , create a little script to fetch the data , all this based on the tutorial, and at the end , no info on page but if I do the same with the ebase_sample db , everything works fine

What do I miss here ?
Thanks

[Jon]

Not sure what's wrong from your description. It all sounds OK. If the connection works, you should check the SQL. When you import a Database Resource it's very easy to leave something in the where clause e.g. key1=&&key1 and this means the fetchtable won't return any data. So check the Database Resource, if OK set the debug option in the Database Resource and check the SQL being issued (in the Execution Log). Cut and paste the SQL into a SQL Server database tool and check it works as expected. Finally, check the mappings are OK.

[alabil]

Hi,

please keep in mind this is new stuff for me. the select statement is as follow
select * from db.dbo.provinces, nothing else ( I follow the wizard)

How to I debug the database resource and where is the execution log ? ( I have check the debug check box and then what)

The SQL does returns data from SQL SSMS
How to I check the mapping?

THanks

[Jon]

Database Resource Debug: open the Database Resource in the designer, Debug checkbox is in the top right-hand corner, check this then save the resource. Then run the form.

Execution log: View > Execution Log (from the menu in the designer). You expect to see lines something like this:

14:59:09.945 Debug: Debug for database resource MYCDS - SQL statement:
14:59:09.945 Debug: select * from mycds order by CD_ID

It just shows the SQL that has been issued, which is probably not enormously useful to you. But you should check that the same SQL executes ok in your SQL Server database tool and returns the records expected. Possibly it should be just "select * from provinces".

Mappings: open the form, then click the mappings icon on the toolbar at the top (5th icon from the left). This shows you the mappings between the Database Resource fields and the table columns in the form. You expect to see that all columns are mapped.

[alabil]

THanks for the information input

the log shows as follow:
10:14:01.176 Debug: select * from WEBBODWv2.dbo.DimProvince where ProvinceID=?
10:14:01.176 Debug: parameter1 : value null
10:14:01.179 Debug: End execution of command - fetchtable : 10:14:01.179

I know I did not enter any values or parameters in the where clause originally. I just recheck the resource and found this :
ProvinceID=&&ProvinceID

Just removed it and it now works fine, so my question is how can this be added after I follow the wizard ??

Thasnks

[Jon]

Yes I agree it's confusing. The wizard doesn't know whether you want to read all the records from the table or just use the resource as a single record lookup using the primary key. So it detects the primary key and adds these columns to the where clause (in your case the ProvinceId column). In the last page of the wizard you have the chance to change this but I think this page is poorly designed. There is a Tables Only checkbox and if you check this you can then click on the columns to the right and remove them from the where clause. Sorry you found it confusing.

[alabil]

Thank you very much

greatly appreciated

[Segi]

alabil,

I have found that when you use the database schema wizard, it creates the resource as Assisted SQL and also defaults to non-table operations which most of the time you do NOT want. If you are comfortable doing so, it might be better to write Native SQL. You get much more flexibility with your queries.

For example, in Native SQL you could have your select query read like this:

select * from WEBBODWv2.dbo.DimProvince &&WHERECLAUSE

where the resource field WHERECLAUSE is not required, does not have persistent checked and has dynamic SQL checked.

In the server side you can build the WHERECLAUSE and call tables.RESOURCENAME.fetchTable() to get records based on the criteria that you specified in the WHERECLAUSE