Having problems in a vbscript with creating a query to retreive information from an msi.
Here's the base query
SELECT `File`.`FileName`, `File`.`Component_`, `File`.`Version`, `ModuleFiles`.`ModuleName` FROM `File`, `ModuleFiles` WHERE `File`.`FileName` = `ModuleFiles`.`FileName`
this works fine, ModuleFiles is a custom table, that basically contains data about all existing mergemodules that are available to use in the project, such as module names, filenames, filesize, fileversions. the query currently returns the filename, component, fileversion, and corresponding module name for all files in the file table that exist in the mergemodules that are being used for the project.
Thats all fine, but I wanted to refine the query so that at the same time it would eliminate any records from the list where the corresponding component to the file is found in the Component field of the ModuleComponents. Unfortunately it won't accept the query, and the vbscript crashes.
SELECT `File`.`FileName`, `File`.`Component_`, `File`.`Version`, `ModuleFiles`.`ModuleName` FROM `File`, `ModuleFiles`, `ModuleComponents` WHERE `File`.`FileName` = `ModuleFiles`.`FileName` AND `File`.`Component_` <> `ModuleComponents`.`Component`
All the checks to make sure that the relevant tables exist in the msi have already been completed.
Now what really bugs me is the if you change the last part of the condition to equate to instead of not equating it accepts the query fine. So this next identical query, except for the <> changing to a =, works perfectly fine.
SELECT `File`.`FileName`, `File`.`Component_`, `File`.`Version`, `ModuleFiles`.`ModuleName` FROM `File`, `ModuleFiles`, `ModuleComponents` WHERE `File`.`FileName` = `ModuleFiles`.`FileName` AND `File`.`Component_` = `ModuleComponents`.`Component`
Can anyone explain why, and in turn provide me with a solution or even point to where I can figure out the solution to be able to do what I want to do. Both fields in question are strings which means that according to the msdn I can only use = or <>, but in this case its refusing to allow the <>. Its not a circular query either with is not supported in the SQL engine used in windows installer.
It will be quiet annoying if I can't get the required results with a single query since any additional processing would be relatively inefficient particularly when working on the larger applications.
Thanks in advance for any help
This is a ready-only archive of the InstallSite Forum. You cannot post any new content here. / Dies ist ein Archiv des InstallSite Forums. Hier können keine neuen Beiträge veröffentlicht werden.
Problems with SQL Query of MSI
Started by
felix
, Jan 09 2004 23:12
5 replies to this topic
Posted 10 January 2004 - 15:07
Did you try with parentheses:
SELECT `File`.`FileName`, `File`.`Component_`, `File`.`Version`, `ModuleFiles`.`ModuleName` FROM `File`, `ModuleFiles`, `ModuleComponents` WHERE (`File`.`FileName` = `ModuleFiles`.`FileName`) AND (`File`.`Component_` <> `ModuleComponents`.`Component`)
SELECT `File`.`FileName`, `File`.`Component_`, `File`.`Version`, `ModuleFiles`.`ModuleName` FROM `File`, `ModuleFiles`, `ModuleComponents` WHERE (`File`.`FileName` = `ModuleFiles`.`FileName`) AND (`File`.`Component_` <> `ModuleComponents`.`Component`)
Stefan Krüger
InstallSite.org twitter facebook
Posted 10 January 2004 - 20:32
tried that, didn't appear to make any difference. What's really bugging me is why the query with just the = swaped for <> works, but the one using <> doesn't.
Edited by felix, 10 January 2004 - 20:41.
Posted 13 January 2004 - 00:39
According to the Platform SDK (ms-help://MS.PSDK.1033/msi/setup/sql_syntax.htm)
A WHERE {operation-list} clause is optional and is a grouping of operations to be used to filter the selection. The operations must be of the following types:
{column} = {column}
{column} = | <> | > | < | >= | <= {constant}
{column} = | <> | > | < | >= | <= {marker}
{column} is null
{column} is not null
Column to column not equals comparisons are not supported.
A WHERE {operation-list} clause is optional and is a grouping of operations to be used to filter the selection. The operations must be of the following types:
{column} = {column}
{column} = | <> | > | < | >= | <= {constant}
{column} = | <> | > | < | >= | <= {marker}
{column} is null
{column} is not null
Column to column not equals comparisons are not supported.
Posted 13 January 2004 - 09:23
Doh! It would appear that despite reading the documentation I am totally blind.
Thanks for pointing that out, although I really should have spotted it before now.
Thanks for pointing that out, although I really should have spotted it before now.
Posted 29 January 2004 - 21:30
One point to remember is that there is no implied join between tables so you cannot ask for records that do not match a criteria until you have actually retrieved the record.