[SOLVED] SC7 & SC8: Update Statement wrong when field name is numeric

Scriptcase 8 Bugs
#1

Hi
i have a table with tinyint(1) fields (Yes/No Checkboxes) where the field names are numbers like 2014, 2013, 2012.
In this case, Scriptcase 8.00.0007 generates invalid SQL Statements (…2014 = 0, 2013 = 0, 2012 = 0, 2011 = 0,…)

This is not allowed. It schould be (2014=0, …). Why you not put every fieldname in ? Filed names with spaces like "This fieldname" you put allready in .

Thank’s for fix soon. Because it is a database other people use also, i not can change fieldnames.

Best regards
Steve

UPDATE: Also tested in SC7, same problem.

#2

If you go to the sql section can you apply the quotes to the fieldnames?

#3

Aparently it is an adodb issue: http://bugs.mysql.com/bug.php?id=50180
A hint on the fix may be here: https://github.com/ADOdb/ADOdb/blob/master/drivers/adodb-mysql.inc.php
you would need to edit the file then, this one is not protected with zend guard. I havent tested it so you would need to do that yourself.

Scriptcase is not to blame on this one I think…

#4

Who can i do?

Hi Albert
this sql generated from SC:

UPDATE User SET  2014 = 1, 2013 = 0, 2012 = 0, 2011 = 0 WHERE id = 919

This not work.
When i change to this:

UPDATE User SET  `2014` = 1, `2013` = 0, `2012` = 0, `2011` = 0 WHERE id = 919

then it works. Who generates this SQL Code? SC or the adodb stuff? Who can i fix it?

Best regards and thanks
Steve

@rr: I think the link you posted describes another problem, doesn’t it?

#5

When you create a form then the fields are extracted from the table and put into the sql section. Did you correct the ’ there? Or did you changed the generated code? If I understand rr correct then this problem is not easy solvable, so I would change the fields section myself. If there’s a reserved word then sc puts quotes around them. The enhancement request could be to do the same for numeric fieldnames.

But point is that identifiers need to start with a letter. So actually the ansi standard was not followed. So I consider this not as a bug. I understand that you can’t change names so if it works for you to quote the fields in the sql section then I consider it solved. But you can always propose an enhancement request for quoting number fieldnames.

#6

Hi Albert
i copied the sql Statement from the error message to phpmyadmin and put the quotes there.

Where can i put the quotes in the sql section. I think in the grid i can, but in the form?

Identifiers can be, when quoted, like that: see http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

I not understand why the code who make the sql statement not always use quote… that’s always a good idea. The code put the quotes as you said when for example a space is in the field name…

Many thank’s for your help. I’am happy with a workaround also. But i can not change the field names… sorry.

Best regards
Steve

#7

[QUOTE=stephanw;27075]Hi Albert
i copied the sql Statement from the error message to phpmyadmin and put the quotes there.

Where can i put the quotes in the sql section. I think in the grid i can, but in the form?

Identifiers can be, when quoted, like that: see http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

I not understand why the code who make the sql statement not always use quote… that’s always a good idea. The code put the quotes as you said when for example a space is in the field name…

Many thank’s for your help. I’am happy with a workaround also. But i can not change the field names… sorry.

Best regards
Steve[/QUOTE]

Otherwise, congratulations to the genious who used “years” as field names. :slight_smile:

#8

It’s not the topic to talk about, how much sense this field names make. Fact is, the field names are allowed. But why sc not uses quotes to field names? And how can i do a workaround for that.

I really thank’s for serious solution… and yes, giu, i also not make field names like that. But someone did and the database is used by many “clients” and works with any other systems who access this db, like Access via ODBC and http://www.sqlmaestro.com for example… i think good for sc, if it supports any fieldnames who are allowed. Agree?

#9

[QUOTE=stephanw;27081]It’s not the topic to talk about, how much sense this field names make. Fact is, the field names are allowed. But why sc not uses quotes to field names? And how can i do a workaround for that.

I really thank’s for serious solution… and yes, giu, i also not make field names like that. But someone did and the database is used by many “clients” and works with any other systems who access this db, like Access via ODBC and http://www.sqlmaestro.com for example… i think good for sc, if it supports any fieldnames who are allowed. Agree?[/QUOTE]

I forgot to add on my topic, [OFFTOPIC]

#10

I was pretty convinced that you could change fieldnames, but this is only possible at grids. For now the only thing I can think of is to create a view where you use your fieldnames not starting with a number.

#11

Hi Albert
thank’s for this idea. Works and does the trick for me!

But i hope, they will fix this soemtime, because i have to do many frontends for existing DB’s for customers. And i wish that SC support every fieldname allowed.

Best regards
Steve

#12

Closed as solved.

#13

Well, I stay with the idea that numeric fieldnames are not sql standard so I consider this as a bad design. I know that that doesn’t help you a lot as you have to deal with it, but I think there are more important issues to fix currently … But good that the issue is fixed.

#14

I open again the thread, sorry, didn’t noticed it was in Bugs forum.

Otherwise, Stephan, When you post a bug, best way is to report this bug too to bugs@scriptcase.net, and if you want add in your mail a link to the thread.

#15

Giu, why ‘close’ a thread? If you need to imho the best way is to close it as a moderator so that the thread cannot be replied upon. But I don’t think that we want another standard answer like ‘send to bugteam’, ‘thread closed’ etc. :wink:

#16

On forums, normally, threads has a start and an end. If someone start a thread with a question, must be closed when solved to avoid necroposting(*)

On sites like this, has no sense to revive a question from some months ago where patches and workaround are live every week, and if some question is asked and “solved”, has no sense either have the thread open to others to revive, better a new post.

An exception, obviously, is a bug, because we must give time to Bartho to “report to bugs team” :slight_smile: (just kidding Bartho, don’t be angry :rolleyes:)

Note: I closed as moderator, but you writed because you are moderator too.

#17

:stuck_out_tongue: You got me. Didn’t see that. Appearantly I can write on closed subjects because I’m an admin. Sorry Giu, my fault. I owe you a beer (or two).

#18

Hello,

This is a bug and has been reported for the development sector for the solution being added to future releases.

Many thanks for the information!

#19

Hello,

This problem has solved in last Scriptcase Update. (8.0.015)

Thank you!