Null in long text MySQL field Varchar(2000)

[rclatorre]

Hello, I have a problem trying to access a MySQL field Varchar (2000).

This is the code:

sub FetchPublicationsList

'Gets all the available Publications
ExecuteRemoteQuery ("SELECT name, publication_id, image_url, publication_date, SUBSTRING (introduction, 1, 30) publication as ORDER BY FROM introduction publication_id" PUBLICATIONS_LIST) 

End Sub

Sub ExecuteRemoteQuery(Query As String, TaskId As Int)
   Dim req As HttpRequest
   req.InitializePost2("http://www.midomain.pe/testing/cns.php", Query.GetBytes("UTF8"))
   hc.Execute(req, TaskId)
End Sub

Sub hc_ResponseSuccess (Response As HttpResponse, TaskId As Int)
....
res = Response.GetString ("UTF8")
Dim parser As JSONParser
parser.Initialize (res)

Dim publications As List 
publications = parser.NextArray

.....
For i = 0 To publications.Size - 1

Dim m As Map
m = publications.Get (i)

lblIntroduction = Pan (i). GetView (4) 'Is a label
lblIntroduction.Text = m.Get ("introduction") ' <<<------ The problem
...
End Sub

The field "introduction" is Varchar (2000) and the query returns null.

Other fields if they bring value.

Thanks

 

[rclatorre]

Please, someone could see this issue?

Thanks

Roberto

 

[citywest]

Roberto,

A suggestion what happens if you don't use the SUBSTRING i.e just

SELECT name, publication_id, image_url, publication_date, introduction publication as ORDER BY FROM introduction publication_id

Now the above isn't valid SQL anyway I assume it should look something like

SELECT name, publication_id, image_url, publication_date, SUBSTRING (introduction, 1, 30) As introduction FROM publication ORDER BY publication_id

You have a script at the web server end which is running the SQL. I hope it's well though out and designed. What would happen if somebody was to do:

Delete From publication

OR Drop Table publication

And so on. It's a much better idea to have some parameterised functions in your server side code for security reasons which you can call from your client.

Cheers,

Mark S.

 

[rclatorre]

Mark, thanks for answering, you're right I should improve access to the database.

However the problem I have is that when MySQL returns me values ​​for that query, I returns values ​​for all fields except for the introduction field (this field is varchar (2000), I've been testing and returns only up to 60 characters (so I put in the post the substring (introduction, 1,30))

Roberto

 

[rclatorre]

Mark,

Thanks for the help, I found the problem.

Not that there is a limitation to bring large fields, what happened is that the value of that field had included the apostrophe character ('), for some reason it annulled and returned null value.

I remove the apostrophe to the data and went ok.

Roberto

 

[citywest]

Good for you Roberto,

Single quotes in DB's are a menace to my sanity. It's best if you either replace them on the way in and re-replace on the way out or Escape them.

Have a nice weekend,

Mark S.

 

[rclatorre]

Thanks Mark

I will do so

Roberto