Assistant with text-to-SQL Performance

API
, , ,
24

Here is a super scaled down version of my text to sql code:

  // Send $systemMessage and $question to the model
  $response = $this->solraiService->solrai_generateSQL($systemMessage, $question);
  
  // Get the $sql statement from the response.
  $sql = $response['generated_text'];	

  if ($this->isSafeSQL($sql)) {
    // proceed with executing the SQL query
    $isSafe = 'Y';
  } else {
    // log an error and halt the execution
    $logger->error('Unsafe SQL query detected: @query', ['@query' => $sql]);
    $isSafe = 'N';
  }

  // Check if the SQL is not an error message
  if ($isSafe === 'Y' && $sql !== null && (substr($sql, 0, 6) !== "Error:" && substr($sql, 0, 14) !== "Error occurred")) {
    // Hopefully, we have SQL here
    // So, we execute the $sql command and get the results
    $results = $this->solraiService->dbConNew_readonly($sql);
    
    // We convert the results to HTML for display.
    $output = $this->solraiService->convertToHTML($results);
  	
    // Alternately, I can also send this $output to model to render a semantic response	
    if ($analyze === 'Y') {
  	$response01 = $this->analyzeResults($sqlQueryOutput, $analyzeQuery, $sqlAnalyzeMessage, $modelSql);
  		$generated_text = $response01['generated_text'];
  	$sqlQueryOutput = $sqlQueryOutput . '<strong>Analysis: </strong>' . $analyzeQuery . '<br>' . $generated_text;
    }

I run my queries inside of a Drupal CMS, so I can directly access my database without use of API – which appears to be what you wish to do.

Note that I can output to the end user (your Step #4) an HTML formatted table and/or a semantic (natural language) response.