• Replies 4 replies
  • Subscribers 583 subscribers
  • Views 369 views
  • Users 0 members are here
Options
Related

SQL, how t write correctly a query on cicode

Agostino

Hello,

I received from database manager the request to create this query to cicode to copy data between twp SQL table on the same Database. I cannot find the correct way to write it on cicode,  and other problem, Citect respond me that query is too long.. Someone can help me?

INSERT INTO [_PIZZA_PARAMETRI_RICETTE_HISTORY]

      (PPR_MAC_NUM
      ,PPR_NUMERO_ORDINE
      ,PPR_DATETIME
      ,PPR_RICETTA
      ,PPR_PARAMETRO
      ,PPR_VALORE_A
      ,PPR_VALORE_N)
SELECT
      PPR_MAC_NUM
      ,PPR_NUMERO_ORDINE
      ,PPR_DATETIME
      ,PPR_RICETTA
      ,PPR_PARAMETRO
      ,PPR_VALORE_A
      ,PPR_VALORE_N
  FROM [dbo].[_PIZZA_PARAMETRI_RICETTE]
  WHERE  PPR_MAC_NUM = 1

Thank you

  • You need to call SQLSet multiple times with parts of your query in stages, as citect can't cope with strings longer than 128? Or 256? characters. Use the help for details
  • Assuming you have SQL Server. Instead of creating large SQL statements in Cicode you could create a stored procedure. In Cicode you only need an EXEC {stored procedure name}. So you only have to trigger it at the right moment. Also better from a security point of view. The database manager should be able to help you with creating the stored procedure if you don't have experience with that.
  • Ok. Thank you, I resolved writing the query like below:

    hRec_MA = SQLSet(hSQL, "INSERT INTO [_PIZZA_PARAMETRI_RICETTE_HISTORY](PPR_MAC_NUM,PPR_NUMERO_ORDINE,PPR_DATETIME,PPR_RICETTA,PPR_PARAMETRO,PPR_VALORE_A,PPR_VALORE_N)");

    //Add parameters to the query
    SQLAppend(hSQL, " SELECT PPR_MAC_NUM,PPR_NUMERO_ORDINE,PPR_DATETIME,PPR_RICETTA,PPR_PARAMETRO,PPR_VALORE_A,PPR_VALORE_N FROM [dbo].[_PIZZA_PARAMETRI_RICETTE] WHERE PPR_MAC_NUM =1");
    //Execute the query
    SQLExec(hSQL, "");
    //the locally created query is disposed
    SQLQueryDispose(hSQL);

  • Good to see you resolved it. However you really should be identifying exactly what columns you're inserting values into, otherwise if the table design ever changes (columns added, columns rearranged) your query may no longer work; it's also a good idea for future you or other people looking at the code to be able to easily identify the columns and the data you're writing into the table.

    E. G. Syntax:
    INSERT INTO table
    (col1, col2,...., colX)
    VALUES (col1val, col2val,..., colXval)

    Edit: I edited the sentence as I was clearly lacking sleep...

© 2025 AVEVA Group Limited or its subsidiaries. All Rights Reserved.