Board index » delphi » SQL Union access violation...

SQL Union access violation...

I am trying to use the sql union function and am getting a access
violation 4C5F3168 involving the IDSQL32.dll.  The file input and out
put for both sections of the sql are the same.  They run fine
independently.  All I did was to take a working sql and copy it, change
some of the selection parameters and add a 'sql.add('union')'
statement.  Any suggestions?  Thanks
 

Re:SQL Union access violation...


It has to to with including conditional statements in the sql.  When I
include the following selection criteria  in both sections of the union, I
get the error.  If I comment it out of the 'union' section, runs OK.
However, I need the selection criteria in both sections.

        if gsRecord.gsSalesman <> '' then
        begin
          sql.add('and customer.salesman = :sqlSalesman');
          parambyname('sqlSalesman').asString := gsRecord.gsSalesman;
        end;

Quote
Rob Rietow wrote:
> I am trying to use the sql union function and am getting a access
> violation 4C5F3168 involving the IDSQL32.dll.  The file input and out
> put for both sections of the sql are the same.  They run fine
> independently.  All I did was to take a working sql and copy it, change
> some of the selection parameters and add a 'sql.add('union')'
> statement.  Any suggestions?  Thanks

Re:SQL Union access violation...


Quote
>It has to to with including conditional statements in the sql.  When I
>include the following selection criteria  in both sections of the union, I
>get the error.  If I comment it out of the 'union' section, runs OK.
>However, I need the selection criteria in both sections.

>        if gsRecord.gsSalesman <> '' then
>        begin
>          sql.add('and customer.salesman = :sqlSalesman');
>          parambyname('sqlSalesman').asString := gsRecord.gsSalesman;
>        end;

Let us see the full code.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:SQL Union access violation...


Code follows:

    // get year budgets
    decodeDate(r8PeriodEnd,yyear2,mmon2,dday2);
    decodeDate(r8YearBegin,yyear,mmon,dday);
    close;
    sql.clear;
    if mmon <> mmon2 then
    begin
      sql.add('select customer,grossSales,salesman');
      sql.add('from budget,customer');
      sql.add('where customer.code = budget.customer');
      sql.add('and budget.''year'' = :sqlyear');
      parambyname('sqlyear').asInteger := yyear;
      sql.add('and budget.''month'' >= :sqlmon');
      parambyname('sqlmon').asInteger := mmon;
      sql.add('and budget.''month'' <= :sqlmon2');
      if mmon < mmon2 then
        parambyname('sqlmon2').asInteger := 12
      else
        parambyname('sqlmon2').asInteger := mmon2-1;
      if gsRecord.gsSalesman <> '' then
      begin
        sql.add('and customer.salesman = :sqlSalesman');
        parambyname('sqlSalesman').asString := gsRecord.gsSalesman;
      end;
      if gsRecord.gsCustomer <> '' then
      begin
        sql.add('and budget.Customer = :sqlCustomer');
        parambyname('sqlCustomer').asString := gsRecord.gsCustomer;
      end;
      if mmon < mmon2 then
      begin
        sql.add('union');
        sql.add('select customer,grossSales,salesman');
        sql.add('from budget,customer');
        sql.add('where customer.code = budget.customer');
        sql.add('and budget.''year'' = :sqlyear');
        parambyname('sqlyear').asInteger := yyear2;
        sql.add('and budget.''month'' >= 1');
        sql.add('and budget.''month'' <= :sqlmon2');
        parambyname('sqlmon2').asInteger := mmon2-1;
        if gsRecord.gsSalesman <> '' then
        begin
          sql.add('and customer.salesman = :sqlSalesman2');
          parambyname('sqlSalesman2').asString := gsRecord.gsSalesman;
        end;
        if gsRecord.gsCustomer <> '' then
        begin
          sql.add('and budget.Customer = :sqlCustomer2');
          parambyname('sqlCustomer2').asString := gsRecord.gsCustomer;
        end;
      end;
      open;

Quote
Brian Bushay TeamB wrote:
> >It has to to with including conditional statements in the sql.  When I
> >include the following selection criteria  in both sections of the union, I
> >get the error.  If I comment it out of the 'union' section, runs OK.
> >However, I need the selection criteria in both sections.

> >        if gsRecord.gsSalesman <> '' then
> >        begin
> >          sql.add('and customer.salesman = :sqlSalesman');
> >          parambyname('sqlSalesman').asString := gsRecord.gsSalesman;
> >        end;

> Let us see the full code.

> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:SQL Union access violation...


Hi,

Quote
Rob Rietow wrote:

[...]
>       if mmon < mmon2 then
>       begin
>         sql.add('union');
>         sql.add('select customer,grossSales,salesman');
>         sql.add('from budget,customer');
>         sql.add('where customer.code = budget.customer');
>         sql.add('and budget.''year'' = :sqlyear');
>         parambyname('sqlyear').asInteger := yyear2;
>         sql.add('and budget.''month'' >= 1');
>         sql.add('and budget.''month'' <= :sqlmon2');
>         parambyname('sqlmon2').asInteger := mmon2-1;
>         if gsRecord.gsSalesman <> '' then
>         begin
>           sql.add('and customer.salesman = :sqlSalesman2');
>           parambyname('sqlSalesman2').asString := gsRecord.gsSalesman;
>         end;
>         if gsRecord.gsCustomer <> '' then
>         begin
>           sql.add('and budget.Customer = :sqlCustomer2');
>           parambyname('sqlCustomer2').asString := gsRecord.gsCustomer;
>         end;
>       end;
>       open;

Ups, my last post was corrupt.
(I've tested a new Newsreader... failed :-)

I repeat me:
As far as I know, you can not use params for UNION statements - at least
for Paradox. I do not know the reason for this restriction.

Furthermore, why do you use an UNION statement here? You should get the same
result set using a WHERE expression with OR-clauses.

SELECT customer, grossSales, salesman
FROM budget, customer
WHERE (customer.code = budget.customer)
  AND (<condition of select 1> OR <condition of select 2>)

Regards,
Marco

Re:SQL Union access violation...


Quote
>Code follows:

My guess is that the error is occurring when the query is not complete when you
set parameters.

Quote
>      if gsRecord.gsSalesman <> '' then
>     begin
>        sql.add('and customer.salesman = :sqlSalesman');
>        parambyname('sqlSalesman').asString := gsRecord.gsSalesman;
>      end;
>      if gsRecord.gsCustomer <> '' then
>      begin
>        sql.add('and budget.Customer = :sqlCustomer');
>        parambyname('sqlCustomer').asString := gsRecord.gsCustomer;
>      end;

Try changing the above logic so that the setting the two parameter values comes
after the two SQL Add

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:SQL Union access violation...


Are you saying that it should work?  What about Marco's comment above?
Quote
Brian Bushay TeamB wrote:
> >Code follows:

> My guess is that the error is occurring when the query is not complete when you
> set parameters.

> >      if gsRecord.gsSalesman <> '' then
> >     begin
> >        sql.add('and customer.salesman = :sqlSalesman');
> >        parambyname('sqlSalesman').asString := gsRecord.gsSalesman;
> >      end;
> >      if gsRecord.gsCustomer <> '' then
> >      begin
> >        sql.add('and budget.Customer = :sqlCustomer');
> >        parambyname('sqlCustomer').asString := gsRecord.gsCustomer;
> >      end;

> Try changing the above logic so that the setting the two parameter values comes
> after the two SQL Add

> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:SQL Union access violation...


Quote
>Are you saying that it should work?

I am taking a guess at what the problem is.  The code is a bit too strange to
declare working with out testing it

Quote
> What about Marco's comment above?

What about it?  He is wrong about being able to use parameters with a Union
query

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:SQL Union access violation...


Quote
Brian Bushay TeamB wrote:

> > What about Marco's comment above?
> What about it?  He is wrong about being able to use parameters with a Union
> query

:-)

You are right. Someone has told me garbage. I've tested it with the demo tables
and it works fine.

with Query1 do begin
  SQL.Text := 'SELECT * FROM "VENUES.DB" Venues, "EVENTS.DB" Events';
  SQL.Add('WHERE (Events.VenueNo = Venues.VenueNo) AND (Events.VenueNo =
:Param0)');
  ParamByName('Param0').AsSmallInt := 2;
  SQL.Add('UNION');
  SQL.Add('SELECT * FROM "VENUES.DB" Venues, "EVENTS.DB" Events');
  SQL.Add('WHERE (Events.VenueNo = Venues.VenueNo) AND (Events.VenueNo =
:Param1)');
  ParamByName('Param1').AsSmallInt := 5;
  Open;
end;

Sorry for the disorientation, Rob.

Regards,
Marco

Re:SQL Union access violation...


I have simplified the code.  I should run against any table with the appropriate
field and table names.  Note the assignment of  'wsalesman' at the beginning of
the procedure.  If this is not blank, I get the error.  If I make wsalesman = ''
then the code runs ok.

procedure TForm1.Button12Click(Sender: TObject);
var
  wsalesman: string;
begin
  wsalesman := 'HANS';
  mmon := 10;

  with Query1 do
  begin
    close;
    sql.clear;
    sql.add('select code');
    sql.add('from customer');
    if mmon = 10 then
    begin
      sql.add('union');
      sql.add('select code');
      sql.add('from customer');
      sql.add('where code < ''4000'''); // any where statement so 'and'
conditional can be used
      if wSalesman <> '' then
      begin
        sql.add('and customer.salesman = :sqlSalesman');
        parambyname('sqlSalesman').asString := wsalesman;
      end;
    end;
    open;
  end;
  screen.cursor := crDefault;
end;

Quote
Brian Bushay TeamB wrote:
> >Are you saying that it should work?
> I am taking a guess at what the problem is.  The code is a bit too strange to
> declare working with out testing it

> > What about Marco's comment above?
> What about it?  He is wrong about being able to use parameters with a Union
> query

> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:SQL Union access violation...


Quote
>I have simplified the code.  I should run against any table with the appropriate
>field and table names.  Note the assignment of  'wsalesman' at the beginning of
>the procedure.  If this is not blank, I get the error.  If I make wsalesman = ''
>then the code runs ok.

Rob

It helps to have clear code that I can use with out a lot of work at my end.
I tested your code after adding Code and Salesman to an exiting Customer table.
When I test it works with no errors.
No where in this thread can I find what version of Delphi you are using So I
assume it is 4.  I am testing with Delphi 4 all patches and BDE updates
installed.   So if you have not installed patch 3 and after that the latest BDE
update I suggest you try that.
Then if you still have a problem create a small extract of the table you are
testing with and try that.  If it fails Email me a copy along with a copy of
this message.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:SQL Union access violation...


I'm using D3.
Quote
Brian Bushay TeamB wrote:
> >I have simplified the code.  I should run against any table with the appropriate
> >field and table names.  Note the assignment of  'wsalesman' at the beginning of
> >the procedure.  If this is not blank, I get the error.  If I make wsalesman = ''
> >then the code runs ok.

> Rob

> It helps to have clear code that I can use with out a lot of work at my end.
> I tested your code after adding Code and Salesman to an exiting Customer table.
> When I test it works with no errors.
> No where in this thread can I find what version of Delphi you are using So I
> assume it is 4.  I am testing with Delphi 4 all patches and BDE updates
> installed.   So if you have not installed patch 3 and after that the latest BDE
> update I suggest you try that.
> Then if you still have a problem create a small extract of the table you are
> testing with and try that.  If it fails Email me a copy along with a copy of
> this message.

> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:SQL Union access violation...


Also, BDE 451.
Quote
Brian Bushay TeamB wrote:
> >I have simplified the code.  I should run against any table with the appropriate
> >field and table names.  Note the assignment of  'wsalesman' at the beginning of
> >the procedure.  If this is not blank, I get the error.  If I make wsalesman = ''
> >then the code runs ok.

> Rob

> It helps to have clear code that I can use with out a lot of work at my end.
> I tested your code after adding Code and Salesman to an exiting Customer table.
> When I test it works with no errors.
> No where in this thread can I find what version of Delphi you are using So I
> assume it is 4.  I am testing with Delphi 4 all patches and BDE updates
> installed.   So if you have not installed patch 3 and after that the latest BDE
> update I suggest you try that.
> Then if you still have a problem create a small extract of the table you are
> testing with and try that.  If it fails Email me a copy along with a copy of
> this message.

> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:SQL Union access violation...


Quote
>I'm using D3.

If you don't learn to provide that information up front you waste a lot of time
for everyone.

I tested with Delphi 3 and BDE 5.1 and had no problem.
Looks like it is time for you to try an updated BDE but you better make sure you
copy of Delphi 3 is update too.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:SQL Union access violation...


Yes, sorry.  I get focused on what I am doing and forget there's a world out there.
Appreciate the help.  Installed 5.10 and it runs.  Any problems going 4.xx to 5.10?
Quote
Brian Bushay TeamB wrote:
> >I'm using D3.

> If you don't learn to provide that information up front you waste a lot of time
> for everyone.

> I tested with Delphi 3 and BDE 5.1 and had no problem.
> Looks like it is time for you to try an updated BDE but you better make sure you
> copy of Delphi 3 is update too.

> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Go to page: [1] [2]

Other Threads