1. Scott Roberts
  2. Valentina Database ADK
  3. Samstag, Juni 30 2018, 09:59 PM
  4.  Abonnieren via E-Mail
I am working in LiveCode and am using V4REV 8.3.3.

I have been using the SQLDiff tool in Visual Studio to create SQL statements that will allow me to migrate my database from one version to the next. I have been reading the files into a variable and calling VDatabase_SqlExecute() to run the SQL statements. This has been working fine; however, I ran across a situation today that does not work.

If the SQL statements passed to VDatabase_SqlExecute() include the creation of both functions and procedures (that reference those functions), the call fails. The error reported is 0x9050E - Error in function name "<function name>".

If the SQL for creating the functions is processed first (without the SQL for the procedures), that executes successfully. Then, the SQL for creating the procedures can be run successfully.

I have attached a zip file that contains the necessary items to recreate this issue. The files are as follows:

Test Valentina.livecodescript - contains the routine to run the test.
Valentina Test.sql - contains the SQL generated by SQL Diff (includes both functions and procedures)
Valentina Test 2.sql - includes only the SQL for creating the functions
Valentina Test 3.sql - includes only the SQL for creating the procedures
V4REV_Log_20180630_165707.log - is the log file created when I ran the test.

When the code is run, the attempt to execute the SQL statements in Valentina Test.sql will fail. Then, executing the SQL statements in Valentina Test 2.sql and Valentina 3.sql will succeed.
Anhänge
Kommentar
There are no comments made yet.
Ivan Smahin Akzeptierte Antwort
Correct, LoadDump will create a new database if it is absent and use existed one otherwise.
Kommentar
There are no comments made yet.
Scott Roberts Akzeptierte Antwort
It's working for me now, thanks. It seems that it was the "UTF8" parameter that did the trick.

I ran a test and it looks like I can use LoadDump instead of SqlExecute for my migration SQL statements (created from the SQL Diff tool). I was unclear as to whether LoadDump could be run on an existing database because the documentation seemed to imply that LoadDump would create a new database. However, it seems to be OK to set the path for the "new" database to be the same as the existing database. Please correct me if I'm wrong in thinking this.

In my additional test, I created a second SQL file that created a table. I ran LoadDump with one file and then the other and the database was updated as expected.

Thanks again for your help.
Kommentar
There are no comments made yet.
Ivan Smahin Akzeptierte Antwort

on TestValentina_CreateFunctionsAndProcedures
get Valentina_Init(10 * 1024 * 1024)
get Valentina_DebugLevel("kLogParams")

local tDB, tDBPath, tDumpFilePath, tSql, tDumpFolder
put VDatabase_Constructor() into tDB
put specialFolderPath("Documents") into tDBPath
put slash & "Valentina Test" after tDBPath
put tDBPath into tDumpFolder
put slash & "ValentinaTest.vdb" after tDBPath

get VDatabase_Create(tDB, tDBPath)

put tDumpFolder & slash & "Valentina Test.sql" into tDumpFilePath

get VDatabase_LoadDump(tDB, tDumpFilePath, tDBPath, "kSQL", "UTF8")

get VDatabase_Close(tDB)

get Valentina_DebugLevel("kLogNothing")
get Valentina_ShutDown()
end TestValentina_CreateFunctionsAndProcedures

Kommentar
There are no comments made yet.
Scott Roberts Akzeptierte Antwort
When I attempt to use VDatabase_LoadDump(), the database is created but there is nothing in it (no procedures or functions). I modified my code as follows:


get Valentina_Init(10 * 1024 * 1024)
get Valentina_DebugLevel("kLogParams")

local tDB, tDBPath, tDumpFilePath, tSql, tDumpFolder
put VDatabase_Constructor() into tDB
put specialFolderPath("Documents") into tDBPath
put slash & "Valentina Test" after tDBPath
put tDBPath into tDumpFolder
put slash & "ValentinaTest.vdb" after tDBPath

put tDumpFolder & slash & "Valentina Test.sql" into tDumpFilePath
open file tDumpFilePath for read
read from file tDumpFilePath until EOF
put it into tSql
close file tDumpFilePath
get VDatabase_LoadDump(tDB, tDumpFilePath, tDBPath, "kSQL")
get VDatabase_Close(tDB)

get Valentina_DebugLevel("kLogNothing")
get Valentina_ShutDown()


The generated log does not indicate any error. (I have attached the file.)
Anhänge
Kommentar
There are no comments made yet.
Ivan Smahin Akzeptierte Antwort
Currently, it would not work, because the parser is looking for "test_func" UDF (called from test_proc) in the database at parsing stage - before any part of that statements is executed.

As a workaround, you may try LoadDump (instead of SqlExecute).
LoadDump() works a bit different. It reads a single statement from the file and executes it immediately, so test_func() is available at parsing test_proc().
Kommentar
There are no comments made yet.
Scott Roberts Akzeptierte Antwort
I have reported this issue on Mantis (ID 0008300). (I was unable to access Mantis yesterday.)
Kommentar
There are no comments made yet.
  • Seite :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories