This topic is locked

Blank Value on PrepareSQL

5/10/2023 10:18:50 AM
PHPRunner General questions
D
DRCR Dev author

I have this code in the server section of a custom button:
`$record = $button->getCurrentRecord();
$journal_id = $record["id"];
$journal_type = "R" . $record["journal_type"];
$ID = $record["user_id"];
$md5 = substr(uniqid('', true) . uniqid('', true), 0, 32);

$rs = DB::Query("select year, period from current_period where user_id= ".$ID);
$data = $rs->fetchAssoc();
$current_year = $data['year'];
$current_period = $data['period'];

// Delete allocations

$rs3 = DB::Query("SELECT allocation_id FROM ledger WHERE journal_id = '".$journal_id."' AND user_id = ".$ID);
$data3 = array();
while ($row3=$rs3->fetchAssoc()) {
$data3[] = $row3;
}

foreach ($data3 as $row3) {
$allocation_id = $row3["allocation_id"];
$data3 = array();
$keyvalues3 = array();
$keyvalues3["user_id"] = $ID;
$keyvalues3["allocation_id"] = $allocation_id;
$data3["allocated"] = "";
$data3["allocation_id"] = "";
DB::Update("ledger", $data3, $keyvalues3);
}
// End Delete Allocations

// Reverse journal and use reversing code as journal_type

$set = DB::PrepareSQL("
SET @financial_year = $current_year;
SET @financial_period = '$current_period';
SET @journal_id = '$journal_id';
SET @user_id = $ID;
SET @journal_type = '$journal_type';
SET @allocated = '';
SET @vat_return = NULL;
SET @allocate = 0;
SET @allocation_id = '';
");

$sql = DB::PrepareSQL("INSERT INTO ledger (
date_document, type, section, nominal, value, vat, description, reference, company,
journal_id, user_id, financial_year, financial_period, journal_type, allocated, vat_return, allocate, allocation_id, reference2, reference3, attachment
)
SELECT
date_document, type, section, nominal, -1 * value, vat, description, reference, company,
@journal_id, @user_id, @financial_year, @financial_period, @journal_type, @allocated, @vat_return, @allocate, @allocation_id, reference2, reference3, attachment
FROM
ledger
WHERE
journal_id = ':journal_id' AND user_id = :user_id;
");
echo $set;
echo $sql;

try{

DB::Exec ("START TRANSACTION;");
DB::Exec ($set);
DB::Exec ($sql);

DB::Exec ("COMMIT;");

}catch (Exception $e) {

DB::Exec ("ROLLBACK;");
}

// End Reverse journal and use reversing code as journal_typeWhen I print the $set and $sql, everything prints correctly, except the final journal_id = Everywhere else the journal_id is picked up correctly, but in the final WHERE statement it's blank. SET @financial_year = 2023; SET @financial_period = 'P01'; SET @journal_id = '250c6ba7-ef2c-11ed-8057-fa163e777afe'; SET @user_id = 12; SET @journal_type = 'RSALI'; SET @allocated = ''; SET @vat_return = NULL; SET @allocate = 0; SET @allocation_id = ''; INSERT INTO ledger (date_document,type,section,nominal,value,vat,description,reference,company,journal_id,user_id,financial_year,financial_period,journal_type,allocated,vat_return,allocate,allocation_id,reference2,reference3,attachment) SELECTdate_document,type,section,nominal, -1 *value,vat,description,reference,company, @journal_id, @user_id, @financial_year, @financial_period, @journal_type, @allocated, @vat_return, @allocate, @allocation_id,reference2,reference3,attachment` FROM ledger WHERE journal_id = '' AND user_id = 12;
The journal_id is a UUID
I can't figure out what would cause this.

Sergey Kornilov admin 5/10/2023

Instead of :journal_id you need to use $journal_id

D
DRCR Dev author 5/11/2023

Thank you !!!!!! I didn't realise we could do that with this API.
Here is functioing code for anyone needing database API example with TRANSACTIONS and debugging (delete the echos once the code works)
`$record = $button->getCurrentRecord();
$journal_id = $record["id"];
$journal_type = "R" . $record["journal_type"];
$ID = $record["user_id"];
$md5 = substr(uniqid('', true) . uniqid('', true), 0, 32);

$rs = DB::Query("select year, period from current_period where user_id= ".$ID);
$data = $rs->fetchAssoc();
$current_year = $data['year'];
$current_period = $data['period'];

// Delete allocations

$rs3 = DB::Query("SELECT allocation_id FROM ledger WHERE journal_id = '".$journal_id."' AND user_id = ".$ID);
$data3 = array();
while ($row3=$rs3->fetchAssoc()) {
$data3[] = $row3;
}

foreach ($data3 as $row3) {
$allocation_id = $row3["allocation_id"];
$data3 = array();
$keyvalues3 = array();
$keyvalues3["user_id"] = $ID;
$keyvalues3["allocation_id"] = $allocation_id;
$data3["allocated"] = "";
$data3["allocation_id"] = "";
DB::Update("ledger", $data3, $keyvalues3);
}
// End Delete Allocations

// Reverse journal and use reversing code as journal_type

$set = DB::PrepareSQL("
SET @financial_year = $current_year;
SET @financial_period = '$current_period';
SET @journal_id1 = '$journal_id';
SET @user_id1 = $ID;
SET @journal_type = '$journal_type';
SET @allocated = '';
SET @vat_return = NULL;
SET @allocate = 0;
SET @allocation_id = '';
");

$sql = DB::PrepareSQL("INSERT INTO ledger (
date_document, type, section, nominal, value, vat, description, reference, company,
journal_id, user_id, financial_year, financial_period, journal_type, allocated, vat_return, allocate, allocation_id, reference2, reference3, attachment
)
SELECT
date_document, type, section, nominal, -1 * value, vat, description, reference, company,
@journal_id1, @user_id1, @financial_year, @financial_period, @journal_type, @allocated, @vat_return, @allocate, @allocation_id, reference2, reference3, attachment
FROM
ledger
WHERE
journal_id = '".$journal_id."' AND user_id = :user_id;
");

// End Reverse journal and use reversing code as journal_type

// Reverse customer invoices

$set2 = DB::PrepareSQL("SET @journal_id2 = '$journal_id';
SET @user_id2 = $ID;");

$sql2 = DB::PrepareSQL("INSERT INTO customer_invoices (
user_id, company, customer, invoice_number, quantity, item, net, vat,
each_value, item_code, invoice_date, transaction_date, journal_id, po_number
)
SELECT
@user_id2, company, customer, CONCAT('C', invoice_number), -1 quantity, item, -1 net, -1 * vat,
each_value, item_code, invoice_date, transaction_date, @journal_id2, po_number
FROM customer_invoices
WHERE
journal_id = '".$journal_id."' AND user_id = :user_id;
");
// End Reverse customer invoices
// Post Transaction
echo $set;
echo $sql;
echo "END OF 1 START 2";
echo $set2;
echo $sql2;

try{

DB::Exec ("START TRANSACTION;");
DB::Exec ($set);
DB::Exec ($sql);
DB::Exec ($set2);
DB::Exec ($sql2);
DB::Exec ("COMMIT;");

}catch (Exception $e) {

DB::Exec ("ROLLBACK;");
die("Transaction failed: " . $e->getMessage()); // or exit()
}

//End Post Transaction`

D
DRCR Dev author 5/11/2023

This is weird. The $sql and $sql2 print perfect MYSQL code that does exactly as expected in mysql, but the DB::Exec( $sql ); and DB::Exec( $sql2 ); lines do not run.
I've removed transactions for now, while I troubleshoot this. I don't understand what is needed with the DB::Exec code to make valid mysql code run.
`$record = $button->getCurrentRecord();
$journal_id = $record["id"];
$journal_type = "R" . $record["journal_type"];
$ID = $record["user_id"];
$md5 = substr(uniqid('', true) . uniqid('', true), 0, 32);

$rs = DB::Query("select year, period from current_period where user_id= ".$ID);
$data = $rs->fetchAssoc();
$current_year = $data['year'];
$current_period = $data['period'];

// Delete allocations

$rs3 = DB::Query("SELECT allocation_id FROM ledger WHERE journal_id = '".$journal_id."' AND user_id = ".$ID);
$data3 = array();
while ($row3=$rs3->fetchAssoc()) {
$data3[] = $row3;
}

foreach ($data3 as $row3) {
$allocation_id = $row3["allocation_id"];
$data3 = array();
$keyvalues3 = array();
$keyvalues3["user_id"] = $ID;
$keyvalues3["allocation_id"] = $allocation_id;
$data3["allocated"] = "";
$data3["allocation_id"] = "";
DB::Update("ledger", $data3, $keyvalues3);
}
// End Delete Allocations

// Reverse journal and use reversing code as journal_type

$sql = DB::PrepareSQL("
SET @financial_year = $current_year;
SET @financial_period = '$current_period';
SET @journal_id1 = '$journal_id';
SET @user_id1 = $ID;
SET @journal_type = '$journal_type';
SET @allocated = '';
SET @vat_return = NULL;
SET @allocate = 0;
SET @allocation_id = '';
INSERT INTO ledger (
date_document, type, section, nominal, value, vat, description, reference, company,
journal_id, user_id, financial_year, financial_period, journal_type, allocated, vat_return, allocate, allocation_id, reference2, reference3, attachment
)
SELECT
date_document, type, section, nominal, -1 * value, vat, description, reference, company,
@journal_id1, @user_id1, @financial_year, @financial_period, @journal_type, @allocated, @vat_return, @allocate, @allocation_id, reference2, reference3, attachment
FROM
ledger
WHERE
journal_id = '".$journal_id."' AND user_id = :user_id;
");

DB::Exec( $sql );
// Does not execute the $sql

// End Reverse journal and use reversing code as journal_type

// Reverse customer invoices

$sql2 = DB::PrepareSQL("
SET @journal_id2 = '$journal_id';
SET @user_id2 = $ID;
INSERT INTO customer_invoices (
user_id, company, customer, invoice_number, quantity, item, net, vat,
each_value, item_code, invoice_date, transaction_date, journal_id, po_number
)
SELECT
@user_id2, company, customer, CONCAT('C', invoice_number), -1 quantity, item, -1 net, -1 * vat,
each_value, item_code, invoice_date, transaction_date, @journal_id2, po_number
FROM customer_invoices
WHERE
journal_id = '".$journal_id."' AND user_id = :user_id;
");
DB::Exec( $sql2 );
// Does not execute the $sql2

// End Reverse customer invoices

// Test SQL parameters in MYSQL

echo $sql;
// Prints perfect code that works in mysql
echo "END OF 1 START 2";
echo $sql2;
// Prints perfect code that works in mysql

// End Test SQL parameters in MYSQL
`

D
DRCR Dev author 5/11/2023

FINAL CODE THAT WORKS - TRANSACTIONS
I've never worked with transactions so this was a learning experience. Things to consider:

  • Where the transaction starts segregation of mysql commands One part I still dont understand is why some variables are '".$variable". and not :variable Delete the echos once tested - this was a godsend because it sped up knowing what issues to troubleshoot. because of the speed of INSERT into, I have 6 queries that need to optimised to use this method. This is about 10-30 times faster than SELECT and INSERT
    $record = $button->getCurrentRecord();
    $journal_id = $record["id"];
    $journal_type = "R" . $record["journal_type"];
    $ID = $record["user_id"];
    $md5 = substr(uniqid('', true) . uniqid('', true), 0, 32);
    $rs = DB::Query("select year, period from current_period where user_id= ".$ID);
    $data = $rs->fetchAssoc();
    $current_year = $data['year'];
    $current_period = $data['period'];
    // Delete allocations
    $rs3 = DB::Query("SELECT allocation_id FROM ledger WHERE journal_id = '".$journal_id."' AND user_id = ".$ID);
    $data3 = array();
    while ($row3=$rs3->fetchAssoc()) {
    $data3[] = $row3;
    }
    foreach ($data3 as $row3) {
    $allocation_id = $row3["allocation_id"];
    $data3 = array();
    $keyvalues3 = array();
    $keyvalues3["user_id"] = $ID;
    $keyvalues3["allocation_id"] = $allocation_id;
    $data3["allocated"] = "";
    $data3["allocation_id"] = "";
    DB::Update("ledger", $data3, $keyvalues3);
    }
    // End Delete Allocations
    // Reverse journal and use reversing code as journal_type
    $set1 = DB::PrepareSQL("SET @financial_year = $current_year;");
    $set2 = DB::PrepareSQL("SET @financial_period = '$current_period';");
    $set3 = DB::PrepareSQL("SET @journal_id1 = '$journal_id';");
    $set4 = DB::PrepareSQL("SET @user_id1 = $ID;");
    $set5 = DB::PrepareSQL("SET @journal_type = '$journal_type';");
    $set6 = DB::PrepareSQL("SET @allocated = '';");
    $set7 = DB::PrepareSQL("SET @vat_return = NULL;");
    $set8 = DB::PrepareSQL("SET @allocate = 0;");
    $set9 = DB::PrepareSQL("SET @allocation_id = '';");
    $sql = DB::PrepareSQL("INSERT INTO ledger (
    date_document, type, section, nominal, value, vat, description, reference, company,
    journal_id, user_id, financial_year, financial_period, journal_type, allocated, vat_return, allocate, allocation_id, reference2, reference3, attachment
    )
    SELECT
    date_document, type, section, nominal, -1 value, vat, description, reference, company,
    @journal_id1, @user_id1, @financial_year, @financial_period, @journal_type, @allocated, @vat_return, @allocate, @allocation_id, reference2, reference3, attachment
    FROM
    ledger
    WHERE
    journal_id = '".$journal_id."' AND user_id = :user_id;
    ");
    // End Reverse journal and use reversing code as journal_type
    // Reverse customer invoices
    $set21 = DB::PrepareSQL("SET @journal_id2 = '$journal_id';");
    $set22 = DB::PrepareSQL("SET @user_id2 = $ID;");
    $sql2 = DB::PrepareSQL("INSERT INTO customer_invoices (
    user_id, company, customer, invoice_number, quantity, item, net, vat,
    each_value, item_code, invoice_date, transaction_date, journal_id, po_number
    )
    SELECT
    @user_id2, company, customer, CONCAT('C', invoice_number), -1 <em> quantity, item, -1 </em> net, -1
    vat,
    each_value, item_code, invoice_date, transaction_date, @journal_id2, po_number
    FROM customer_invoices
    WHERE
    journal_id = '".$journal_id."' AND user_id = :user_id;
    ");
    // End Reverse customer invoices
    // Test SQL parameters in MYSQL
    echo $set1;
    echo $set2;
    echo $set3;
    echo $set4;
    echo $set5;
    echo $set6;
    echo $set7;
    echo $set8;
    echo $set9;
    echo $sql;
    echo $set21;
    echo $set22;
    echo $sql2;
    // End Test SQL parameters in MYSQL
    // Run Transaction
    DB::Exec ("START TRANSACTION;");
    try{
    DB::Exec( $set1 );
    DB::Exec( $set2 );
    DB::Exec( $set3 );
    DB::Exec( $set4 );
    DB::Exec( $set5 );
    DB::Exec( $set6 );
    DB::Exec( $set7 );
    DB::Exec( $set8 );
    DB::Exec( $set9 );
    DB::Exec( $sql );
    DB::Exec( $set21 );
    DB::Exec( $set22 );
    DB::Exec( $sql2 );
    DB::Exec ("COMMIT;");
    }catch (Exception $e) {
    DB::Exec ("ROLLBACK;");
    die("Transaction failed: " . $e->getMessage()); // or exit()
    }
    // End Run Transaction

D
DRCR Dev author 5/11/2023

Reposted code for easy reading
`$record = $button->getCurrentRecord();
$journal_id = $record["id"];
$journal_type = "R" . $record["journal_type"];
$ID = $record["user_id"];
$md5 = substr(uniqid('', true) . uniqid('', true), 0, 32);

$rs = DB::Query("select year, period from current_period where user_id= ".$ID);
$data = $rs->fetchAssoc();
$current_year = $data['year'];
$current_period = $data['period'];

// Delete allocations

$rs3 = DB::Query("SELECT allocation_id FROM ledger WHERE journal_id = '".$journal_id."' AND user_id = ".$ID);
$data3 = array();
while ($row3=$rs3->fetchAssoc()) {
$data3[] = $row3;
}

foreach ($data3 as $row3) {
$allocation_id = $row3["allocation_id"];
$data3 = array();
$keyvalues3 = array();
$keyvalues3["user_id"] = $ID;
$keyvalues3["allocation_id"] = $allocation_id;
$data3["allocated"] = "";
$data3["allocation_id"] = "";
DB::Update("ledger", $data3, $keyvalues3);
}
// End Delete Allocations

// Reverse journal and use reversing code as journal_type

$set1 = DB::PrepareSQL("SET @financial_year = $current_year;");
$set2 = DB::PrepareSQL("SET @financial_period = '$current_period';");
$set3 = DB::PrepareSQL("SET @journal_id1 = '$journal_id';");
$set4 = DB::PrepareSQL("SET @user_id1 = $ID;");
$set5 = DB::PrepareSQL("SET @journal_type = '$journal_type';");
$set6 = DB::PrepareSQL("SET @allocated = '';");
$set7 = DB::PrepareSQL("SET @vat_return = NULL;");
$set8 = DB::PrepareSQL("SET @allocate = 0;");
$set9 = DB::PrepareSQL("SET @allocation_id = '';");

$sql = DB::PrepareSQL("INSERT INTO ledger (
date_document, type, section, nominal, value, vat, description, reference, company,
journal_id, user_id, financial_year, financial_period, journal_type, allocated, vat_return, allocate, allocation_id, reference2, reference3, attachment
)
SELECT
date_document, type, section, nominal, -1 * value, vat, description, reference, company,
@journal_id1, @user_id1, @financial_year, @financial_period, @journal_type, @allocated, @vat_return, @allocate, @allocation_id, reference2, reference3, attachment
FROM
ledger
WHERE
journal_id = '".$journal_id."' AND user_id = :user_id;
");

// End Reverse journal and use reversing code as journal_type

// Reverse customer invoices

$set21 = DB::PrepareSQL("SET @journal_id2 = '$journal_id';");
$set22 = DB::PrepareSQL("SET @user_id2 = $ID;");

$sql2 = DB::PrepareSQL("INSERT INTO customer_invoices (
user_id, company, customer, invoice_number, quantity, item, net, vat,
each_value, item_code, invoice_date, transaction_date, journal_id, po_number
)
SELECT
@user_id2, company, customer, CONCAT('C', invoice_number), -1 quantity, item, -1 net, -1 * vat,
each_value, item_code, invoice_date, transaction_date, @journal_id2, po_number
FROM customer_invoices
WHERE
journal_id = '".$journal_id."' AND user_id = :user_id;
");

// End Reverse customer invoices

// Test SQL parameters in MYSQL

echo $set1;
echo $set2;
echo $set3;
echo $set4;
echo $set5;
echo $set6;
echo $set7;
echo $set8;
echo $set9;
echo $sql;
echo $set21;
echo $set22;
echo $sql2;
// End Test SQL parameters in MYSQL

// Run Transaction

DB::Exec ("START TRANSACTION;");

try{

DB::Exec( $set1 );
DB::Exec( $set2 );
DB::Exec( $set3 );
DB::Exec( $set4 );
DB::Exec( $set5 );
DB::Exec( $set6 );
DB::Exec( $set7 );
DB::Exec( $set8 );
DB::Exec( $set9 );
DB::Exec( $sql );

DB::Exec( $set21 );
DB::Exec( $set22 );
DB::Exec( $sql2 );

DB::Exec ("COMMIT;");

}catch (Exception $e) {

DB::Exec ("ROLLBACK;");
die("Transaction failed: " . $e->getMessage()); // or exit()
}
// End Run Transaction`