I have been working on a few methods that query data set from SQL.
I created the folowing method:
var MySQLQuery = “SELECT ServiceOrders.ServiceOrderNumber,ServiceOrders.OrderClassification,ServiceOrders.DateTimeReceived,ServiceOrders.RepID,Accounts.Name”+
“FROM ServiceOrders, Accounts”+
“WHERE ServiceOrders.ReceivingAccountID = Accounts.AccountID”;
var maxrows = 100
var MySQLDataSet =databaseManager.getDataSetByQuery(currentcontroller.getServerName(),MySQLQuery, null, maxrows);
var HTML = “”+“
”;
for ( var i = 1; i <= MySQLDataSet.getMaxRowIndex(); i++ )
{
MySQLDataSet.rowIndex = i; //set the row
HTML +=
‘
’+
‘’+
‘’+
‘’+
‘’+
‘’+
‘’
}
globals.MySQLHTMLReport = HTML
I displayed it in a global html field, nothing shows up.
I reduced the Method to show just the dataset without html (probably did this wrong):
var MySQLQuery =
“SELECT
ServiceOrders.ServiceOrderNumber,ServiceOrders.OrderClassification,ServiceOrders.DateTimeReceived,ServiceOrders.RepID,Accounts.Name”+
“FROM ServiceOrders, Accounts”+
“WHERE ServiceOrders.ReceivingAccountID = Accounts.AccountID”;
var maxrows = 100
var MySQLDataSet =databaseManager.getDataSetByQuery(currentcontroller.getServerName(),MySQLQuery,null,maxrows);
globals.MySQLHTMLReport = MySQLDataSet globals.RunSQLReportNoHTML();
I get an error saying the method cannot be found. I thought this might be related to my XP / Java Main Class Error when booting my PC.
Here is my log file
| ’+‘MySQLDataSet[1]’+‘ | ’+‘MySQLDataSet[5]’+‘ | ’+‘MySQLDataSet[3]’+‘ | ’+‘MySQLDataSet[2]’+‘ | ’+‘MySQLDataSet[4]’+‘ |
a couple of notes:
globals.MySQLHTMLReport = MySQLDataSet globals.RunSQLReportNoHTML();
This seems strange. You’re strying to set your global with two different things MySQLDataSet (object?) and the result of the global script globals.RunSQLReportNoHTML();
globals.MySQLHTMLReport = HTML
I displayed it in a global html field, nothing shows up.
Are you sure you set the display property of your global to “HTMLarea”
activate your debugger and set a breakpoint in your “HTML +=” loop to see if data comes in.
Quote:
globals.MySQLHTMLReport = MySQLDataSet globals.RunSQLReportNoHTML();
Yes I noticed after I pasted the code, I did fix it to read:
globals.MySQLHTMLReport = MySQLDataSet
During Debug I receieve this Data Set Error :
MySQLDataSet java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Line 1: Incorrect syntax near ‘.’. [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Line 1: Incorrect syntax near ‘.’.
The syntax looks correct to me
It looks to be the “Null” String.
I copied from the example you posted. Is this correct?
getDataSetByQuery(currentcontroller.getServerName(),MySQLQuery, null, maxrows)
no, that looks OK.
The message seems to come from your database, complaining about the query it received.
Can you check that query perhaps in a query analizer?
I Ran in the query analyzer:
SELECT ServiceOrders.ServiceOrderNumber,ServiceOrders.OrderClassification,ServiceOrders.DateTimeReceived,ServiceOrders.RepID,Accounts.Name
FROM ServiceOrders, Accounts
WHERE ServiceOrders.ReceivingAccountID = Accounts.AccountID
It returned the records correctly
I must be missing something. When I debug, the query runs fine, it throws the error when the reach the :
getDataSetByQuery(currentcontroller.getServerName(),MySQLQuery, null, maxrows).
Thanks for the help on this. I will try to figure it out.
Maybe I’m wrong, but you add several strings of text together into the query. Between the different strings do not seem to be any spaces.
This might be your problem… Just add a space at the end of each string of text (“Select…” , "From… ", "where… ")
Paul
I tried that, At least I the ‘serviceOrderNumber’ returned that time:
Here is the dbug variable listing
arguments [object Arguments]
maxrows 100
MySQLDataSet 182 183 185 186 187 188 191
datetimereceived undefined
name undefined
orderclassification undefined
repid undefined
serviceordernumber undefined
MySQLQuery SELECT ServiceOrders.ServiceOrderNumber, ServiceOrders.OrderClassification, ServiceOrders.DateTimeReceived, ServiceOrders.RepID, Accounts.Name FROM ServiceOrders, Accounts WHERE ServiceOrders.ReceivingAccountID = Accounts.AccountID
This must be the problem, some sort of spacing issue
Thank you for the help.
Is everything working OK now?
regarding the space issue:
I always place a space in front of each string part,
so you can easily check that no strings are “pasted” against each other.
var query = “SELECT A, B”+
" FROM table"+
" WHERE sdfsdfsd"+
" ORDER BY erwerwe";
etc…
Well, during debug I thought that all the fields should have variables next to them, but the all say undefined, is this correct?
Shouldn’t
MySQLDataSet 182 183 185 186 187 188 191
return the entire dataset? Not just the Service Order Numbers
I have not really played with the debugger yet because all me methods so far have been pretty basic.
As I am testing all the fuctionality that I want to include into my solution I am using more of the tools.
When I open my solution this morning to debug the method I have been working on, I enabled the debugger but have no option to run the method.
I have noticed this before when I was playing with the debugger but I wasn’t really concerned because I was not using it yet. I am not sure if my issues are related to my ignorance, or there is some conflict with my solution / Java / XP.
I will keep trying to get the debugger working and copy and paste the SOLDataSet variable to see if the entire dataset has be captured.
Thanks for your patience
When I open my solution this morning to debug the method I have been working on, I enabled the debugger but have no option to run the method.
make sure that you’re not in design mode.
(The green arrow will be disabled in that case)
Well, during debug I thought that all the fields should have variables next to them, but the all say undefined, is this correct?
Can you try setting a breakpoint inside your dataset loop, to see if the vars get filled after each increment?
If you want to post or mail your solution(with sampledata) I’ll be happy to examine it.
If my data is in SQL .mlb files. How would I put sample data into the solution So I could post it ?
When you export a solution, there’s an option to attache sample data.
(servoy does this for you)
ok, the good news is there’s nothing wrong with your query.
Just a few things to be aware about…
remarks about RunSQLOnlyReport script:
globals.MySQLHTMLReport = MySQLDataSet
// this only sets the global with the primary keys of the MySQLDataSet Object.
//you have to loop through the dataset in order to extract the data inside
use this instead…
globals.MySQLHTMLReport = “”+MySQLDataSet.getAsHTML()
//code should always start with “html” and the method “.getAsHTML()” extracts the data from MySQLDataSet as “raw” HTML tableview
remarks about RunSQLHTMLReport:
- make sure you’ve set the spacing right between the concatenating queryStrings
repace following…
‘’+‘MySQLDataSet[1]’+‘’+
with
‘’+MySQLDataSet[1]+‘’+
(and the ohter similar lines…)
Be aware of the syntax when concatenating literal strings with java variables/database columns
‘literalText1’+javascriptVariableObjectOrDatabaseColumn+‘literalText2’
maarten:
So here is where the data and html come together…
var HTML = "<html>"+ // all the HTML code is captured inside this var.
“
”;
for( var i = 1 ; i <= myDataset.getMaxRowIndex() ; i++ )
{
myDataset.rowIndex = i; //set the row
HTML += ‘’+
‘’+ //refers to contact_name
‘’+//refers tocompany_name
‘’
}
Now that you have all HTML code lined up do following:
1)create a global named myHTMLReport
2) place it in any form you want
3) set the display type property to "HTMLarea", and make it non editable
add this line in your script:
globals.myHTMLReport = HTML //fill the global with the html created in this script.
Attach this script to the onShow event of the form. (or perhaps other events)
This was the original example you compiled for me. That’s why I was a bit confused.
It boils down me working on syntax and better codeing practices.
I appreciate all the time you spent on this and other topics. In the end, I learned the basic concepts of a very powerful tool.
Thankx,
Erich
| ‘+myDataset[1]+’ | ‘+myDataset[2]+’ |