Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » Community » Newbie corner » SQL query generator
SQL query generator [message #32948] Fri, 24 June 2011 14:07 Go to next message
jibe is currently offline  jibe
Messages: 294
Registered: February 2007
Location: France
Experienced Member
Hi,

I want to make a kind of query generator. I'm trying this :

In a dialog box, I have :
- a dropdown list with the fields of my table,
- another dropdown list with possible relations (==, !=, <, <= etc.)
- an EditString where to type the desired value.

This will generate SQL queries like :
SELECT * from MYTABLE where (field) (relation) (value)

ie : SELECT * FROM CLIENTS WHERE NAME == TOTO

I tried this :

void MyApp::Query()
{
	String field, cond, val;
	SqlBool where;

	field = dialog.dl_field.GetValue();
	cond = dialog.dl_cond.GetValue();
	val = dialog.ed_cond.GetData();
	where = NAME == ~val;
	mytable.Query(where);
}

This is working well Smile

But when I change to this
	where = ~field == ~val;

Or this
	where = NAME ~cond ~val;

It's no more working...

I tried many other things, ie grouping field, cond and val in a single String, but I cannot find a way to have it working.

How can I do ? I want to find a way to choose at runtime the complete "where" condition...

[Updated on: Fri, 24 June 2011 14:12]

Report message to a moderator

Re: SQL query generator [message #32951 is a reply to message #32948] Fri, 24 June 2011 18:52 Go to previous messageGo to next message
BioBytes is currently offline  BioBytes
Messages: 307
Registered: October 2008
Location: France
Senior Member
Hi Jibe,

Did you try to build the SqlBool where as follows ?

where = (AsString(~field)==AsString(~val))

I am trying some code lines based on what you wish to do and will come back to you as soon as I have finished.

Kind regards

Biobytes
Re: SQL query generator [message #32952 is a reply to message #32948] Fri, 24 June 2011 22:09 Go to previous messageGo to next message
jibe is currently offline  jibe
Messages: 294
Registered: February 2007
Location: France
Experienced Member
No, it's not working...

I also tried
where = (NAME AsString(~cond) AsString(~val));

but it does not works also...

However, this works :
where = (NAME == AsString(~val));


Have you a better solution ?

Thanks anyway Smile
Re: SQL query generator [message #32953 is a reply to message #32952] Fri, 24 June 2011 23:33 Go to previous messageGo to next message
BioBytes is currently offline  BioBytes
Messages: 307
Registered: October 2008
Location: France
Senior Member
Hi Jibe,

Please find attached files showing a basic application using a sqlite3 database named "cookbook.db" I am using in one of my projects. The files include pictures of the main window showing the 2 droplists and the editString before clicking the "Search" button and after having chosen the table field "NAME" and the ailment to identify ("tomate") and clicked the button "Search". The doc file is the source code (h,cpp and database schema). I prefer to use SqlId as often as possible and did that way in this example.

Hoping this could ne helpful

Let me know

have a nice week-end

Biobytes
Re: SQL query generator [message #32976 is a reply to message #32948] Mon, 27 June 2011 15:54 Go to previous messageGo to next message
jibe is currently offline  jibe
Messages: 294
Registered: February 2007
Location: France
Experienced Member
Hi BioBytes,

Thanks for your kind and efficient help !

Reading your sample, I finally made this :
where = (SqlId)(~field) == ~val;

This works well Smile Finally, the solution was only to cast to the right type !!!

However, I see that you use switch/case to choose the condition. It seems a heavy solution ! Does somebody knows a better way to do that ? Seems better to have :
where = (SqlId)(~field) COND ~val;

where COND is ==, !=; <, > etc. But what type should be COND so that it works ?
Re: SQL query generator [message #32988 is a reply to message #32976] Mon, 27 June 2011 21:49 Go to previous messageGo to next message
BioBytes is currently offline  BioBytes
Messages: 307
Registered: October 2008
Location: France
Senior Member
Hi Jibe,

Happy that I could bring some help. Switch/case seems to be heavy but I think the application is not really slowed going this way.

Meanwhile, I am trying to test that following code:

SqlId FIELD(~fieldsDL);
SqlBool where("Where"+AsString(~opeDL)+AsString(~valueEdit),pr);

SQL *Select(ALL).From(TBLALIMENTS).Where(where);

while(SQL.Fetch())PromptOK(AsString(SQL[0])+" "+AsString(SQL[1]));


The problem is to know what to pass in pr. U++ help tells that pr should be an integer but all my trials fail except when passing pr = 0. In that case, all the records from the table are obtained ignoring the Where condition.

Another way is to use SqlVal but the problem is to get a SqlBool expression from a SqlVal.

I will inform you if I can solve this issue.

Cheers

Biobytes
Re: SQL query generator [message #32990 is a reply to message #32988] Tue, 28 June 2011 08:32 Go to previous message
jibe is currently offline  jibe
Messages: 294
Registered: February 2007
Location: France
Experienced Member
Hi,

BioBytes wrote on Mon, 27 June 2011 21:49

Switch/case seems to be heavy but I think the application is not really slowed going this way.

Yes, it's a good solution ! I was just telling that there could be some "more beautifull" way to code that.

In fact, it's not sure that there is really another solution, as some statements (Like, IsNull) have another syntax...

For me, I'm satisfied with the switch/cas solution, more especially because I need Like, IsNull and there contraries.

Anyway, if somebody has other(s) solution(s), or usefull links to documentation, I'll be happy to know Smile

Thanks again to BioBytes, and good luck for your research ! Smile
Previous Topic: Converting HTML to "normal" text
Next Topic: Foreign Languages - For FileSel, PromptOK, and other
Goto Forum:
  


Current Time: Fri Mar 29 08:03:59 CET 2024

Total time taken to generate the page: 0.01179 seconds