Automatic Timestamp convesrion

[jjeffman]

Hello,

In the Help file there is a mention on the "Unified SQL" topic where we can find the text:

SELECT * FROM emp WHERE HIREDATE>{date '1982-01-15'}

How should I change it to acomplish Timestamp values ('1982-01-15 23:32:07.869')accordind to the Provider used to connect to the database?

Thank you very much.

Jayme Jeffman

[Stellar]

The universal format for writing the timestamp value when using macros is "yyyy-mm-dd hh:mm:ss:zzz". For example, the macro for timestamp with the value '1982-01-15 23:32:07.869' looks like this:

Code: Select all

SELECT * FROM emp WHERE HIREDATE > {timestamp '1982-01-15 23:32:07:869'}

[jjeffman]

Thank you very much for answering me.

I attempted to use

{ TIMESTAMP '2018-10-16 06:32:30.163'}

but it is raising "empty macro or function name" error.

I am connecting to a SQL Server using

Code: Select all

ProviderName = "SQL Server";
SpecificOptions->Values["Provider"] = "prDirect";
SpecificOptions->Values["Authentication"] = "auServer";

Connections to SQL Server using prDirect do not support this kind of macros ?

Thank you very much.

Jayme Jeffman

[jjeffman]

I have changed the connection provider to prNativeClient but nothing has changed. I am still getting macros errors.

The SQL before the EndUpdate() command loks like bellow:

Code: Select all

INSERT INTO &tablename    
	(INICIO              
	,ALARMEIN           
	,TERMINO            
	,DURACAO             
	,ENVIOABERTURA       
	,ENVIOFECHAMENTO     
	,INTERVBUSCAINI      
	,INTERVBUSCAFIM      
	,BUSCAFALINF         
	,BUSCAFALSUP         
	,TRATAR              
	,ENVIAREMAIL         
	,MODULO_ID)          
VALUES                   
	({ TIMESTAMP '2018-10-16 06:32:30.163'} 
	,''                                                   
	,{ TIMESTAMP '1899-12-30 00:00:00.000'} 
	,0.00 
	,{ TIMESTAMP '1899-12-30 00:00:00.000'}   
	,{ TIMESTAMP '1899-12-30 00:00:00.000'}  
	,{ TIMESTAMP '2018-10-16 06:32:35.677'}   
	,{ TIMESTAMP '1899-12-30 00:00:00.000'}   
	,0.00 
	,0.00
	, 'S' 
	,'N' 
	,27)

[Stellar]

Macros work regardless of the selected provider. Below is a sample of adding a record to a table using macros:

The table creation script:

Code: Select all

CREATE TABLE [dbo].[Test] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[f_datetime] [datetime] NULL 	
)

Adding a record to a table using macros:

Code: Select all

  UniQuery1->SQL->Text = "INSERT INTO &tablename (f_datetime) VALUES ({TIMESTAMP '1982-01-15 23:32:07.861'}) ";
  UniQuery1->MacroByName("tablename")->Value = "Test";
  UniQuery1->Execute();

[jjeffman]

Do you think the problem might be I am enclosing the SQL definition in a Begin-EnUpdate block ?

Code: Select all

UniQuery->SQL->BeginUpdate();
UniQuery->SQL->Clear();
UniQuery->SQL->Add("INSERT INTO &tablename    \
	(INICIO             \ 
	,ALARMEIN          \ 
	,TERMINO           \ 
	,DURACAO            \ 
	,ENVIOABERTURA   \    
	,ENVIOFECHAMENTO   \     
	,INTERVBUSCAINI         \
	,INTERVBUSCAFIM      \
	,BUSCAFALINF         \
	,BUSCAFALSUP         
	,TRATAR              \
	,ENVIAREMAIL       \  
	,MODULO_ID)        \  
VALUES                   \
	({ TIMESTAMP '2018-10-16 06:32:30.163'}  \
	,''                                                   \
	,{ TIMESTAMP '1899-12-30 00:00:00.000'}  \
	,0.00 \
	,{ TIMESTAMP '1899-12-30 00:00:00.000'}   \
	,{ TIMESTAMP '1899-12-30 00:00:00.000'}  \
	,{ TIMESTAMP '2018-10-16 06:32:35.677'}   \
	,{ TIMESTAMP '1899-12-30 00:00:00.000'}   \
	,0.00 \
	,0.00\
	, 'S' \
	,'N' \
	,27)");
UniQuery->SQL->EndUpdate(); // I am getting exception at this point

PS.: I have commented the BeginUpdate and EndUpdate statements and it remains raising exception.

I have edited this post to add some information you might need.

I am using C++ Builder 10.2 and UniDAC 7.1.4 running on Windows 10 Professional.

I am connecting to an SQL Server Express version 14.0.1000.169 ( MSSQL 2017 ).

Thank you for any help.

Jayme Jeffman

[jjeffman]

I have tried the code bellow:

Code: Select all

		AddLogEntry("Numero de Macros: "+IntToStr(BDUNPVD->UDB->Macros->Count), evtInformation);
		for( int i=0; i<BDUNPVD->UDB->Macros->Count; ++i)
		{
			AddLogEntry("Macro[ "+IntToStr(i)+" ]= "+ BDUNPVD->UDB->Macros->Items[i]->Name, evtInformation);
    }

And the result was :

[ 2018-11-14 17:18:58.086 ] - Provider Name=SQL Server;Provider=TDS;Data Source=LOCALHOST\SQLEXPRESS;User ID=SA;Password=senha;Pooling=True;Connection LifeTime=120000;Max Pool Size=10;Validate Connection=True
[ 2018-11-14 17:18:58.143 ] - Numero de Macros: 0

Is there a way of checking if the predefined macros are present or available? It seems that there is some TUniConnection parameter not set.

Thank you very much.

Jayme Jeffman

[Stellar]

It is possible to use the BeginUpdate() ... EndUpdate() statement, but macro values should be set only after finishing editing the SQL statement.
that is, after executing the EndUpdate() method. This limitation is due to the fact that macros are formed after applying SQL statement editing, for example:

Code: Select all

UniQuery1->SQL->BeginUpdate();
try {
  UniQuery1->SQL->Clear();
  UniQuery1->SQL->Text = "INSERT INTO &tablename (f_datetime) VALUES ({TIMESTAMP '1982-01-15 23:32:07.861'}) ";
} __finally {
  UniQuery1->SQL->EndUpdate();
}
UniQuery1->MacroByName("tablename")->Value = "Test";
UniQuery1->Execute();

However, predefined macros are not added to the list of connection macros. But you can use the provider name to detect the DBMS and change the program behavior, for example:

Code: Select all

if  UniConnection.ProviderName = 'Oracle' then
  UniConnection.MacroByName('tablename').Value := 'dept'
else
if  UniConnection.ProviderName = 'MySql' then
  UniConnection.MacroByName('tablename').Value := 'test.dept';

[jjeffman]

Hello Stellar.

I understand all your answers about macros created by the user.
As a matter of fact my problem is related to the predefined UniDAC macros.

I have changed my code using one of the options available for the tablename macro and the code still raises an exception at the EndUpdate method call.
It seems that the {timestamp } macro is not available in this version.

UnWork->SQL->BeginUpdate();
UnWork->SQL->Clear();

UnWork->SQL->Add("INSERT INTO HST2_OCORRENCIA \

(INICIO \
,ALARMEIN \
,TERMINO \
,DURACAO \
,ENVIOABERTURA \
,ENVIOFECHAMENTO \
,INTERVBUSCAINI \
,INTERVBUSCAFIM \
,BUSCAFALINF \
,BUSCAFALSUP \
,TRATAR \
,ENVIAREMAIL \
,MODULO_ID) \
VALUES \
({ timestamp '"+Ocorrencia.Inicio.FormatString("yyyy-mm-dd hh:nn:ss.zzz")+"'} \
,'"+Ocorrencia.AlarmeIN+"' \
,{ timestamp '"+Ocorrencia.Termino.FormatString("yyyy-mm-dd hh:nn:ss.zzz")+"'} \
,"+StringReplace(FloatToStrF(Ocorrencia.Duracao,ffFixed,10,2),",",".",TReplaceFlags()<<rfReplaceAll
)+" \
,{ timestamp '"+Ocorrencia.EnvioAbertura.FormatString("yyyy-mm-dd hh:nn:ss.zzz")+"'} \
,{ timestamp '"+Ocorrencia.EnvioFechamento.FormatString("yyyy-mm-dd hh:nn:ss.zzz")+"'} \
,{ timestamp '"+Ocorrencia.E3TimeStampIni.FormatString("yyyy-mm-dd hh:nn:ss.zzz")+"'} \
,{ timestamp '"+Ocorrencia.E3TimeStampFim.FormatString("yyyy-mm-dd hh:nn:ss.zzz")+"'} \
,"+StringReplace(FloatToStrF(Ocorrencia.E3LimFalInf,ffFixed,10,2),",",".",TReplaceFlags()<<rfReplaceAll
)+" \
,"+StringReplace(FloatToStrF(Ocorrencia.E3LimFalSup,ffFixed,10,2),",",".",TReplaceFlags()<<rfReplaceAll
)+" \
, '"+(Ocorrencia.Tratar ? "S" : "N") +"' \
,'"+(Ocorrencia.EnviarEmail ? "S" : "N") +"' \
,"+ IntToStr(Ocorrencia.ModuloId)+")");
}
UnWork->SQL->EndUpdate();

Please read carefully my post.
I can not afford an upgrade of UniDAC current version, and if the issue I am facing is a bug of the version I am using I would like a workaround to fix the issue.

Thank you very much.



Jayme Jeffman

[Stellar]

Please try removing the spaces between the opening brace and the macro name. For example, replace the line:

Code: Select all

,{ timestamp '"+Ocorrencia.EnvioAbertura.FormatString("yyyy-mm-dd hh:nn:ss.zzz")+"'} \

with

Code: Select all

,{timestamp '"+Ocorrencia.EnvioAbertura.FormatString("yyyy-mm-dd hh:nn:ss.zzz")+"'} \

[jjeffman]

Thank you very much for your time.

It is finally working.

Thank you so much.

Best regards.

Jayme Jeffman

[Stellar]

Glad to see that the issue was resolved.
Feel free to contact us if you have any additional questions about our products.