Problem with SQL - query

[eny]

If you want to, you can give me your e-mail or tell me where to upload & i'll send you a rar with these files.

Just attach it to the posting (don't include exe's and other mammoths).

[timmermanj]

The attachment contains a simple example of the SQL - query i tried in Lazarus.

If you start the app. you can see the table in it's normal condition (standard tabel).

The box beneath the table contains the SQL - query where I've entered the query trying to pivot the table.

You can try opening the query by pushing the 'open sql" button.
or you can try executing the query by pushing the 'exe sql' button.

I've got this SQL - query from the wizard in Access 2003.

P.S. If you open the database with access, you can see the cross-table query that the wizard has generated. This is the one I entered in the box in the app.

If it is a bug, how can this be solved?

Thanks a lot!

[timmermanj]

Anybody any ideas yet?

I'am stuck with my app.

Thanks!

[Lacak2]

If it is a bug, how can this be solved?

IMHO yes it is a bug, but ATM it can be solved only by modifying sources of fcl-db package (sqlDB), as far as you want use TODBCConnection component.

You can post bug report and wait when will be fixed, then download snapshot ... or fix it by your own hands.

search for odbcconn.pas and add this protected inherited method :

function TODBCConnection.StrToStatementType(s : string) : TStatementType;
begin
  S:=Lowercase(s);
  if s = 'transform' then Result:=stSelect //MS Access
  else Result := inherited StrToStatementType(s);
end;

[timmermanj]

I've modified the odbcconn.pas like you said but it's still not working.

I'm still getting the same error that it cannot open a non select statement.

I've added the pas file in the attachment.

Am I forgetting something? I've tried rebuilding Lazarus but still the same wrong result.

Please advice.

Thanks!

[Lacak2]

For me it works with your test application!
But I use Lazarus with FPC 2.5.1 (you use some old version of FPC)

We can try this:
1. copy you modified odbcconn.pas into your application directory and try compile ... you must see, in message window, that odbcconn.pas is compiled
2. if some errors about not found files (for example registersqldb) appears, try add into your Project options / Paths / Search Unit path: $(LazarusDir)\components\sqldb\;
3. if it does not solve problem try download Daily Snapshot with FPC 2.5.1 (or at least 2.4.x) and apply patch there

[timmermanj]

I'm now using the latest Lazarus fix from daily snapshots:

  lazarus-0.9.28.3-26670-20100716-src.zip

The FPC version = 2.4.0

I've rebuild Lazarus again after I replaced the odbccon.pas file.
But still the same result.

Which odbccon.pas are you using? Did it work for you with the one i put in the attachment?
If not could you send me the working odbccon.pas file you are using?

Thanks a lot!
Jeffrey

[timmermanj]

It's working!!!

I've added the odbccon.pas file in the application.
Then i've added the path into the project options.

This didn't work yet.
So after this "disaster" I want to undo these changes.
First i removed the path in the project options.
Then i was about to remove the odbccon.pas from the project inspector.
But instead of removing the odbccon.pas, i removed SQLDBLaz (per accident).

I didn't noticed i removed the wrong file in project inspector.
I recompiled to see if my app works again & i did.
The only difference is can now open transform queries.

Really weird!
Maybe you have a explanation?

Anyway, thanks for all the help!
I really appreciate!

 

[timmermanj]

I've tried something again.

I've added a new TSQLQuery on the form.

This added the SQLDBLaz back into the project inspector.

I've tried compiling again but then i got the error:

registersqldb.pas(14,36) Fatal: Can't find unit registersqldb used by SQLDBLaz

Somehow i doesn't work when this is in the project inspector...

[Lacak2]

I've added a new TSQLQuery on the form.

This added the SQLDBLaz back into the project inspector.

Yes, SQLdbLaz is required package

registersqldb.pas(14,36) Fatal: Can't find unit registersqldb used by SQLDBLaz

As I wrote:
2. if some errors about not found files (for example registersqldb) appears, try add into your Project options / Paths / Search Unit path: $(LazarusDir)\components\sqldb\;

Please add also bug report into bug tracker, with link to this discussion.

[timmermanj]

I've just installed Lazarus with FPC 2.4.3

After adding the adjusted odbccon.pas in the project inspector I don't have any problems running the transform query.

I also don't get following error anymore:

registersqldb.pas(14,36) Fatal: Can't find unit registersqldb used by SQLDBLaz

I will report this bug.

thanks a lot for all the help!!!

[eny]

Good thing it's working now.
Have fun.

(Sorry for the late reply but I had to rebuild my PC; MB and PSU crash  )

[Lacak2]

Reported as bug in bugtracker : http://bugs.freepascal.org/view.php?id=17086