Generic SQL Coding For Server

Does anyone have some advice on how to write a generic SQL statement that will execute on Server?

I have the need to execute a query which could return anywhere from 1 – 51 fields in a table, all based upon the users request. This means I need to generically build this SQL statement to return any number of these 51 fields, but just cannot seem to figure out anyway to make this work.

All works great if I hard-code the SQL statement, but then it cannot be generic. I’ve written code to generically create a text version of the SQL statement, but cannot find a way to execute it without generating errors when it’s run on the server.

For example, here is a hard-coded statement:

Begin SQL
SELECT CONVERT(Varchar(12),[TIMESTAMP], 101), CONVERT(Varchar(12),[TIMESTAMP], 108),
Energy_1,Energy_2,Energy_3
FROM DMM_HISTORIAN
WHERE [TimeStamp]>=(CONVERT(datetime,CONVERT(varchar(30),:vt_temp1), 120))
AND [TimeStamp] <= (CONVERT(datetime,CONVERT(varchar(30),:vt_temp2), 120))
INTO :at_temp1, :at_temp2, :ar_gl_hist_em1, :ar_gl_hist_em2, :ar_gl_hist_em3
End SQL

This will execute perfectly, but if it is built as a text variable, I cannot get it to run. I am looking for a way to make the “SELECT” and “INTO” lines generic.

I’m looking for suggestions from anyone who may have done this…

Thanks!

Steve

Hi Steve,

do you mean that with this,

Code :

$SQLtxt:=" SELECT CONVERT(Varchar(12),[TIMESTAMP], 101), CONVERT(Varchar(12),[TIMESTAMP], 108),"
$SQLtxt:= $SQLtxt+“Energy_1,Energy_2,Energy_3\r”
$SQLtxt:= $SQLtxt+" FROM DMM_HISTORIAN\r"
$SQLtxt:= $SQLtxt+“WHERE [TimeStamp]>=(CONVERT(datetime,CONVERT(varchar(30),:vt_temp1), 120))\r”
$SQLtxt:= $SQLtxt+" AND [TimeStamp] <= (CONVERT(datetime,CONVERT(varchar(30),:vt_temp2), 120))\r"
$SQLtxt:= $SQLtxt+" INTO :at_temp1, :at_temp2, :ar_gl_hist_em1, :ar_gl_hist_em2, :ar_gl_hist_em3;"

Begin SQL
EXECUTE IMMEDIATE :$SQLtxt;
End SQL

it doesn’t work?

Patrick,

I believe I already tried this and could not get it to work on Server. I will try again…

Thank you!

Steve

Don’t forget to set the method to be executed on the server. But, if you do that and expect to get the result from a client, you have to “exchange information” form server to client by
GET PROCESS VARIABLE(-100;Var1;Var1) and SET PROCESS VARIABLE(-100;Var1;Var1)

Patrick,

Yes, the EOS attribute is checked, however I’m using an object as my communication mechanism. This just makes things cleaner in my mind.

Best,

Steve

: Steve ORTH

however I’m using an object as my communication mechanism. This just
makes things cleaner in my mind.

me too, and easier to see in the debugger what there is in.

Patrick

Patrick,

I’m running into the same issues I did before, the call returns nothing and generates the following error:

Error Codes: Calling Method =
Error Desc = Error #2812 (Could not find stored procedure ‘IMMEDIATE’)
Error Code = 2812
Component = 4DRT
Calling Method =
Error Desc = Error #2812 (Could not find stored procedure ‘IMMEDIATE’)
Error Code = 2812
Component = 4DRT
Calling Method =
Error Desc = Error #2812 (Could not find stored procedure ‘IMMEDIATE’)
Error Code = 2812
Component = 4DRT
Calling Method =
Error Desc = Error 9922 (Error returned by the driver via the statement.)
Error Code = 9922
Component = 4DRT

Last time I tried this I was unable to get past this issue…any clue?

Best,

Steve

strange,

just to be sure: Do all variables typed?
I do not understand the error. The ‘execute immediate’ is called between a BEGIN SQL and END SQL, right?
Just asking to see, because there is no more description/information on the error number in the documentation (SQL error).

Patrick

Patrick,

Yes, as far as I can tell everything is correct. Here is the logic:

<code 4D>
$energy_metric:=“Energy_”+String($metric_index)
$energy_array:=":ar_gl_hist_em"+String($metric_index)

vt_temp1:="SELECT CONVERT(Varchar(12),[TIMESTAMP], 101), CONVERT(Varchar(12),[TIMESTAMP], 108), “+$energy_metric+” "
vt_temp1:=vt_temp1+"FROM DMM_HISTORIAN "
vt_temp1:=vt_temp1+"WHERE [TimeStamp]>=(CONVERT(datetime,CONVERT(varchar(30),:vt_temp1), 120)) "
vt_temp1:=vt_temp1+"AND [TimeStamp] <= (CONVERT(datetime,CONVERT(varchar(30),:vt_temp2), 120)) "
vt_temp1:=vt_temp1+“INTO :at_temp1, :at_temp2, “+$energy_array+”;”

Begin SQL
EXECUTE IMMEDIATE :vt_temp1;
End SQL

</code 4D>

Here is what the vt_temp1 variable contains:

SELECT CONVERT(Varchar(12),[TIMESTAMP], 101), CONVERT(Varchar(12),[TIMESTAMP], 108), Energy_1
FROM DMM_HISTORIAN
WHERE [TimeStamp]>=(CONVERT(datetime,CONVERT(varchar(30),:vt_temp1), 120))
AND [TimeStamp] <= (CONVERT(datetime,CONVERT(varchar(30),:vt_temp2), 120))
INTO :at_temp1, :at_temp2, :ar_gl_hist_em1;

The error message is directly from my error handler and what 4D has responded back with. This connection is to MS SQL server I have running, not another 4D database.

Best,

Steve

This connection is to MS SQL server

this is the critical piece of information that was not apparent in your original post.

4D sends the SQL statement “as is”,
so a 4D-specific commands like EXECUTE IMMEDIATE would not pass the ODBC Driver for MS SQL.

you need to use SQL EXECUTE instead of Begin SQL/End SQL.

I figured this was the issue the first time I tried using this. There is a not so clear note in the commands documentation that made me think it would not work.

I’ve been trying to get SQL Execute to work, but this command requires that you physically name the arrays:

 SQL EXECUTE ( sqlStatement {; boundObj}{; boundObj2 ; ... ; boundObjN} ) 

Explain how I can make the “boundOjb” generic?

Thanks!

Steve

I don’t have much experience with SQL EXECUTE,
but you already use variables in your SQL like

INTO :at_temp1, :at_temp2, :ar_gl_hist_em1, :ar_gl_hist_em2, :ar_gl_hist_em3

so there shouldn’t be any difference (it is not mandatory to use the extra parameters)

see “Referencing 4D expressions in SQL requests”

http://doc.4d.com/4Dv17/4D/17/Overview-of-SQL-Commands.300-3730653.en.html

but of course the extra parameters give you more flexibility (the use of local arrays in particular)

perhaps pointers would work too.

what is not flexible is the number of parameters,
but that should not be a prohibitive factor in most cases.

I will give this a try, thank you

Steve

Hi,

Somehow code does not display properly so here it is as exported method…

http://forums.4d.com/4DBB_Main/x_User/4275/files/25246069.zipSQL_executePtrArray method exported as C4D file>

Bruno,

Thanks, I ended up doing something similar to this, however I could not use the SQL EXECUTE command as I’m talking to a MS SQL server.

Best,

Steve