Sql statement doesn t return rows ошибка

TOraStoredProc не разрешают использовать?

begin

имя(параметры);

end;

я так понимаю к этим компонентам можно обртиться из ФР скриптяги…

тогда пробуй так

в SmartQuery.Sql.Text

begin

MYPROCE(:P1, :P2, :RESULT1, :RESULT2);

end;

а вскрипте



q1.ParamByName("P1").AsString := "T";

q1.ParamByName("P2").AsInteger := c;

a := q1.ParamByName("RESULT1").AsInteger;

b := q1.ParamByName("RESULT2").AsString;



а если ф-я то..

SELECT MYFUNC(...) FROM ХЗ[DUAL]

roottim что то я не понял что ты имееш в виду…

У меня есть Хранимая PL/SQL процедура в Oracle… Есть компонента DELPHI FAstREPORT. а там есть TfrODACQuery и TfrODACDatebase….

И я ODACQuery.SQl хочу запустить хранимую процедуру… но нисего не получается си. выше

TOraQuery.ExecSQL

Ругается что «SQL statement doesn`t return rows» ?

я так пологаю на OnPrint некого элемента FR вешается обработчик для получения неких данных через ODAC для их дальнейшего изображения.

пусть у тебя будет

TfrODACQuery c именем .Name := «q1»;

на OnPrint элемениа

begin

q1.SQL.Text := "begin MYPROCE(:P1, :P2, :RESULT1, :RESULT2); end;"

// это твои входные параметы IN

q1.ParamByName("P1").AsString := "T";

q1.ParamByName("P2").AsInteger := 12;

q1.ExecSql;

// это твои выходные данные

a := q1.ParamByName("RESULT1").AsInteger;

b := q1.ParamByName("RESULT2").AsString;

// а дальше незнаю что ты хочеш с ними делать

// например

memo1.Text := b;

end;

так предпологаю я…

а еслиб вы мне их закинули на roottim@electro.tchercom.ru,

я бы сказал точнее :), если не жалко конечно…

хотя эти fr интересно зависят от моих ODAC, или нет…

если независят то какая верси ODAC использовалась для внедрения в FR компоненты

BEGIN

MY_PROCEDURE(…)

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

НЕ ПРОКАТЫВАЕТ =(

ROTTIM

ХРАНИМАЯ ПРОЦЕДУРА ПРОИЗВОДИТ СЛОЖНУЮ ОБРАБОТКУ ДАННЫЕ ИЗ РАЗНЫХ ТАБЛИЦ, ЧТО НЕЛЬЗЯ СДЕЛАТЬ SQL ЗАПРОСАМИ, И ЗАПОЛНЯЕТ НЕКОТОРУЮ ВРЕМЕННУЮ ТАБЛИЦУ….

МНЕ НУЖНЫ ДАННЫЕ ИЗ ЭТО ТАБЛИЦЫ ДЛЯ СОСТАВЛЕНИЯ ОТЧЕТА

> ХРАНИМАЯ ПРОЦЕДУРА ПРОИЗВОДИТ СЛОЖНУЮ ОБРАБОТКУ ДАННЫЕ ИЗ

> РАЗНЫХ ТАБЛИЦ, ЧТО НЕЛЬЗЯ СДЕЛАТЬ SQL ЗАПРОСАМИ, И ЗАПОЛНЯЕТ

> НЕКОТОРУЮ ВРЕМЕННУЮ ТАБЛИЦУ….

> МНЕ НУЖНЫ ДАННЫЕ ИЗ ЭТО ТАБЛИЦЫ ДЛЯ СОСТАВЛЕНИЯ ОТЧЕТА



вот эти слова должны первые посте стоять… откуда мне то знать чего твоя процедура делает…

ну незнаю на диалоге на батон вешай обработчик

begin

q1.SQL.Text := "begin MYPROCE(:P1, :P2); COMMIT; end;"

// это твои входные параметы IN

q1.ParamByName("P1").AsString := "T";

q1.ParamByName("P2").AsInteger := 12;

q1.ExecSql;

end;

и что ничего не работает ??

Reinder

Похоже только так =(

> такой код в Fast-e не прокатит



естестенно такой не покатит, но ведь у меня нет исходников на фрОДАК

я смотрел реализацию фрДОА.

св-ва params, SQL и ExecSQL там зарегистрированы…

а что есть в его компонентах.. хз.. могу толькоп редполагать..

2Raven

либо реализация фрОДАК у вас кривая (помоему она не входит в пак_адд_он, видимо нетестирования самопальная), либо…

• Remove From My Forums

• Question

• I have execute sql task and i pass in 2 parameters to the sql statement inside execute sql task. Sometimes the sql statement returns rows and sometimes it doesn’t.
  when the sql statement returns rows, the rest of the package runs fine. If the sql statement does not return rows, the package fails and the error message is

  [Execute SQL Task] Error: An error occurred while assigning a value to variable «varPeriod»: «Single Row result set is specified, but no rows were returned.».

  what should i do to make sure that even if the sql statement returns nothing, it has to go on?

  The execute sql task that i am talking about is inside foreach loop?

  ---

  Thanks

[!] Как относитесь к модерированию на этом форуме? Выскажите свое мнение здесь

Проблема с Odac
, update

0 пользователей читают эту тему (0 гостей и 0 скрытых пользователей)

0 пользователей:

Hi Folks,

I am trying to execute a standard   ‘select * from x’  statement using PHP 5.4 and the PHP SQL Drivers (5.5 as 64bit) and am getting no rows returned (there should be about 20).

The code is:  $result = sql_query($db_handle, $query, null);    with $result containing nothing.

$db_handle contains a valid resource id and $query contains «select * from x’.

I have all of the logging enabled (and nothing is being output error wise (LogSubsystems=-1 and LogSeverity=-1) — also have display_errors set and nothing being output.

Running SQL Server profiler I see an audit_login followed by a query (rpc_completed).  exec sp_cursoropen @p1 output, N’select * from x’, @p3, output, @p4 output, $p5 output

The following line in the profiler is ‘audit logout’

No data is returned.   If I cut and paste the query into SQL server management studio I get 20 rows returned.

I have tried putting some invalid SQL in (select FROM x settings) but no error is given when the query is executed (although I do see it appear in the SQL Server profiler.

Does anyone have an idea on what might be going wrong?

Regards

Andy

Update:  I also get in the SQL Profiler

SQL:BatchStarting:  Select * from x,  

SqlBatchCompleted: Select * from x,

But no rows are returned.

Resolution: Upon logging in to the database server via a remote SQL Management Studio session using the UID/PWD off the PHP sql_connection it was determined that the user did not have read permissions on the database.  No error was
being returned.    Correct permissions were assigned to the login and all is working.

• Edited by

  Wednesday, July 2, 2014 3:38 AM

def update_csv_status_db(db_instance, name_of_db_instance_tabledict, csvfile_path, dir_status):
    table_dict = db_instance[name_of_db_instance_tabledict]
    csvfile_name = csvfile_path.name
    sql = update(table_dict['table']).where(table_dict['table'].c.CSV_FILENAME == csvfile_name).values(CSV_STATUS=dir_status)
    df = pd.read_sql(sql, table_dict['connection'])
    #df.to_sql('odfs_tester_history_files', con=table_dict['engine'], if_exists='append', index=False)

def odf_history_from_csv_to_dbtable(db_instance):
    odfsdict = db_instance['odfs_tester_history']
    #table_row = {}
    totalresult_list = []

    dir_dict, dictofpdir_flist = make_dict_of_csvprocessing_dirs()
    print(dir_dict)
    csvbase_path_list = dictofpdir_flist["csvbase_path_list"]
    archivefiles_path_set = dictofpdir_flist["archivefiles_path_set"]
    errorfiles_path_set = dictofpdir_flist["errorfiles_path_set"]
    emptyfiles_path_set = dictofpdir_flist["emptyfiles_path_set"]

    for csv in csvbase_path_list:  # is there a faster way to compare the list of files in archive and history?
        if csv.name in archivefiles_path_set:
            print(csv.name + " is in archive folder already")
        elif csv.name in errorfiles_path_set:
            print(csv.name + " is in error folder already")
        elif csv.name in emptyfiles_path_set:
            print(csv.name + " is in empty folder already")
        else:
            csvhistoryfilelist_to_dbtable(csv, db_instance)
            df_tuple = process_csv_formatting(csv)
            df_cnum, odfscsv_df = df_tuple
            if df_cnum == 1:
                trg_path = Path(dir_dict['empty_dir'])
                csv.rename(trg_path.joinpath(csv.name))
                update_csv_status_db(db_instance, 'odfs_tester_history_files', csv, 'empty')
            elif df_cnum == 0 and len(odfscsv_df.index != 0):
                result = odfscsv_df.to_sql('odfs_tester_history', con=odfsdict['engine'], if_exists='append', index=False)
                totalresult_list.append(result)
                trg_path = Path(dir_dict['archive_dir'])
                csv.rename(trg_path.joinpath(csv.name))
                update_csv_status_db(db_instance, 'odfs_tester_history_files', csv, 'archive')

    return totalresult_list

Traceback (most recent call last):
  File "C:ProgramDataAnaconda3libsite-packagessqlalchemyengineresult.py", line 1161, in _fetchall_impl
    return self.cursor.fetchall()
AttributeError: 'NoneType' object has no attribute 'fetchall'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:/Users/sys_nsgprobeingestio/Documents/dozie/odfs/odfshistory3.py", line 269, in <module>
    odf_history_from_csv_to_dbtable(db_instance)
  File "C:/Users/sys_nsgprobeingestio/Documents/dozie/odfs/odfshistory3.py", line 244, in odf_history_from_csv_to_dbtable
    update_csv_status_db(db_instance, 'odfs_tester_history_files', csv, 'error')
  File "C:/Users/sys_nsgprobeingestio/Documents/dozie/odfs/odfshistory3.py", line 178, in update_csv_status_db
    df = pd.read_sql(sql, table_dict['connection'])
  File "C:ProgramDataAnaconda3libsite-packagespandasiosql.py", line 438, in read_sql
    chunksize=chunksize,
  File "C:ProgramDataAnaconda3libsite-packagespandasiosql.py", line 1231, in read_query
    data = result.fetchall()
  File "C:ProgramDataAnaconda3libsite-packagessqlalchemyengineresult.py", line 1216, in fetchall
    e, None, None, self.cursor, self.context
  File "C:ProgramDataAnaconda3libsite-packagessqlalchemyenginebase.py", line 1478, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "C:ProgramDataAnaconda3libsite-packagessqlalchemyutilcompat.py", line 153, in reraise
    raise value
  File "C:ProgramDataAnaconda3libsite-packagessqlalchemyengineresult.py", line 1211, in fetchall
    l = self.process_rows(self._fetchall_impl())
  File "C:ProgramDataAnaconda3libsite-packagessqlalchemyengineresult.py", line 1163, in _fetchall_impl
    return self._non_result([])
  File "C:ProgramDataAnaconda3libsite-packagessqlalchemyengineresult.py", line 1168, in _non_result
    "This result object does not return rows. "
sqlalchemy.exc.ResourceClosedError: This result object does not return rows. It has been closed automatically.

█ 15.06.2010 11:16

█ 15.06.2010 12:41

█ 15.06.2010 14:23

 SELECT MIN(DTT) AS DT FROM(
    SELECT DTT FROM(
        select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date('13.06.2010')+7 dt from dual)
        UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date('13.06.2010')+8 dt from dual)
        UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date('13.06.2010')+9 dt from dual)
        UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date('13.06.2010')+10 dt from dual)
        UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date('13.06.2010')+11 dt from dual)
        UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date('13.06.2010')+12 dt from dual)
        UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date('13.06.2010')+13 dt from dual)
        UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date('13.06.2010')+14 dt from dual)
        UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date('13.06.2010')+15 dt from dual)
) WHERE mod(TO_NUMBER(SUBSTR(DTT,0,2)),2)<>0)

█ 15.06.2010 15:30

█ 15.06.2010 16:13

█ 15.06.2010 16:15

CREATE OR REPLACE TRIGGER SUPERMAG.LMMATRIXCHANGE
BEFORE DELETE OR INSERT
ON SUPERMAG.SMASSORTMATRIX 
FOR EACH ROW
DECLARE
dayForZak int;
DDATE date;
daten char(10);
BEGIN
SELECT TO_NUMBER(VALUE) into dayForZak FROM SUPERMAG.LMCONFIG WHERE PARM='day_for_vvod';
SELECT TO_CHAR(SYSDATE, 'DD.MM.YYYY') into daten FROM dual;
SELECT TO_DATE(MIN(DTT)) into DDATE FROM(
    SELECT DTT FROM(
              select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date(daten)+7 dt from dual)
        UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date(daten)+8 dt from dual)
        UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date(daten)+9 dt from dual)
        UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date(daten)+10 dt from dual)
        UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date(daten)+11 dt from dual)
        UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date(daten)+12 dt from dual)
        UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date(daten)+13 dt from dual)
        UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date(daten)+14 dt from dual)
        UNION select decode(to_char(dt,'d'),1, dt) AS DTT from (select to_date(daten)+15 dt from dual)
) WHERE mod(TO_NUMBER(SUBSTR(DTT,0,2)),2)<>0);
--Core.FailMsg('Äàòà'||DDATE);

if inserting or updating then
INSERT INTO LMMATRIXACTIONS(ARTICLE,ACTIONDATE,OWNER,ACTION,MATRIXID,LOCATION,ZAKDATE) 
VALUES (:New.ARTICLE,SYSDATE,USER,'insert',:New.IDMATRIX,(SELECT STORELOC FROM SMASSORTMATRIXLOC WHERE IDMATRIX=:New.IDMATRIX),DDATE);

else
INSERT INTO LMMATRIXACTIONS(ARTICLE,ACTIONDATE,OWNER,ACTION,MATRIXID,LOCATION) VALUES 
(:Old.ARTICLE,SYSDATE,USER,'delete',:Old.IDMATRIX,(SELECT STORELOC FROM SMASSORTMATRIXLOC WHERE IDMATRIX=:Old.IDMATRIX));
end if;   
END LMMATRIXCHANGE;
/

█ 15.06.2010 16:40

█ 15.06.2010 17:01

WITH datelist AS
     (SELECT TO_DATE ('01012010', 'DDMMYYYY') fromdate, TO_DATE ('31122010', 'DDMMYYYY') todate
        FROM DUAL)
SELECT DAY
  FROM (SELECT     fromdate + LEVEL - 1 DAY
              FROM datelist
        CONNECT BY LEVEL <= todate - fromdate + 1)
 WHERE TO_CHAR (DAY, 'D') = 1 AND TO_NUMBER (TO_CHAR (DAY, 'DD')) / 2 != TRUNC (TO_NUMBER (TO_CHAR (DAY, 'DD')) / 2) AND ROWNUM = 1

█ 15.06.2010 17:24

Error at line 1
SQL statement doesn't return rows

█ 15.06.2010 17:46

SQL*Plus: Release 10.2.0.4.0 - Production on Вт Июн 15 17:44:01 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Присоединен к:
Oracle9i Release 9.2.0.8.0 - Production
JServer Release 9.2.0.8.0 - Production

SQL> WITH datelist AS
  2       (SELECT TO_DATE ('01012010', 'DDMMYYYY') fromdate, TO_DATE ('31122010', 'DDMMYYYY') todate
  3          FROM DUAL)
  4  SELECT DAY
  5    FROM (SELECT     fromdate + LEVEL - 1 DAY
  6                FROM datelist
  7          CONNECT BY LEVEL <= todate - fromdate + 1)
  8   WHERE TO_CHAR (DAY, 'D') = 1 AND TO_NUMBER (TO_CHAR (DAY, 'DD')) / 2 != TRUNC (TO_NUMBER (TO_CHAR (DAY, 'DD')) / 2) AND ROWNUM = 1;

DAY
--------
11.01.10

SQL>