I wish you'd fix this!

Scriptcase 8 Bugs
#1

I first highlighted this bug during the SC5 phase. It applies to MySQL, MS-SQL, Access, SQLlite and seemingly it still hasn’t been fixed.

Here’s the SQL

SELECT [B]Allocations.ID,[/B] Allocations.Area, AllocAreas.UserCode, AllocAreas.name, Allocations.Portion, AllocPortions.UserCode, AllocPortions.name, 
Allocations.Row, AllocRows.UserCode, AllocRows.Name, Allocations.Number, Allocations.`Sub-Number`, Allocations.Interment, Allocations.Status, RegisterData.ID AS RegisterIndex
FROM (AllocRows INNER JOIN (AllocPortions INNER JOIN (AllocAreas INNER JOIN AllocSectionIndex ON AllocAreas.ID = AllocSectionIndex.AREA) 
ON AllocPortions.ID = AllocSectionIndex.PORTION) ON AllocRows.ID = AllocSectionIndex.ROW) INNER JOIN (Allocations LEFT JOIN RegisterData ON Allocations.ID = 
RegisterData.AllocationCode) ON AllocSectionIndex.ID = Allocations.SectionID
ORDER BY Allocations.Area, Allocations.Portion, Allocations.Row, Allocations.Number, Allocations.Interment;

Note the field name in bold. The above runs in SC8’s SQL builder.

At runtime, and error occurs. The first two CHARS are dropped off the first field in the select statement. See attached screen dump. Compare the SQL to the fields list at left.

Aliasing the field has no effect in SC8. Aliasing the table name has no effect in SC8.

Thank you

missingcharbug.jpg

#2

Just a quick update; in earlier versions of SC I was able to alias some fields as a possible work-around. This no longer works in SC8:

 Error
Error while accessing the database:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
SelectLimit(SELECT [B]ocations.ID[/B] as allocid, AllocAreas.name as areaname, AllocPortions.name as portionname, 
AllocRows.Name as rowname, Allocations.`Number` as allocations_number, Allocations.`Sub-Number` as sc_field_0, 
Allocations.Interment as allocations_interment, Allocations.Status as allocations_status, 
RegisterData.RegisterGroupID as registergroup, RegisterData.Surname as registerdata_surname, RegisterData.`Given Names` as sc_field_1, 
RegisterData.Town as registerdata_town, RegisterData.Gender as registerdata_gender, RegisterData.Aged as registerdata_aged 
FROM
 ((((( AllocSectionIndex INNER JOIN Allocations ON AllocSectionIndex.AREA = Allocations.Area AND AllocSectionIndex.PORTION = 
Allocations.Portion AND AllocSectionIndex.ROW = Allocations.Row) INNER JOIN AllocAreas ON AllocSectionIndex.AREA = AllocAreas.ID) 
INNER JOIN AllocPortions ON AllocSectionIndex.PORTION = AllocPortions.ID) INNER JOIN AllocRows ON AllocSectionIndex.ROW = AllocRows.ID) 
LEFT OUTER JOIN RegisterData ON Allocations.ID = RegisterData.AllocationCode) , 12, 0)

It has become a Showstopper!

#3

Everytime I have issues with complex queries in SC, I move the complex query to a new view in the DB engine, so that in SC I can use a very basic:

SELECT * FROM ‘viewname’

If needed, in SC you can always apply a WHERE clause to a SELECT on a view:

SELECT * FROM ‘viewname’ WHERE …

But I don’t know if it applies to your problem.

#4

+1 on robydago’s workaround.

I have found ScriptCase to be very limited on what SQL will, or won’t, work.

On MS SQL and MySQL, you can also make this a stored function or a stored procedure, then call the function from SC8’s SQL. I do this a lot.

#5

[QUOTE=Giblet535;32406]+1 on robydago’s workaround.

I have found ScriptCase to be very limited on what SQL will, or won’t, work.

On MS SQL and MySQL, you can also make this a stored function or a stored procedure, then call the function from SC8’s SQL. I do this a lot.[/QUOTE]

Same here. Sometimes I solve issues like this syntax. select a.field, a.otherfield, b.somefield from table1 a, table 2 b where a.key=b.key

I think that it has something to do with the sql parser of scriptcase.

#6

Hi,
Thanks for the attempt to cheer me up, people. Anyway, the reason the issue got up my goat was that I had an MS-Access db project underway and SC7 didn’t support views. But I see now, my ongoing ODBC problems notwithstanding, SC8 can now process Access’ “queries” as views, which has sort of solved things.
However, I have now used a different work-around, which is not to use complex SQL. Instead, just use the table and use lookups. A little more tedious but much more robust.
Nevertheless, I wish they’d fix it. After all, how hard can it be given it is always the first field in the SQL clause that gets hammered?
Cheers