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 » U++ Library support » U++ SQL » Call external funcions in SQL
Call external funcions in SQL [message #40745] Thu, 12 September 2013 18:37 Go to next message
koldo is currently offline  koldo
Messages: 3374
Registered: August 2008
Senior Veteran
Hello all

I wanted to ask you how to call an external function from SQL.

For example, doing:
SQL & ::Select(SqlAll()).From(TABLE).Where(DATE < GetSysTime()));


in the SQL it will always appear the origonal time, so if I call the query one hour later, the time will remain.
What I would need is that GetSysTime() were called every time the query is called.


Best regards
Iñaki
Re: Call external funcions in SQL [message #40747 is a reply to message #40745] Thu, 12 September 2013 19:23 Go to previous messageGo to next message
dolik.rce is currently offline  dolik.rce
Messages: 1789
Registered: August 2008
Location: Czech Republic
Ultimate Contributor

koldo wrote on Thu, 12 September 2013 18:37

Hello all

I wanted to ask you how to call an external function from SQL.

For example, doing:
SQL & ::Select(SqlAll()).From(TABLE).Where(DATE < GetSysTime()));


in the SQL it will always appear the origonal time, so if I call the query one hour later, the time will remain.
What I would need is that GetSysTime() were called every time the query is called.

Hi Koldo,

If you use the query as you posted it should evaluate the GetSysTime() each time you call it. The only problem might be if you stored the query for later or multiple execution. I mean something like:
SqlSelect sel = Select(SqlAll()).From(TABLE).Where(DATE < GetSysTime());
// .. and one hour later or in some function
SQL & sel;
SQL.Fetch();

If you do that, than the easiest way would be to either create the query each time just before executing.

If I misunderstood something about the problem, please post bit more of the code, so I can get better idea what is the problem.

Also, if you really need what the title says, I'm not aware how to call user defined C++ functions in Sql, unless it is SQLite. If you are interested in that, I can post couple examples Smile But it should not be necessary for you, judging by the example code you posted.

Best regards,
Honza
Re: Call external funcions in SQL [message #40751 is a reply to message #40747] Thu, 12 September 2013 20:19 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13986
Registered: November 2005
Ultimate Member
Also worth noting that SQL engines usually have means to specify current time/date (e.g. in PostgreSQL, CURRENT_DATE).

(Perhaps we should somewhat represent it in SqlExp... adding to RM).

Mirek
Re: Call external funcions in SQL [message #40752 is a reply to message #40747] Thu, 12 September 2013 20:29 Go to previous messageGo to next message
Zbych is currently offline  Zbych
Messages: 327
Registered: July 2009
Senior Member
koldo wrote on Thu, 12 September 2013 18:37

SqlSelect sel = Select(SqlAll()).From(TABLE).Where(DATE < GetSysTime());
// .. and one hour later or in some function
SQL & sel;
SQL.Fetch();

If you do that, than the easiest way would be to either create the query each time just before executing.


or let database check current time for you:
Postgres:
SqlSelect sel = Select(SqlAll()).From(TABLE).Where(DATE < SqlTxt("NOW()");

Re: Call external funcions in SQL [message #40756 is a reply to message #40752] Thu, 12 September 2013 23:09 Go to previous messageGo to next message
koldo is currently offline  koldo
Messages: 3374
Registered: August 2008
Senior Veteran
Thank you. You are all right Smile

I have seen

SQLITE
SELECT datetime('now');
SELECT CURRENT_TIMESTAMP

SQL_SERVER
SELECT CURRENT_TIMESTAMP
SELECT {fn NOW()}
SELECT GETDATE()

I would like to add it to Redmine.


Best regards
Iñaki
Re: Call external funcions in SQL [message #40757 is a reply to message #40756] Fri, 13 September 2013 07:58 Go to previous messageGo to next message
koldo is currently offline  koldo
Messages: 3374
Registered: August 2008
Senior Veteran
It is added!. Thank you Mirek.

Best regards
Iñaki
Re: Call external funcions in SQL [message #40761 is a reply to message #40757] Fri, 13 September 2013 13:57 Go to previous messageGo to next message
koldo is currently offline  koldo
Messages: 3374
Registered: August 2008
Senior Veteran
Meanwhile this works in Sqlite:

(DATE_TIME >= SqlTxt("(datetime('now'))")) && 
(DATE_TIME < (SqlTxt("(datetime('now','+5 day'))"))


Best regards
Iñaki
Re: Call external funcions in SQL [message #40889 is a reply to message #40761] Mon, 30 September 2013 12:49 Go to previous message
koldo is currently offline  koldo
Messages: 3374
Registered: August 2008
Senior Veteran
A detail to add

If you use local time, it is necessary to add 'localtime'. For example:

(DATE_TIME < (SqlTxt("(datetime('now','localtime','+5 day'))"))


Best regards
Iñaki
Previous Topic: Added code to MySqlSession::DoConnect() correct?
Next Topic: Strange build error with OleDB
Goto Forum:
  


Current Time: Sun Jun 23 00:06:35 CEST 2024

Total time taken to generate the page: 0.01811 seconds