Hi guys!
I’m making my first web service in xojo and I need some help.
The database is MSSql 2008 R2.
I started from the REST Web Service part 2 example, and modified the source code for my DB type (the original refers to eddieelectronics sql lite file).
The web service is the EEWebService code binary.
So this is what I’ve modified:
- in the open event of the app I made the connection to the db with success
- I create a new method “ElencoAlunni” that contains the query to my db, here’s the code
// Always returns specific columns for displaying customers in a list
Dim stringa_sql as string
stringa_sql="SELECT CODSTUDE, COGSTUDE, NOMSTUDE FROM ANA_STUD where TIPSTUDE='S'"
msgbox(stringa_sql)
If db1.Connect Then
MsgBox("Connect to database: " +db1.DatabaseName)
System.DebugLog("Connect to database: " +db1.DatabaseName)
Else
MsgBox("Connection error:" + db1.ErrorMessage)
System.DebugLog("Connection error:" + db1.ErrorMessage)
End If
Dim ps As MSSQLServerPreparedStatement
ps = db1.Prepare(stringa_sql)
Dim rs As RecordSet = ps.SQLSelect
'Dim rs As RecordSet
'rs = db1.SQLSelect(stringa_sql)
If DB1.Error Then
System.DebugLog("DB Error: " + DB1.ErrorMessage)
msgbox("DB Error: " + DB1.ErrorMessage)
Dim jsonError As New JSONItem
jsonError.Value("DBError") = DB1.ErrorMessage
Return jsonError
else
msgbox("DB ok")
End If
msgbox(RS.FieldCount.ToText)
'If DB.Error Then
'System.DebugLog("DB Error: " + DB.ErrorMessage)
'
'Dim jsonError As New JSONItem
'jsonError.Value("DBError") = DB.ErrorMessage
'Return jsonError
'End If
Dim jsonAlunni As New JSONItem
Dim cust As New Dictionary
While Not rs.EOF
cust = New Dictionary
cust.Value("FirstName") = rs.Field("COGSTUDE").StringValue
cust.Value("LastName") = rs.Field("NOMSTUDE").StringValue
'cust.Value("City") = rs.Field("City").StringValue
'cust.Value("State") = rs.Field("State").StringValue
'cust.Value("Zip") = rs.Field("Zip").StringValue
jsonAlunni.Value(rs.Field("CODSTUDE").StringValue) = cust
MsgBox( rs.Field("COGSTUDE").StringValue)
rs.MoveNext
Wend
rs.Close
Dim jsonResults As New JSONItem
jsonResults.Value("ElencoAlunni") = jsonAlunni
Return jsonResultson the server the last msgbox I can see is; msgbox(stringa_sql) then nothing else.
This is the other code that refers to the client used to query the web service, is called WebQuote, just modified the example code
in the action event of the button:
QuoteArea.Text=""
'QuoteSocket_ciro1.SendRequest("POST", "http://demos.xojo.com/EEWS/index.cgi/api/GetAllCustomers")
'QuoteSocket_ciro1.SendRequest("POST", "127.0.0.1:8080/special/GetAllCustomers")
QuoteSocket_ciro1.SendRequest("POST", "127.0.0.1:8080/special/ElencoAlunni")
Return
'Dim postData As New JSONItem
'postData.Value("Type") = "MovieQuotes"
'QuoteSocket1.SetRequestContent(postData.ToString, "application/x-www-form-urlencoded")
'QuoteSocket1.Post("127.0.0.1:8080/special/quote")in the PageReceived event of the socket:
'Dim jsonData As Text = DefineEncoding(content,Encodings.SystemDefault).ToText
Dim jsonData As Text = DefineEncoding(content,Encodings.UTF8).ToText
'Dim jsonData As Text = Xojo.Core.TextEncoding.UTF8.ConvertDataToText(Content)
Dim json As Xojo.Core.Dictionary
json = Xojo.Data.ParseJSON(jsonData)
Dim customers As Xojo.Core.Dictionary
customers = json.Value("ElencoAlunni")
'customers = json.Value("GetAllCustomers")
For Each entry As Xojo.Core.DictionaryEntry In customers
Dim custDict As Xojo.Core.Dictionary = entry.Value
Dim firstName As Text = custDict.Value("FirstName")
QuoteArea.Text=QuoteArea.Text+firstName+chr(13)
Nextthe problem is that I’m getting this error:
content=Internal Server Error
Internal Server Error
The server encountered an unhandled NilObjectException while executing this request.
and also
exception=xojo.data.invalidjsonexception (lexical error: invalid char in json text.)
but the query specified in the web service
stringa_sql=“SELECT CODSTUDE, COGSTUDE, NOMSTUDE FROM ANA_STUD where TIPSTUDE=‘S’”
works in the enterprise manager tool!!
any idea?
thx
Ciro